| From: | "K(dot) Ari Krupnikov" <ari(at)cogsci(dot)ed(dot)ac(dot)uk> |
|---|---|
| To: | pgsql-general(at)postgresql(dot)org |
| Subject: | EXCEPT performace |
| Date: | 2001-11-03 20:08:52 |
| Message-ID: | 3BE44ED4.62D31BDC@cogsci.ed.ac.uk |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | 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.
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Tom Lane | 2001-11-03 21:05:48 | Re: CREATE TYPE delimiter? |
| Previous Message | Command Prompt, Inc. | 2001-11-03 19:43:11 | Re: CREATE TYPE delimiter? |