8
I currently have a postgresql query that is slow because of an OR statement. It's apparently not using an index because of it. Rewriting this query failed so far.
The query:
EXPLAIN ANALYZE SELECT a0_.id AS id0
FROM advert a0_
INNER JOIN advertcategory a1_
ON a0_.advert_category_id = a1_.id
WHERE a0_.advert_category_id IN ( 1136 )
OR a1_.parent_id IN ( 1136 )
ORDER BY a0_.created_date DESC
LIMIT 15;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.00..27542.49 rows=15 width=12) (actual time=1.658..50.809 rows=15 loops=1)
-> Nested Loop (cost=0.00..1691109.07 rows=921 width=12) (actual time=1.657..50.790 rows=15 loops=1)
-> Index Scan Backward using advert_created_date_idx on advert a0_ (cost=0.00..670300.17 rows=353804 width=16) (actual time=0.013..16.449 rows=12405 loops=1)
-> Index Scan using advertcategory_pkey on advertcategory a1_ (cost=0.00..2.88 rows=1 width=8) (actual time=0.002..0.002 rows=0 loops=12405)
Index Cond: (id = a0_.advert_category_id)
Filter: ((a0_.advert_category_id = 1136) OR (parent_id = 1136))
Rows Removed by Filter: 1
Total runtime: 50.860 ms
Cause of slowness: Filter: ((a0_.advert_category_id = 1136) OR (parent_id = 1136))
I've tried using INNER JOIN instead of the WHERE statement:
EXPLAIN ANALYZE SELECT a0_.id AS id0
FROM advert a0_
INNER JOIN advertcategory a1_
ON a0_.advert_category_id = a1_.id
AND ( a0_.advert_category_id IN ( 1136 )
OR a1_.parent_id IN ( 1136 ) )
ORDER BY