On Thu, May 7, 2009 at 4:20 AM, Dimitri <dimitrik.fr@xxxxxxxxx> wrote: > Hi Simon, > > may you explain why REINDEX may help here?.. - database was just > created, data loaded, and then indexes were created + analyzed.. What > may change here after REINDEX?.. > > With hashjoin disabled was a good try! > Running this query "as it" from 1.50ms we move to 0.84ms now, > and the plan is here: > > QUERY PLAN > ------------------------------------------------------------------------------------------------------------------------------------------------------ > Sort (cost=4562.83..4568.66 rows=2329 width=176) (actual > time=0.225..0.229 rows=20 loops=1) > Sort Key: h.horder > Sort Method: quicksort Memory: 30kB > -> Merge Join (cost=4345.89..4432.58 rows=2329 width=176) (actual > time=0.056..0.205 rows=20 loops=1) > Merge Cond: (s.ref = h.ref_stat) > -> Index Scan using stat_ref_idx on stat s > (cost=0.00..49.25 rows=1000 width=45) (actual time=0.012..0.079 > rows=193 loops=1) > -> Sort (cost=4345.89..4351.72 rows=2329 width=135) (actual > time=0.041..0.043 rows=20 loops=1) > Sort Key: h.ref_stat > Sort Method: quicksort Memory: 30kB > -> Index Scan using history_ref_idx on history h > (cost=0.00..4215.64 rows=2329 width=135) (actual time=0.013..0.024 > rows=20 loops=1) > Index Cond: (ref_object = '0000000001'::bpchar) > Total runtime: 0.261 ms > (12 rows) > > Curiously planner expect to run it in 0.26ms > > Any idea why planner is not choosing this plan from the beginning?.. > Any way to keep this plan without having a global or per sessions > hashjoin disabled?.. can you work prepared statements into your app? turn off hash join, prepare the query, then turn it back on. merlin -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
