Re: Query Performance...

jhood@hmcon.com (Jeffrey Hood) writes:
> 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)

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

The only *possible* alternative to a seqscan on patient would be to use
a nestloop with inner indexscan on patient.patientid, and I'm not at all
clear that that'd be faster than the seqscan --- it would depend on how
many rows are actually returned by the rx scan.

Have you got an index on patientid?  If you set enable_seqscan = off,
does the plan change?

            regards, tom lane