Re: Simple Query Very Slow

Thanks for your responses. Sorry, I forgot to mention that the query
actually takes 46 seconds despite what analyze (I dont quite understand the
output of explain). We did perform a vacuum last Friday and it seems to
help but not too much. We'll also try to recreate the indices.

Here's the output of
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM TICKET
WHERE CREATED BETWEEN '2012-12-19 00:00:00' AND '2012-12-20 00:00:00'

"Index Scan using t_created_idx on ticket (cost=0.00..127638.47
rows=206383 width=183) (actual time=0.065..46104.557 rows=212126 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))"
" Buffers: shared hit=44141 read=157167"
"Total runtime: 46293.384 ms"

Thanks.

On Sat, Dec 22, 2012 at 7:26 AM, Andres Freund <andres(at)2ndquadrant(dot)com>wrote:

> 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
>