Re: [EXT] Re: Improve "select count(*)" query - takes more than 30 mins for some large tables

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On Tue, 2022-07-12 at 14:25 -0400, MichaelDBA Vitale wrote: 
> On 07/12/2022 2:13 PM Pierson Patricia L (Contractor) <patricia.l.pierson@xxxxxxx> wrote: 
> > Do a count on the primary key.  Will force index access and you don’t access the entire row which may be very long.
> > LIKE : select count(ID) from my_table;
> 
> That is not true: doing the select on the primary key will still result in a table scan,
> not an index scan.  The heap always gets accessed for select counts. 

I'd say that both statements are wrong:

- count(id) is *slower* than count(*), because it has to check each "id" if it is
  NULL or not (NULL values are not counted).  count(*) is just the SQL standard's
  weird way of writing a parameterless aggregate; it has nothing to do with the *
  in "SELECT * FROM ".

- Both "SELECT count(id) FROM tab" and "SELECT count(*) FROM tab" can result in an
  index-only scan.  You just need the table to be recently VACUUMed, you need
  a table that is wide enough that a sequential scan is actually slower than an
  index-only scan, and perhaps you need "random_page_cost" to be low enough.

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com





[Index of Archives]     [Postgresql Home]     [Postgresql General]     [Postgresql Performance]     [Postgresql PHP]     [Postgresql Jobs]     [PHP Users]     [PHP Databases]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Databases]     [Yosemite Forum]

  Powered by Linux