Re: Why so long?

On 04/19/2017 11:57 AM, Jeff Janes wrote:
> On Wed, Apr 19, 2017 at 8:24 AM, Steve Clark <steve(dot)clark(at)netwolves(dot)com <mailto:steve(dot)clark(at)netwolves(dot)com>> wrote:
>
> Hello,
>
> I am confused. I have a table that has an incrementing primary key id.
>
> When I select max(id) from table is returns almost instantly but
> when I select min(id) from table it takes longer than I want to wait.
>
> Shouldn't postgresql be able to quickly find the minimum id value in the index?
>
>
> Not if the low end of the index is stuffed full of obsolete entries, which haven't been cleaned up because it is not being vacuumed often enough.
>
> Do you have autovacuum on? Have you manually vacuumed the table recently?
>
> Cheers,
>
> Jeff
Hi Jeff,

Autovacuum is turned on.

schemaname | relname | last_vacuum | last_autovacuum | vacuum_count | autovacuum_count
------------+-----------------------+-------------+-------------------------------+--------------+------------------
public | netflow | | 2017-04-11 01:18:53.261221-04 | 0 | 1

It is a large table.
select pg_size_pretty(pg_relation_size('netflow'));
pg_size_pretty
----------------
1267 GB

select pg_size_pretty(pg_total_relation_size('netflow_pkey'));
pg_size_pretty
----------------
287 GB

Regards,
Steve