From: | "John Liu" <johnl(at)synthesys(dot)com> |
---|---|
To: | <pgsql-hackers(at)postgresql(dot)org> |
Subject: | why? |
Date: | 2002-07-25 13:55:53 |
Message-ID: | NDBBKKKHILOHGHNKGOCEGEHEDNAA.johnl@synthesys.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers pgsql-patches |
I've two queries -
1. emrxdbs=# explain select * from patient A where exists (select NULL from
patient B where B.mrn=A.mrn and B.dob=A.dob and B.sex=A.sex and
B.lastname=A.lastname and B.firstname=A.firstname group by B.mrn, B.dob,
B.sex, B.lastname, B.firstname having A.patseq < max(B.patseq)) limit 10;
NOTICE: QUERY PLAN:
Limit (cost=0.00..121.50 rows=10 width=141)
-> Seq Scan on patient a (cost=0.00..6955296.53 rows=572430 width=141)
SubPlan
-> Aggregate (cost=6.03..6.05 rows=1 width=42)
-> Group (cost=6.03..6.05 rows=1 width=42)
-> Sort (cost=6.03..6.03 rows=1 width=42)
-> Index Scan using patient_name_idx on patient
b (cost=0.00..6.02 rows=1 width=42)
2. emrxdbs=# explain select * from patient A where exists (select NULL from
patient B where B.mrn=A.mrn and B.dob=A.dob and B.sex=A.sex and
B.lastname=A.lastname and B.firstname=A.firstname and B.mrn='3471585' group
by B.mrn, B.dob, B.sex, B.lastname, B.firstname having A.patseq <
max(B.patseq)) limit 10;
NOTICE: QUERY PLAN:
Limit (cost=0.00..121.45 rows=10 width=141)
-> Seq Scan on patient a (cost=0.00..6951997.59 rows=572430 width=141)
SubPlan
-> Aggregate (cost=6.03..6.05 rows=1 width=42)
-> Group (cost=6.03..6.04 rows=1 width=42)
-> Sort (cost=6.03..6.03 rows=1 width=42)
-> Index Scan using patient_mrnfac_idx on
patient b (cost=0.00..6.02 rows=1 width=42)
The first query results come back fairly quick, the 2nd one just sits there
forever.
It looks similar in the two query plans.
Let me know.
thanks.
johnl
From | Date | Subject | |
---|---|---|---|
Next Message | Marc Lavergne | 2002-07-25 14:01:31 | Re: Oracle Decode Function |
Previous Message | Christopher Kings-Lynne | 2002-07-25 07:16:14 | Re: Proposal: anonymous composite types for Table Functions (aka SRFs) |
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2002-07-25 15:03:39 | Re: small psql patch - show Schema name for \dt \dv \dS |
Previous Message | Christopher Kings-Lynne | 2002-07-25 07:16:14 | Re: Proposal: anonymous composite types for Table Functions (aka SRFs) |