From: | Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at> |
---|---|
To: | MichaelDBA Vitale <michaeldba(at)sqlexec(dot)com>, "Pierson Patricia L (Contractor)" <Patricia(dot)L(dot)Pierson(at)irs(dot)gov> |
Cc: | "pgsql-admin(at)lists(dot)postgresql(dot)org" <pgsql-admin(at)lists(dot)postgresql(dot)org> |
Subject: | Re: [EXT] Re: Improve "select count(*)" query - takes more than 30 mins for some large tables |
Date: | 2022-07-12 19:13:27 |
Message-ID: | 05fa08bf8b4e1023eb879bcab0f83d428e4f7a43.camel@cybertec.at |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin |
On Tue, 2022-07-12 at 14:25 -0400, MichaelDBA Vitale wrote:
> On 07/12/2022 2:13 PM Pierson Patricia L (Contractor) <patricia(dot)l(dot)pierson(at)irs(dot)gov> 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
From | Date | Subject | |
---|---|---|---|
Next Message | MichaelDBA | 2022-07-13 02:12:56 | Re: [EXT] Re: Improve "select count(*)" query - takes more than 30 mins for some large tables |
Previous Message | MichaelDBA Vitale | 2022-07-12 18:25:45 | RE: [EXT] Re: Improve "select count(*)" query - takes more than 30 mins for some large tables |