David Jarvis <thangalin@xxxxxxxxx> 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 -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
