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_idxon 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_idxon
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