> and here's my query > > select * from ticket > where created between '2012-12-19 00:00:00' and '2012-12-20 00:00:00' > > This was working fine until the number of records started to grow (about 5 > million) and now it's taking forever to return. > > Explain analyze reveals this: > > "Index Scan using ticket_1_idx on ticket (cost=0.00..10202.64 rows=52543 > width=1297) (actual time=0.109..125.704 rows=53340 loops=1)" > " Index Cond: ((created >= '2012-12-19 00:00:00+00'::timestamp with time > zone) AND (created <= '2012-12-20 00:00:00+00'::timestamp with time zone))" > "Total runtime: 175.853 ms"
> Nothing works. What am I doing wrong? why is it selecting sequential scan? > the indexes are supposed to make the query fast. Anything that can be done to > optimize it? It is not selecting sequential scan, you're looking at an index scan. That should be pretty fast, and it isn't that slow - that's still sub-second performance (0.176s). Is that explain from the correct table? According to the results there are but 53 thousand rows in it, not anywhere near 5 million. Perhaps your index got bloated? Do you have any long-running transactions still active that prevent cleaning up deprecated index entries? If that's the case, close the application that keeps that connection open and run a vacuum. Are you vacuuming that table often enough? If none of that helps, perhaps a REINDEX does. Is that a dedicated database machine or is it also doing other stuff that's eating up resources? You didn't mention what version of Postgres you're on or what OS you're using. Alban Hertroys -- Screwing up is an excellent way to attach something to the ceiling. -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
