EXCEPT performace
От | K. Ari Krupnikov |
---|---|
Тема | EXCEPT performace |
Дата | |
Msg-id | 3BE44ED4.62D31BDC@cogsci.ed.ac.uk обсуждение исходный текст |
Ответы |
Re: EXCEPT performace
Re: EXCEPT performace |
Список | pgsql-general |
I noticed an unexpected difference in performance between seemingly equivalent queries: SELECT id FROM short_table EXCEPT SELECT id FROM long_table; and SELECT id FROM short_table EXCEPT (SELECT id FROM short_table JOIN long_table ON short_table.id = long_table.id); There is an index on longtable.id; short_table is a temporary table. EXPLAIN suggests that the index is ignored in the first case but consulted in the second. Seq Scan on short_table (cost=0.00..2857177.50 rows=1000 width=12) SubPlan -> Seq Scan on long_table (cost=0.00..5714.31 rows=200831 width=12) vs. Seq Scan on short_table (cost=0.00..44.52 rows=3 width=12) SubPlan -> Materialize (cost=14.49..14.49 rows=3 width=24) -> Nested Loop (cost=0.00..14.49 rows=3 width=24) -> Seq Scan on short_table (cost=0.00..1.03 rows=3 width=12) -> Index Scan using long_table_id_ix on long_table (cost=0.00..4.48 rows=1 width=12) Is there a reason an index cannot be used in an EXCEPT query? Ari.
В списке pgsql-general по дате отправления: