From: Laurenz Albe <laurenz.albe@cybertec.at>
To: MichaelDBA Vitale <michaeldba@sqlexec.com>
To: Pierson Patricia L (Contractor) <Patricia.L.Pierson@irs.gov>
Cc: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
Subject: Re: [EXT] Re: Improve "select count(*)" query - takes more than 30 mins for some large tables
Date: Tue, 12 Jul 2022 21:13:27 +0200
Message-ID: <05fa08bf8b4e1023eb879bcab0f83d428e4f7a43.camel@cybertec.at> (raw)
In-Reply-To: <1037158693.49441.1657650345834@email.ionos.com>
References: <CACxU--UJY4sD2SjW8V3W0oAPuuTzE3fAvBzTaej-BmUUNJxN_Q@mail.gmail.com>
<1B1927BA-F7DE-40E8-8F67-BCC001CD4C6B@gmail.com>
<1eb233ff-90f0-529b-f0cc-fa982f668a8f@gmail.com>
<1221536526.20277.1657636277921@email.ionos.com>
<CALcG2D+JJMK13U3qV_pn8np+m2urGJ7iyj_hB0Mt9XfM8GU-vw@mail.gmail.com>
<SJ0PR09MB6061D637A2A7988D19DC506FC7869@SJ0PR09MB6061.namprd09.prod.outlook.com>
<1037158693.49441.1657650345834@email.ionos.com>
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@irs.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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: laurenz.albe@cybertec.at, michaeldba@sqlexec.com, Patricia.L.Pierson@irs.gov, pgsql-admin@lists.postgresql.org
Subject: Re: [EXT] Re: Improve "select count(*)" query - takes more than 30 mins for some large tables
In-Reply-To: <05fa08bf8b4e1023eb879bcab0f83d428e4f7a43.camel@cybertec.at>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox