Re: Query Performance...

Here is the output from an explain...

explain
select r.dateissued, r.medication, p.lastname, p.dob
from rx r
inner join patient p on r.patientid = p.patientid
where r.dateissued between '7/13/02' and '7/14/02'
and lower (p.lastname) = 'may';

Merge Join (cost=42343.82..42498.98 rows=248957 width=64)
-> Sort (cost=8590.77..8590.77 rows=2515 width=32)
-> Index Scan using idx_rx_date_issued on rx r
(cost=0.00..8448.70 rows=2515 width=32)
-> Sort (cost=33753.05..33753.05 rows=9897 width=32)
-> Index Scan using idx_patient_last_name on patient p
(cost=0.00..32959.90 rows=9897 width=32)

explain
select r.dateissued, r.medication, p.lastname, p.dob
from rx r
inner join patient p on r.patientid = p.patientid
where r.dateissued between '7/13/02' and '7/14/02';

Merge Join (cost=237899.24..250302.47 rows=24895698 width=64)
-> Sort (cost=8590.77..8590.77 rows=2515 width=32)
-> Index Scan using idx_rx_date_issued on rx r
(cost=0.00..8448.70 rows=2515 width=32)
-> Sort (cost=229308.47..229308.47 rows=989743 width=32)
-> Seq Scan on patient p (cost=0.00..35256.43 rows=989743
width=32)

The first would return around 4 rows, the second around 1000...

How does one get rid of the table scan on patient in the second...???

Thanks,
JH