Re: Slow query on CLUTER -ed tables

2011/3/23 Laszlo Nagy <gandalf(at)shopzeus(dot)com>:
> "GroupAggregate  (cost=5553554.25..5644888.17 rows=2283348 width=50)"
> "  ->  Sort  (cost=5553554.25..5559262.62 rows=2283348 width=50)"
> "        Sort Key: pph.hid, ppoh.merchantid, pph.hdate"
> "        ->  Nested Loop  (cost=0.00..5312401.66 rows=2283348 width=50)"
> "              ->  Index Scan using idx_product_price_history_id_hdate on
> product_price_history pph  (cost=0.00..8279.80 rows=4588 width=16)"
> "                    Index Cond: (id = 37632081)"
> "              ->  Index Scan using pk_product_price_offer_history on
> product_price_offer_history ppoh  (cost=0.00..1149.86 rows=498 width=42)"
> "                    Index Cond: (ppoh.hid = pph.hid)"
> "                    Filter: (ppoh.isfeatured = 1)"

I suspect that, since the matched hid's probably aren't sequential,
many of those ~500 product_price_offer_history rows will be far apart
on disk.

Please show the EXPLAIN ANALYZE output in the slow case, not just
EXPLAIN. Also, PostgreSQL version? What configuration options have you
changed? (http://wiki.postgresql.org/wiki/SlowQueryQuestions)

Regards,
Marti