Query plan not using index for some reason.
От | Jean-Christian Imbeault |
---|---|
Тема | Query plan not using index for some reason. |
Дата | |
Msg-id | 3D99C59A.6090503@mega-bucks.co.jp обсуждение исходный текст |
Ответы |
Re: Query plan not using index for some reason.
Re: Query plan not using index for some reason. |
Список | pgsql-general |
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
В списке pgsql-general по дате отправления: