Re: Query Performance...

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

There is an index on patientid... and setting enable_seqscan = off and
running explain shows that the index will be used, but running the
query, it takes the same time (45 sec) that it does with the scan...
and the same query on SQLServer and mysql runs in .5 sec... (they both
always use the index...)

Thanks,
JH