One of my SQL is is slow so I tried using EXPLAIN to find out why but
the query plan is gives seems bad ... it's not using indexes ...
The query is on two tables, both of which have indexes. When I check
EXPLAIN for the query without the OR clause the planner uses the index.
When I add the OR clause it uses a seq scan ...
Is the planner right in choosing a seq scan?
Here is the relevant data:
$ psql TMP -c "vacuum analyze"
VACUUM
$ psql TMP -c "explain select products.id as pid from
products,rel_genres_movies where maker_id='53' OR
(rel_genres_movies.minor_id='11' AND rel_genres_movies.prod_id=products.id)"
NOTICE: QUERY PLAN:
Nested Loop (cost=0.00..8906651.40 rows=2677 width=40)
-> Seq Scan on products (cost=0.00..953.85 rows=14285 width=20)
-> Seq Scan on rel_genres_movies (cost=0.00..289.81 rows=16681
width=20)
EXPLAIN
#BUT ... removing either side of the OR clause gives an index scan ...
$ psql TMP -c "explain select products.id as pid from
products,rel_genres_movies where (rel_genres_movies.minor_id='11' AND
rel_genres_movies.prod_id=products.id)"
NOTICE: QUERY PLAN:
Nested Loop (cost=0.00..975.45 rows=145 width=32)
-> Seq Scan on rel_genres_movies (cost=0.00..331.51 rows=145 width=16)
-> Index Scan using products_pkey on products (cost=0.00..4.43
rows=1 width=16)
EXPLAIN
$ psql TMP -c "explain select products.id as pid from
products,rel_genres_movies where maker_id='53'"
NOTICE: QUERY PLAN:
Nested Loop (cost=0.00..7100.10 rows=257505 width=16)
-> Index Scan using idx_products_maker_id on products
(cost=0.00..51.25 rows=15 width=16)
-> Seq Scan on rel_genres_movies (cost=0.00..289.81 rows=16681 width=0)
EXPLAIN
Jc
