I think I am correct in saying that the first (inner most) join is the
problem.
All the actual time is spent running this nested loop.
It does a single sequential scan of the m_transaction table (takes
about 1 second) filtering out all the m_transaction records that match
your criteria (28936 records).
For each of these records it then does a sequential scan of the
ap_attribute table looking for records with isactive='Y'.
For each such record it joins it to the m_transaction record if the
m_product_id in the m_transaction record matches the m_product_id in the
ap_attribute record.
That produces 28860 records and takes 938 seconds.
I suspect that if you had an index on
ap_attribute.m_product_id,ap_attribute.isactive then it might go a lot
faster (depending on the actual size of the ap_attribute table).
Note that I am not 100% sure that the query planner will immediately
use the index after you've created it - you might have to run analyze on
the ap_attribute table first.
Hope this helps,
Robin
--
Sent via pgsql-admin mailing list (pgsql-admin@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
