From: Holger Jakobs <holger@jakobs.com>
To: 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 20:25:32 +0200
Message-ID: <6e9caa48-5e29-a493-d54d-3a97f3893029@jakobs.com> (raw)
In-Reply-To: <SJ0PR09MB6061D637A2A7988D19DC506FC7869@SJ0PR09MB6061.namprd09.prod.outlook.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>
Am 12.07.22 um 20:13 schrieb Pierson Patricia L (Contractor):
>
> Hello,
>
> 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;
>
If you do a COUNT(*) the database won't do anything different. That's a
myth.
Just compare the execution plans.
db=# explain select count(anr) from auftrag;
QUERY PLAN
------------------------------------------------------------------------------------------
Finalize Aggregate (cost=11614.55..11614.56 rows=1 width=8)
-> Gather (cost=11614.33..11614.54 rows=2 width=8)
Workers Planned: 2
-> Partial Aggregate (cost=10614.33..10614.34 rows=1 width=8)
-> Parallel Seq Scan on auftrag (cost=0.00..9572.67
rows=416667 width=8)
(5 Zeilen)
db=# explain select count(*) from auftrag;
QUERY PLAN
------------------------------------------------------------------------------------------
Finalize Aggregate (cost=11614.55..11614.56 rows=1 width=8)
-> Gather (cost=11614.33..11614.54 rows=2 width=8)
Workers Planned: 2
-> Partial Aggregate (cost=10614.33..10614.34 rows=1 width=8)
-> Parallel Seq Scan on auftrag (cost=0.00..9572.67
rows=416667 width=0)
(5 Zeilen)
--
Holger Jakobs, Bergisch Gladbach, Tel. +49-178-9759012
Attachments:
OpenPGP_signature (application/pgp-signature, 202B) download
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: holger@jakobs.com, 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: <6e9caa48-5e29-a493-d54d-3a97f3893029@jakobs.com>
* 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