David Jarvis <thangalin@gmail.com> wrote:
> The value for *random_page_cost* was at 2.0; reducing it to 1.1
> had a massive performance improvement (nearly an order of
> magnitude). While the results now return in 5 seconds (down from
> ~85 seconds)
It sounds as though the active portion of your database is pretty
much cached in RAM. True?
> problematic lines remain. Bumping the query's end date by a single
> year causes a full table scan
> How do I persuade PostgreSQL to use the indexes, regardless of
> number of years between the two dates?
I don't know about "regardless of the number of years" -- but you
can make such plans look more attractive by cutting both
random_page_cost and seq_page_cost. Some highly cached loads
perform well with these set to equal values on the order of 0.1 to
0.001.
> (A full table scan against 43 million rows is probably not the
> best plan.)
It would tend to be better than random access to 43 million rows, at
least if you need to go to disk for many of them.
-Kevin