Mike Charnoky wrote: > With respect to the ALTER TABLE SET STATISTICS... how do I determine a > good value to use? This wasn't really clear in the pg docs. Also, do I > need to run ANALYZE on the table after I change the statistics? > > Here are the EXPLAINs from the queries: > > db=# explain select count(*) from prediction_accuracy where evtime > between '2007-09-25' and '2007-09-26'; > > QUERY PLAN > > --------------------------------------------------------------------------------------------------------------------------------------------------------- > Aggregate (cost=475677.40..475677.41 rows=1 width=0) > -> Index Scan using pred_acc_evtime_index on prediction_accuracy > (cost=0.00..444451.44 rows=12490383 width=0) > Index Cond: ((evtime >= '2007-09-25 00:00:00-07'::timestamp > with time zone) AND (evtime <= '2007-09-26 00:00:00-07'::timestamp with > time zone)) > (3 rows) > > db=# explain select count(*) from prediction_accuracy where evtime > between '2007-09-26' and '2007-09-27'; > > QUERY PLAN > > --------------------------------------------------------------------------------------------------------------------------------------------------------- > Aggregate (cost=486615.04..486615.05 rows=1 width=0) > -> Index Scan using pred_acc_evtime_index on prediction_accuracy > (cost=0.00..454671.07 rows=12777586 width=0) > Index Cond: ((evtime >= '2007-09-26 00:00:00-07'::timestamp > with time zone) AND (evtime <= '2007-09-27 00:00:00-07'::timestamp with > time zone)) > (3 rows) Interesting, same plans and no sequential scans... Yet totally different run times. Almost as if something prevents you to read some records between 26 and 27 september... I'm no expert on locking in Postgres, but AFAIK locks that prevent you from reading records are rather rare and probably only issued from userland code. -- Alban Hertroys alban@xxxxxxxxxxxxxxxxx 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 // ---------------------------(end of broadcast)--------------------------- TIP 5: don't forget to increase your free space map settings
