Re: Why so long?

On 20 April 2017 at 03:24, Steve Clark <steve(dot)clark(at)netwolves(dot)com> wrote:
> pmacct=# explain select min(id) from netflow;
> QUERY PLAN
> -----------------------------------------------------------------------------------------------------------------
> Result (cost=1.13..1.14 rows=1 width=0)
> InitPlan 1 (returns $0)
> -> Limit (cost=0.71..1.13 rows=1 width=8)
> -> Index Only Scan using netflow_pkey on netflow
> (cost=0.71..3799108784.10 rows=9123246080 width=8)
> Index Cond: (id IS NOT NULL)
> (5 rows)
>
> pmacct=# \timing
> Timing is on.
> pmacct=# select max(id) from netflow;
> max
> -------------
> 17547256873
> (1 row)
>
> Time: 0.626 ms
>
>
>
> pmacct=# select min(id) from netflow;
> ^CCancel request sent
> ERROR: canceling statement due to user request
> Time: 339114.334 ms

It may help the diagnosis if you run an EXPLAIN (ANALYZE, BUFFERS)
version of this query to completion.

The index pages could simply be cold and coming from disk on a very
much I/O starved system.

More recently added pages are more likely to be cached.

You may also want to consider running the EXPLAIN (ANALYZE, BUFFERS)
after having SET track_io_timing = on;

--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services