Re: Why so long?

Steve Clark <steve.clark@xxxxxxxxxxxxx> · Wed, 19 Apr 2017 12:20:16 -0400



    On 04/19/2017 11:57 AM, Jeff Janes
      wrote:

    
          On Wed, Apr 19, 2017 at 8:24 AM,
            Steve Clark <steve.clark@xxxxxxxxxxxxx>
            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