Re: [GENERAL] OUTER JOIN IS SLOW

Benjamin Arai wrote:
> Just to make things more clear I ran EXPLAIN ANALYZE on the slow query.
> I got

>   ->  Index Scan using mutualfd_weekday_qbid_pkey_idx on
> mutualfd_weekday_qbid  (cost=0.00..6.01 rows=1 width=19) (actual
> time=34.579..8510.801 rows=253 loops=1)
>         Index Cond: ((pkey >= '2005-12-15'::date) AND (pkey <=
> '2006-12-15'::date))
>         Filter: (cusip = '92193920'::text)

This is the relevant part (including the index details). It's filtering
(text) over all index items within a period of a full year. I imagine
can be quite a lot of data (unless pkey is a primary key on a date
field; only 366 records).

As mentioned, ANALYZE helps, but you may want to check:
1) Are you comparing integers as text strings? You may not want to.
2) Do you have an index on cusip?

You probably have, just in case.

--
Alban Hertroys
alban@magproductions.nl

magproductions b.v.

T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
   7500 AK Enschede

// Integrate Your World //