From: | Denis Perchine <dyp(at)perchine(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Problem with nested query |
Date: | 2000-10-03 04:17:11 |
Message-ID: | 0010031117110Q.08992@dyp.perchine.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hello,
I have quite strange problem with nested query.
mails=> explain INSERT INTO member(email)
mails-> select distinct email
mails-> FROM iaddress
mails-> WHERE NOT EXISTS (
mails(> SELECT email
mails(> FROM member
mails(> WHERE member.email=iaddress.email);
NOTICE: QUERY PLAN:
Unique (cost=1869591274.28..1869591274.28 rows=0 width=12)
-> Sort (cost=1869591274.28..1869591274.28 rows=1 width=12)
-> Seq Scan on iaddress (cost=0.00..1869591274.27 rows=1 width=12)
SubPlan
-> Seq Scan on member (cost=0.00..10226.27 rows=1 width=12)
EXPLAIN
As far as you can see it uses sequence scan for inner select. This is quite
strange as if I do this select with a constant it will use index scan:
mails=> explain SELECT email
mails-> FROM member
mails-> WHERE member.email='test';
NOTICE: QUERY PLAN:
Index Scan using ix_member_email on member (cost=0.00..4.76 rows=1 width=12)
EXPLAIN
Additional data.
mails=> select count(*) from iaddress;
count
-------
58742
(1 row)
mails=> select count(*) from member;
count
--------
219648
(1 row)
There is an index member(email).
PostgreSQL 7.0.2.
Any ideas?
--
Sincerely Yours,
Denis Perchine
----------------------------------
E-Mail: dyp(at)perchine(dot)com
HomePage: http://www.perchine.com/dyp/
FidoNet: 2:5000/120.5
----------------------------------
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2000-10-03 04:56:56 | Re: Problem with nested query |
Previous Message | Louis Bertrand | 2000-10-03 00:40:55 | multi-national character sets? |