EXCEPT performace

Поиск
Список
Период
Сортировка
От K. Ari Krupnikov
Тема EXCEPT performace
Дата
Msg-id 3BE44ED4.62D31BDC@cogsci.ed.ac.uk
обсуждение исходный текст
Ответы Re: EXCEPT performace  (Stephan Szabo <sszabo@megazone23.bigpanda.com>)
Re: EXCEPT performace  (Tom Lane <tgl@sss.pgh.pa.us>)
Список 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 по дате отправления:

Предыдущее
От: Culley Harrelson
Дата:
Сообщение: error deleting table
Следующее
От: patriq@gmx.de (Patrick)
Дата:
Сообщение: Define update-rule on view