On 2012-12-22 13:06:21 +0100, Alban Hertroys wrote: > > 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?
Whats the time you would need? Beause the above isn't that slow. Perhaps the timing youre seing from your application includes transferring the data over a not too fast link? It would be interesting to see EXPLAIN (ANALYZE, BUFFERS) $query > 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. Well, thats the estimate *after* applying the restriction, so that seems sensible. Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
