Hi there,
Thanks for your and Martijn's comments, I obviously forgot to put in some
vital detail:
> - You never want to set enable_seq off in production database.
That's what I thought...
> - did you run "VACUUM ANALYZE speed" lately?
Yes, just before I ran all of the queries in my last email. Hence I mentioned
increasing default_statistics_target to 50 and reanalysing, which didn't help
either.
> - what version are you running?
7.4.8, not sure if I'm ready for 8 yet.
> - another parameter to look at is random_page_cost: "Sets the planner's
> estimate of the cost of a nonsequentially fetched disk page. This is
> measured as a multiple of the cost of a sequential page fetch. A higher
> value makes it more likely a sequential scan will be used, a lower value
> makes it more likely an index scan will be used. The default is four."
Hmm, that's interesting. I need to set random_page_cost as low as 0.5 for the
index scan's cost to dip below that of the seq_scan. Surely that's a
non-realistic setting and not what I want in the long run.
Why on earth does the planner in its default configuration so blatantly miss
that the index scan is vastly superior? Maybe some more stats about my data
will help, a summary is attached.
Also, note that set_id is strictly increasing (hence correlation of 1) and
rec_time is strictly increasing within records with same set_id.
--
Kilian Hagemann
Climate Systems Analysis Group
University of Cape Town
Republic of South Africa
Tel(w): ++27 21 650 2748
station_data=# SELECT relname, relkind, reltuples, relpages FROM pg_class WHERE relname LIKE 'speed%';
relname | relkind | reltuples | relpages
------------------+---------+-------------------+----------
speed | r | 1.39002e+07 | 68138
speed_pkey | i | 1000 | 1
station_data=# SELECT attname, n_distinct, most_common_freqs, correlation FROM pg_stats WHERE tablename='speed';
attname | n_distinct | most_common_freqs | correlation
------------------+--------------+-------------------------------------------------------------------------------+-------------
set_id | 13 | | 1
rec_time | 931519| {0.001,0.000666667,0.000666667,0.000666667} | 0.0737221
wind_speed | 136 | {0.0293333,0.0246667,0.0223333,0.021,0.0206667,0.0203333,0.0203333,0.02,0.02,0.02} | 0.0712318
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
