From: | Bruce Momjian <maillist(at)candle(dot)pha(dot)pa(dot)us> |
---|---|
To: | vadim(at)krs(dot)ru (Vadim B(dot) Mikheev) |
Cc: | hackers(at)postgreSQL(dot)org (PostgreSQL-development) |
Subject: | Subqueries and indexes |
Date: | 1999-03-16 22:50:45 |
Message-ID: | 199903162250.RAA24072@candle.pha.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
In this QUERY:
SELECT keyname
FROM markmain
WHERE mark_id NOT IN(SELECT mark_id
FROM markaty)
I have an index on markaty.mark_id, and have vacuum analyzed. EXPLAIN
shows:
Seq Scan on markmain (cost=2051.43 size=45225 width=12)
SubPlan
-> Seq Scan on markaty (cost=2017.41 size=52558 width=4)
Vadim, why isn't this using the index? Each table has 50k rows. Is it
NOT IN that is causing the problem? IN produces the same plan, though.
If I do a traditional join:
SELECT keyname
FROM markmain , markaty
WHERE markmain.mark_id = markaty.mark_id
I then get a hash join plan:
Hash Join (cost=10768.51 size=90519 width=20)
-> Seq Scan on markmain (cost=2051.43 size=45225 width=16)
-> Hash (cost=0.00 size=0 width=0)
-> Seq Scan on markaty (cost=2017.41 size=52558 width=4)
Seems the optimizer could either hash the subquery, or us an index.
Certainly would be faster than a sequental scan, no?
--
Bruce Momjian | http://www.op.net/~candle
maillist(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life IS a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026
From | Date | Subject | |
---|---|---|---|
Next Message | Michael Davis | 1999-03-16 23:14:33 | RE: [HACKERS] Associative Operators? (Was: Re: [NOVICE] Out of f rying pan, into fire) |
Previous Message | Clark Evans | 1999-03-16 22:45:22 | Re: [HACKERS] Associative Operators? (Was: Re: [NOVICE] Out of frying pan, into fire) |