Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I was able to resolve this issue by altering the time.date column to allow 1000 stats: ALTER TABLE time ALTER COLUMN date SET STATISTICS 1000;

Now the queries are running very fast, down to 300ms from 5s.

I'll be looking into patching postgres as well.

I have a table with 13.7 million rows. Up until recently, queries on this table have been quite fast. Suddenly they are slow. More specifically, the same query is fast when looking at a large date range, but crazy slow when looking at a small date range. Here are the two statements and their EXPLAIN ANALYZE output.

I am using Postgresql 8.4.5 with default_statistics_target set to the default 100.

It's obviously choosing a bad query plan for the shorter date range, but how do I resolve this? And how do I interpret the query plan?