pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedImprove "select count(*)" query - takes more than 30 mins for some large tables
15+ messages / 10 participants
[nested] [flat]
* Improve "select count(*)" query - takes more than 30 mins for some large tables
@ 2022-07-11 07:16 Raj kumar <rajkumar820999@gmail.com>
2022-07-11 07:23 ` Re: Improve "select count(*)" query - takes more than 30 mins for some large tables Florents Tselai <florents.tselai@gmail.com>
2022-07-11 08:33 ` Re: Improve "select count(*)" query - takes more than 30 mins for some large tables Holger Jakobs <holger@jakobs.com>
0 siblings, 2 replies; 15+ messages in thread
From: Raj kumar @ 2022-07-11 07:16 UTC (permalink / raw)
To: Pgsql-admin <pgsql-admin@lists.postgresql.org>; +Cc: Raj kumar <rajkumar820999@gmail.com>
Hi,
How can I improve "select count(*)" for larger tables? I'm doing a db
migration and need to validate the data count.
"select count(*) " queries are taking more than 30 minutes for some tables
which is more than the downtime we have.
Will work_mem increase help? or how can i speed up this row count?
Thanks,
Raj
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Improve "select count(*)" query - takes more than 30 mins for some large tables
2022-07-11 07:16 Improve "select count(*)" query - takes more than 30 mins for some large tables Raj kumar <rajkumar820999@gmail.com>
@ 2022-07-11 07:23 ` Florents Tselai <florents.tselai@gmail.com>
2022-07-11 09:15 ` Re: Improve "select count(*)" query - takes more than 30 mins for some large tables Raj kumar <rajkumar820999@gmail.com>
2022-07-12 12:51 ` Re: Improve "select count(*)" query - takes more than 30 mins for some large tables Mladen Gogala <gogala.mladen@gmail.com>
1 sibling, 2 replies; 15+ messages in thread
From: Florents Tselai @ 2022-07-11 07:23 UTC (permalink / raw)
To: Raj kumar <rajkumar820999@gmail.com>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
> On 11 Jul 2022, at 10:16 AM, Raj kumar <rajkumar820999@gmail.com> wrote:
>
> Hi,
>
> How can I improve "select count(*)" for larger tables? I'm doing a db migration and need to validate the data count.
> "select count(*) " queries are taking more than 30 minutes for some tables which is more than the downtime we have.
> Will work_mem increase help? or how can i speed up this row count?
Personally, whenever I’ve had slow count(*) or count (distinct id), eventually
I’ve resorted to Unix tools.
psql “select id from my_table" | sort -u | wc -l
The convenience/performance tradeoff depends heavily on your schema.
After all unix streams don’t know much about your integrity requirements.
>
> Thanks,
> Raj
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Improve "select count(*)" query - takes more than 30 mins for some large tables
2022-07-11 07:16 Improve "select count(*)" query - takes more than 30 mins for some large tables Raj kumar <rajkumar820999@gmail.com>
2022-07-11 07:23 ` Re: Improve "select count(*)" query - takes more than 30 mins for some large tables Florents Tselai <florents.tselai@gmail.com>
@ 2022-07-11 09:15 ` Raj kumar <rajkumar820999@gmail.com>
1 sibling, 0 replies; 15+ messages in thread
From: Raj kumar @ 2022-07-11 09:15 UTC (permalink / raw)
To: Florents Tselai <florents.tselai@gmail.com>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>; Raj kumar <rajkumar820999@gmail.com>
Thanks Florents,
I tried psql -c "select count(*)" . It has brought down the time from 30
minutes to 2 minutes.
Thanks alot.
Thanks Holger,
I'm going to try this query now.
Thanks,
Raj Kumar
On Mon, Jul 11, 2022 at 12:53 PM Florents Tselai <florents.tselai@gmail.com>
wrote:
>
>
> > On 11 Jul 2022, at 10:16 AM, Raj kumar <rajkumar820999@gmail.com> wrote:
> >
> > Hi,
> >
> > How can I improve "select count(*)" for larger tables? I'm doing a db
> migration and need to validate the data count.
> > "select count(*) " queries are taking more than 30 minutes for some
> tables which is more than the downtime we have.
> > Will work_mem increase help? or how can i speed up this row count?
>
> Personally, whenever I’ve had slow count(*) or count (distinct id),
> eventually
> I’ve resorted to Unix tools.
>
> psql “select id from my_table" | sort -u | wc -l
>
> The convenience/performance tradeoff depends heavily on your schema.
> After all unix streams don’t know much about your integrity requirements.
>
> >
> > Thanks,
> > Raj
>
>
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Improve "select count(*)" query - takes more than 30 mins for some large tables
2022-07-11 07:16 Improve "select count(*)" query - takes more than 30 mins for some large tables Raj kumar <rajkumar820999@gmail.com>
2022-07-11 07:23 ` Re: Improve "select count(*)" query - takes more than 30 mins for some large tables Florents Tselai <florents.tselai@gmail.com>
@ 2022-07-12 12:51 ` Mladen Gogala <gogala.mladen@gmail.com>
2022-07-12 14:31 ` Re: Improve "select count(*)" query - takes more than 30 mins for some large tables MichaelDBA Vitale <michaeldba@sqlexec.com>
1 sibling, 1 reply; 15+ messages in thread
From: Mladen Gogala @ 2022-07-12 12:51 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
On 7/11/22 03:23, Florents Tselai wrote:
> psql “select id from my_table" | sort -u | wc -l
That will be a lot slower than just "select count(*) from my_table".
You are delivering data to the user program (psql) and then shipping
them to pipe and then processing the output with "wc". Depending on the
version, PostgreSQL has very reliable parallelism and can do counting
rather quickly. The speed of "select count(*) from my_table" depends on
the speed of I/O. Since the table is big, it cannot be cached in the
file system cache, so all that you have at your disposal is the raw disk
speed. For the smaller machines, NVME is the king. For larger rigs, you
should consider something like Pure, XTremIO or NetApp SolidFire. People
frequently expect database to do miracles with under par hardware.
--
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
https://dbwhisperer.wordpress.com
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Improve "select count(*)" query - takes more than 30 mins for some large tables
2022-07-11 07:16 Improve "select count(*)" query - takes more than 30 mins for some large tables Raj kumar <rajkumar820999@gmail.com>
2022-07-11 07:23 ` Re: Improve "select count(*)" query - takes more than 30 mins for some large tables Florents Tselai <florents.tselai@gmail.com>
2022-07-12 12:51 ` Re: Improve "select count(*)" query - takes more than 30 mins for some large tables Mladen Gogala <gogala.mladen@gmail.com>
@ 2022-07-12 14:31 ` MichaelDBA Vitale <michaeldba@sqlexec.com>
2022-07-12 17:57 ` Re: Improve "select count(*)" query - takes more than 30 mins for some large tables Mladen Gogala <gogala.mladen@gmail.com>
0 siblings, 1 reply; 15+ messages in thread
From: MichaelDBA Vitale @ 2022-07-12 14:31 UTC (permalink / raw)
To: Mladen Gogala <gogala.mladen@gmail.com>; pgsql-admin@lists.postgresql.org
<!doctype html>
<html>
<head>
<meta charset="UTF-8">
</head>
<body>
<div>
Perhaps do an analyze on the table and then select reltuples from pg_class for that table. Might be faster than the select count(*).
</div>
<div class="default-style">
<br>
</div>
<div class="default-style">
Regards,
</div>
<div class="default-style">
Michael Vitale
</div>
<div class="default-style">
<br>
</div>
<blockquote type="cite">
<div>
On 07/12/2022 8:51 AM Mladen Gogala <gogala.mladen@gmail.com> wrote:
</div>
<div>
<br>
</div>
<div>
<br>
</div>
<div class="moz-cite-prefix">
On 7/11/22 03:23, Florents Tselai wrote:
<br>
</div>
<blockquote type="cite">
<pre class="moz-quote-pre">psql “select id from my_table" | sort -u | wc -l
</pre>
</blockquote>
<p>That will be a lot slower than just "select count(*) from my_table". You are delivering data to the user program (psql) and then shipping them to pipe and then processing the output with "wc". Depending on the version, PostgreSQL has very reliable parallelism and can do counting rather quickly. The speed of "select count(*) from my_table" depends on the speed of I/O. Since the table is big, it cannot be cached in the file system cache, so all that you have at your disposal is the raw disk speed. For the smaller machines, NVME is the king. For larger rigs, you should consider something like Pure, XTremIO or NetApp SolidFire. People frequently expect database to do miracles with under par hardware.<br></p>
<pre class="moz-signature">--
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
<a href="https://dbwhisperer.wordpress.com"; class="moz-txt-link-freetext">https://dbwhisperer.wordpress.com</a;
</pre>
</blockquote>
</body>
</html>
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Improve "select count(*)" query - takes more than 30 mins for some large tables
2022-07-11 07:16 Improve "select count(*)" query - takes more than 30 mins for some large tables Raj kumar <rajkumar820999@gmail.com>
2022-07-11 07:23 ` Re: Improve "select count(*)" query - takes more than 30 mins for some large tables Florents Tselai <florents.tselai@gmail.com>
2022-07-12 12:51 ` Re: Improve "select count(*)" query - takes more than 30 mins for some large tables Mladen Gogala <gogala.mladen@gmail.com>
2022-07-12 14:31 ` Re: Improve "select count(*)" query - takes more than 30 mins for some large tables MichaelDBA Vitale <michaeldba@sqlexec.com>
@ 2022-07-12 17:57 ` Mladen Gogala <gogala.mladen@gmail.com>
2022-07-12 18:13 ` RE: [EXT] Re: Improve "select count(*)" query - takes more than 30 mins for some large tables Pierson Patricia L (Contractor) <Patricia.L.Pierson@irs.gov>
0 siblings, 1 reply; 15+ messages in thread
From: Mladen Gogala @ 2022-07-12 17:57 UTC (permalink / raw)
To: MichaelDBA Vitale <michaeldba@sqlexec.com>; +Cc: pgsql-admin@lists.postgresql.org
What's wrong with parallelism? That's why it was invented. If you really
need an accurate count at moment's notice, create a trigger to maintain it.
Regards
On Tue, Jul 12, 2022, 10:31 AM MichaelDBA Vitale <michaeldba@sqlexec.com>
wrote:
> Perhaps do an analyze on the table and then select reltuples from pg_class
> for that table. Might be faster than the select count(*).
>
> Regards,
> Michael Vitale
>
> On 07/12/2022 8:51 AM Mladen Gogala <gogala.mladen@gmail.com> wrote:
>
>
> On 7/11/22 03:23, Florents Tselai wrote:
>
> psql “select id from my_table" | sort -u | wc -l
>
> That will be a lot slower than just "select count(*) from my_table". You
> are delivering data to the user program (psql) and then shipping them to
> pipe and then processing the output with "wc". Depending on the version,
> PostgreSQL has very reliable parallelism and can do counting rather
> quickly. The speed of "select count(*) from my_table" depends on the speed
> of I/O. Since the table is big, it cannot be cached in the file system
> cache, so all that you have at your disposal is the raw disk speed. For the
> smaller machines, NVME is the king. For larger rigs, you should consider
> something like Pure, XTremIO or NetApp SolidFire. People frequently expect
> database to do miracles with under par hardware.
>
> --
> Mladen Gogala
> Database Consultant
> Tel: (347) 321-1217https://dbwhisperer.wordpress.com
>
>
^ permalink raw reply [nested|flat] 15+ messages in thread
* RE: [EXT] Re: Improve "select count(*)" query - takes more than 30 mins for some large tables
2022-07-11 07:16 Improve "select count(*)" query - takes more than 30 mins for some large tables Raj kumar <rajkumar820999@gmail.com>
2022-07-11 07:23 ` Re: Improve "select count(*)" query - takes more than 30 mins for some large tables Florents Tselai <florents.tselai@gmail.com>
2022-07-12 12:51 ` Re: Improve "select count(*)" query - takes more than 30 mins for some large tables Mladen Gogala <gogala.mladen@gmail.com>
2022-07-12 14:31 ` Re: Improve "select count(*)" query - takes more than 30 mins for some large tables MichaelDBA Vitale <michaeldba@sqlexec.com>
2022-07-12 17:57 ` Re: Improve "select count(*)" query - takes more than 30 mins for some large tables Mladen Gogala <gogala.mladen@gmail.com>
@ 2022-07-12 18:13 ` Pierson Patricia L (Contractor) <Patricia.L.Pierson@irs.gov>
2022-07-12 18:25 ` Re: [EXT] Re: Improve "select count(*)" query - takes more than 30 mins for some large tables Holger Jakobs <holger@jakobs.com>
2022-07-12 18:25 ` RE: [EXT] Re: Improve "select count(*)" query - takes more than 30 mins for some large tables MichaelDBA Vitale <michaeldba@sqlexec.com>
0 siblings, 2 replies; 15+ messages in thread
From: Pierson Patricia L (Contractor) @ 2022-07-12 18:13 UTC (permalink / raw)
To: gogala.mladen@gmail.com <gogala.mladen@gmail.com>; michaeldba@sqlexec.com <michaeldba@sqlexec.com>; +Cc: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
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;
From: Mladen Gogala <gogala.mladen@gmail.com>
Sent: Tuesday, July 12, 2022 11:58 AM
To: MichaelDBA Vitale <michaeldba@sqlexec.com>
Cc: pgsql-admin@lists.postgresql.org
Subject: [EXT] Re: Improve "select count(*)" query - takes more than 30 mins
for some large tables
What's wrong with parallelism? That's why it was invented. If you really need
an accurate count at moment's notice, create a trigger to maintain it.
Regards
On Tue, Jul 12, 2022, 10:31 AM MichaelDBA Vitale <michaeldba@sqlexec.com
<mailto:michaeldba@sqlexec.com> > wrote:
Perhaps do an analyze on the table and then select reltuples from pg_class for
that table. Might be faster than the select count(*).
Regards,
Michael Vitale
On 07/12/2022 8:51 AM Mladen Gogala <gogala.mladen@gmail.com
<mailto:gogala.mladen@gmail.com> > wrote:
On 7/11/22 03:23, Florents Tselai wrote:
psql “select id from my_table" | sort -u | wc -l
That will be a lot slower than just "select count(*) from my_table". You are
delivering data to the user program (psql) and then shipping them to pipe and
then processing the output with "wc". Depending on the version, PostgreSQL has
very reliable parallelism and can do counting rather quickly. The speed of
"select count(*) from my_table" depends on the speed of I/O. Since the table
is big, it cannot be cached in the file system cache, so all that you have at
your disposal is the raw disk speed. For the smaller machines, NVME is the
king. For larger rigs, you should consider something like Pure, XTremIO or
NetApp SolidFire. People frequently expect database to do miracles with under
par hardware.
--
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
https://dbwhisperer.wordpress.com
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: [EXT] Re: Improve "select count(*)" query - takes more than 30 mins for some large tables
2022-07-11 07:16 Improve "select count(*)" query - takes more than 30 mins for some large tables Raj kumar <rajkumar820999@gmail.com>
2022-07-11 07:23 ` Re: Improve "select count(*)" query - takes more than 30 mins for some large tables Florents Tselai <florents.tselai@gmail.com>
2022-07-12 12:51 ` Re: Improve "select count(*)" query - takes more than 30 mins for some large tables Mladen Gogala <gogala.mladen@gmail.com>
2022-07-12 14:31 ` Re: Improve "select count(*)" query - takes more than 30 mins for some large tables MichaelDBA Vitale <michaeldba@sqlexec.com>
2022-07-12 17:57 ` Re: Improve "select count(*)" query - takes more than 30 mins for some large tables Mladen Gogala <gogala.mladen@gmail.com>
2022-07-12 18:13 ` RE: [EXT] Re: Improve "select count(*)" query - takes more than 30 mins for some large tables Pierson Patricia L (Contractor) <Patricia.L.Pierson@irs.gov>
@ 2022-07-12 18:25 ` Holger Jakobs <holger@jakobs.com>
1 sibling, 0 replies; 15+ messages in thread
From: Holger Jakobs @ 2022-07-12 18:25 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 15+ messages in thread
* RE: [EXT] Re: Improve "select count(*)" query - takes more than 30 mins for some large tables
2022-07-11 07:16 Improve "select count(*)" query - takes more than 30 mins for some large tables Raj kumar <rajkumar820999@gmail.com>
2022-07-11 07:23 ` Re: Improve "select count(*)" query - takes more than 30 mins for some large tables Florents Tselai <florents.tselai@gmail.com>
2022-07-12 12:51 ` Re: Improve "select count(*)" query - takes more than 30 mins for some large tables Mladen Gogala <gogala.mladen@gmail.com>
2022-07-12 14:31 ` Re: Improve "select count(*)" query - takes more than 30 mins for some large tables MichaelDBA Vitale <michaeldba@sqlexec.com>
2022-07-12 17:57 ` Re: Improve "select count(*)" query - takes more than 30 mins for some large tables Mladen Gogala <gogala.mladen@gmail.com>
2022-07-12 18:13 ` RE: [EXT] Re: Improve "select count(*)" query - takes more than 30 mins for some large tables Pierson Patricia L (Contractor) <Patricia.L.Pierson@irs.gov>
@ 2022-07-12 18:25 ` MichaelDBA Vitale <michaeldba@sqlexec.com>
2022-07-12 19:13 ` Re: [EXT] Re: Improve "select count(*)" query - takes more than 30 mins for some large tables Laurenz Albe <laurenz.albe@cybertec.at>
2022-07-13 02:54 ` Re: [EXT] Re: Improve "select count(*)" query - takes more than 30 mins for some large tables Mladen Gogala <gogala.mladen@gmail.com>
1 sibling, 2 replies; 15+ messages in thread
From: MichaelDBA Vitale @ 2022-07-12 18:25 UTC (permalink / raw)
To: Pierson Patricia L (Contractor) <Patricia.L.Pierson@irs.gov>; gogala.mladen@gmail.com <gogala.mladen@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
<!doctype html>
<html>
<head>
<meta charset="UTF-8">
</head>
<body>
<div>
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.
</div>
<div class="default-style">
<br>
</div>
<div class="default-style">
Regards,
</div>
<div class="default-style">
Michael Vitale
</div>
<blockquote type="cite">
<div>
On 07/12/2022 2:13 PM Pierson Patricia L (Contractor) <patricia.l.pierson@irs.gov> wrote:
</div>
<div>
<br>
</div>
<div>
<br>
</div>
<div class="WordSection1">
<p class="MsoNormal"><span style="font-size: 12.0pt; font-family: 'Verdana',sans-serif;">Hello,</span></p>
<p class="MsoNormal"><span style="font-size: 12.0pt; font-family: 'Verdana',sans-serif;"> </span></p>
<p class="MsoNormal"><span style="font-size: 12.0pt; font-family: 'Verdana',sans-serif;">Do a count on the primary key. Will force index access and you don’t access the entire row which may be very long.</span></p>
<p class="MsoNormal"><span style="font-size: 12.0pt; font-family: 'Verdana',sans-serif;">LIKE : select count(ID) from my_table;</span></p>
<p class="MsoNormal"><span style="font-size: 12.0pt; font-family: 'Verdana',sans-serif;"> </span></p>
<div style="border: none; border-top: solid #E1E1E1 1.0pt; padding: 3.0pt 0in 0in 0in;">
<p class="MsoNormal"><strong>From:</strong> Mladen Gogala <gogala.mladen@gmail.com> <br><strong>Sent:</strong> Tuesday, July 12, 2022 11:58 AM<br><strong>To:</strong> MichaelDBA Vitale <michaeldba@sqlexec.com><br><strong>Cc:</strong> pgsql-admin@lists.postgresql.org<br><strong>Subject:</strong> [EXT] Re: Improve "select count(*)" query - takes more than 30 mins for some large tables</p>
</div>
<p class="MsoNormal"><br></p>
<div>
<p class="MsoNormal">What's wrong with parallelism? That's why it was invented. If you really need an accurate count at moment's notice, create a trigger to maintain it.</p>
<div>
<p class="MsoNormal">Regards</p>
</div>
</div>
<p class="MsoNormal"><br></p>
<div>
<div>
<p class="MsoNormal">On Tue, Jul 12, 2022, 10:31 AM MichaelDBA Vitale <<a href="mailto:michaeldba@sqlexec.com">michaeldba@sqlexec.com</a>> wrote:</p>
</div>
<blockquote>
<div>
<div>
<p class="MsoNormal">Perhaps do an analyze on the table and then select reltuples from pg_class for that table. Might be faster than the select count(*).</p>
</div>
<div>
<p class="MsoNormal"><br></p>
</div>
<div>
<p class="MsoNormal">Regards,</p>
</div>
<div>
<p class="MsoNormal">Michael Vitale</p>
</div>
<div>
<p class="MsoNormal"><br></p>
</div>
<blockquote>
<div>
<p class="MsoNormal">On 07/12/2022 8:51 AM Mladen Gogala <<a target="_blank" href="mailto:gogala.mladen@gmail.com" rel="noopener">gogala.mladen@gmail.com</a>> wrote:</p>
</div>
<div>
<p class="MsoNormal"><br></p>
</div>
<div>
<p class="MsoNormal"><br></p>
</div>
<div>
<p class="MsoNormal">On 7/11/22 03:23, Florents Tselai wrote:</p>
</div>
<blockquote>
<pre>psql “select id from my_table" | sort -u | wc -l </pre>
</blockquote>
<p>That will be a lot slower than just "select count(*) from my_table". You are delivering data to the user program (psql) and then shipping them to pipe and then processing the output with "wc". Depending on the version, PostgreSQL has very reliable parallelism and can do counting rather quickly. The speed of "select count(*) from my_table" depends on the speed of I/O. Since the table is big, it cannot be cached in the file system cache, so all that you have at your disposal is the raw disk speed. For the smaller machines, NVME is the king. For larger rigs, you should consider something like Pure, XTremIO or NetApp SolidFire. People frequently expect database to do miracles with under par hardware.</p>
<pre>-- </pre>
<pre>Mladen Gogala</pre>
<pre>Database Consultant</pre>
<pre>Tel: (347) 321-1217</pre>
<pre><a target="_blank" href="https://dbwhisperer.wordpress.com"; rel="noopener">https://dbwhisperer.wordpress.com</a></pre;
</blockquote>
</div>
</blockquote>
</div>
</div>
</blockquote>
</body>
</html>
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: [EXT] Re: Improve "select count(*)" query - takes more than 30 mins for some large tables
2022-07-11 07:16 Improve "select count(*)" query - takes more than 30 mins for some large tables Raj kumar <rajkumar820999@gmail.com>
2022-07-11 07:23 ` Re: Improve "select count(*)" query - takes more than 30 mins for some large tables Florents Tselai <florents.tselai@gmail.com>
2022-07-12 12:51 ` Re: Improve "select count(*)" query - takes more than 30 mins for some large tables Mladen Gogala <gogala.mladen@gmail.com>
2022-07-12 14:31 ` Re: Improve "select count(*)" query - takes more than 30 mins for some large tables MichaelDBA Vitale <michaeldba@sqlexec.com>
2022-07-12 17:57 ` Re: Improve "select count(*)" query - takes more than 30 mins for some large tables Mladen Gogala <gogala.mladen@gmail.com>
2022-07-12 18:13 ` RE: [EXT] Re: Improve "select count(*)" query - takes more than 30 mins for some large tables Pierson Patricia L (Contractor) <Patricia.L.Pierson@irs.gov>
2022-07-12 18:25 ` RE: [EXT] Re: Improve "select count(*)" query - takes more than 30 mins for some large tables MichaelDBA Vitale <michaeldba@sqlexec.com>
@ 2022-07-12 19:13 ` Laurenz Albe <laurenz.albe@cybertec.at>
2022-07-13 02:12 ` Re: [EXT] Re: Improve "select count(*)" query - takes more than 30 mins for some large tables MichaelDBA <MichaelDBA@sqlexec.com>
1 sibling, 1 reply; 15+ messages in thread
From: Laurenz Albe @ 2022-07-12 19:13 UTC (permalink / raw)
To: MichaelDBA Vitale <michaeldba@sqlexec.com>; Pierson Patricia L (Contractor) <Patricia.L.Pierson@irs.gov>; +Cc: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: [EXT] Re: Improve "select count(*)" query - takes more than 30 mins for some large tables
2022-07-11 07:16 Improve "select count(*)" query - takes more than 30 mins for some large tables Raj kumar <rajkumar820999@gmail.com>
2022-07-11 07:23 ` Re: Improve "select count(*)" query - takes more than 30 mins for some large tables Florents Tselai <florents.tselai@gmail.com>
2022-07-12 12:51 ` Re: Improve "select count(*)" query - takes more than 30 mins for some large tables Mladen Gogala <gogala.mladen@gmail.com>
2022-07-12 14:31 ` Re: Improve "select count(*)" query - takes more than 30 mins for some large tables MichaelDBA Vitale <michaeldba@sqlexec.com>
2022-07-12 17:57 ` Re: Improve "select count(*)" query - takes more than 30 mins for some large tables Mladen Gogala <gogala.mladen@gmail.com>
2022-07-12 18:13 ` RE: [EXT] Re: Improve "select count(*)" query - takes more than 30 mins for some large tables Pierson Patricia L (Contractor) <Patricia.L.Pierson@irs.gov>
2022-07-12 18:25 ` RE: [EXT] Re: Improve "select count(*)" query - takes more than 30 mins for some large tables MichaelDBA Vitale <michaeldba@sqlexec.com>
2022-07-12 19:13 ` Re: [EXT] Re: Improve "select count(*)" query - takes more than 30 mins for some large tables Laurenz Albe <laurenz.albe@cybertec.at>
@ 2022-07-13 02:12 ` MichaelDBA <MichaelDBA@sqlexec.com>
2022-07-13 02:28 ` Re: [EXT] Improve "select count(*)" query - takes more than 30 mins for some large tables Scott Ribe <scott_ribe@elevated-dev.com>
0 siblings, 1 reply; 15+ messages in thread
From: MichaelDBA @ 2022-07-13 02:12 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; +Cc: Pierson Patricia L (Contractor) <Patricia.L.Pierson@irs.gov>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
Laurenz,
Oh really? Even if it did an INDEX ONLY SCAN, it would still be slower
because it still has to access the heap in a random I/O kinda way (like
I said-->"The heap always gets accessed for select counts") because
visibility info is only found in the HEAP not the index.
Laurenz Albe wrote on 7/12/2022 3:13 PM:
> I'd say that both statements are wrong:
Regards,
Michael Vitale
Michaeldba@sqlexec.com <mailto:michaelvitale@sqlexec.com>
703-600-9343
Attachments:
pgadvanced3.jpg (image/jpeg, 20.6K) download
view image
* Re: [EXT] Improve "select count(*)" query - takes more than 30 mins for some large tables
2022-07-11 07:16 Improve "select count(*)" query - takes more than 30 mins for some large tables Raj kumar <rajkumar820999@gmail.com>
2022-07-11 07:23 ` Re: Improve "select count(*)" query - takes more than 30 mins for some large tables Florents Tselai <florents.tselai@gmail.com>
2022-07-12 12:51 ` Re: Improve "select count(*)" query - takes more than 30 mins for some large tables Mladen Gogala <gogala.mladen@gmail.com>
2022-07-12 14:31 ` Re: Improve "select count(*)" query - takes more than 30 mins for some large tables MichaelDBA Vitale <michaeldba@sqlexec.com>
2022-07-12 17:57 ` Re: Improve "select count(*)" query - takes more than 30 mins for some large tables Mladen Gogala <gogala.mladen@gmail.com>
2022-07-12 18:13 ` RE: [EXT] Re: Improve "select count(*)" query - takes more than 30 mins for some large tables Pierson Patricia L (Contractor) <Patricia.L.Pierson@irs.gov>
2022-07-12 18:25 ` RE: [EXT] Re: Improve "select count(*)" query - takes more than 30 mins for some large tables MichaelDBA Vitale <michaeldba@sqlexec.com>
2022-07-12 19:13 ` Re: [EXT] Re: Improve "select count(*)" query - takes more than 30 mins for some large tables Laurenz Albe <laurenz.albe@cybertec.at>
2022-07-13 02:12 ` Re: [EXT] Re: Improve "select count(*)" query - takes more than 30 mins for some large tables MichaelDBA <MichaelDBA@sqlexec.com>
@ 2022-07-13 02:28 ` Scott Ribe <scott_ribe@elevated-dev.com>
2022-07-13 13:16 ` Re: [EXT] Improve "select count(*)" query - takes more than 30 mins for some large tables Ron <ronljohnsonjr@gmail.com>
0 siblings, 1 reply; 15+ messages in thread
From: Scott Ribe @ 2022-07-13 02:28 UTC (permalink / raw)
To: MichaelDBA <MichaelDBA@sqlexec.com>; +Cc: Laurenz Albe <laurenz.albe@cybertec.at>; Pierson Patricia L (Contractor) <Patricia.L.Pierson@irs.gov>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
> On Jul 12, 2022, at 8:12 PM, MichaelDBA <MichaelDBA@sqlexec.com> wrote:
>
> Oh really? Even if it did an INDEX ONLY SCAN, it would still be slower because it still has to access the heap in a random I/O kinda way (like I said-->"The heap always gets accessed for select counts") because visibility info is only found in the HEAP not the index.
This changed in 9.something. There is now a visibility map, which can, for data that hasn't changed recently, greatly reduce the amount of access required to the heap to determine visibility.
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: [EXT] Improve "select count(*)" query - takes more than 30 mins for some large tables
2022-07-11 07:16 Improve "select count(*)" query - takes more than 30 mins for some large tables Raj kumar <rajkumar820999@gmail.com>
2022-07-11 07:23 ` Re: Improve "select count(*)" query - takes more than 30 mins for some large tables Florents Tselai <florents.tselai@gmail.com>
2022-07-12 12:51 ` Re: Improve "select count(*)" query - takes more than 30 mins for some large tables Mladen Gogala <gogala.mladen@gmail.com>
2022-07-12 14:31 ` Re: Improve "select count(*)" query - takes more than 30 mins for some large tables MichaelDBA Vitale <michaeldba@sqlexec.com>
2022-07-12 17:57 ` Re: Improve "select count(*)" query - takes more than 30 mins for some large tables Mladen Gogala <gogala.mladen@gmail.com>
2022-07-12 18:13 ` RE: [EXT] Re: Improve "select count(*)" query - takes more than 30 mins for some large tables Pierson Patricia L (Contractor) <Patricia.L.Pierson@irs.gov>
2022-07-12 18:25 ` RE: [EXT] Re: Improve "select count(*)" query - takes more than 30 mins for some large tables MichaelDBA Vitale <michaeldba@sqlexec.com>
2022-07-12 19:13 ` Re: [EXT] Re: Improve "select count(*)" query - takes more than 30 mins for some large tables Laurenz Albe <laurenz.albe@cybertec.at>
2022-07-13 02:12 ` Re: [EXT] Re: Improve "select count(*)" query - takes more than 30 mins for some large tables MichaelDBA <MichaelDBA@sqlexec.com>
2022-07-13 02:28 ` Re: [EXT] Improve "select count(*)" query - takes more than 30 mins for some large tables Scott Ribe <scott_ribe@elevated-dev.com>
@ 2022-07-13 13:16 ` Ron <ronljohnsonjr@gmail.com>
0 siblings, 0 replies; 15+ messages in thread
From: Ron @ 2022-07-13 13:16 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
On 7/12/22 21:28, Scott Ribe wrote:
>> On Jul 12, 2022, at 8:12 PM, MichaelDBA <MichaelDBA@sqlexec.com> wrote:
>>
>> Oh really? Even if it did an INDEX ONLY SCAN, it would still be slower because it still has to access the heap in a random I/O kinda way (like I said-->"The heap always gets accessed for select counts") because visibility info is only found in the HEAP not the index.
> This changed in 9.something. There is now a visibility map, which can, for data that hasn't changed recently,
How recently is "recently"? And does VACUUM clean it up?
--
Angular momentum makes the world go 'round.
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: [EXT] Re: Improve "select count(*)" query - takes more than 30 mins for some large tables
2022-07-11 07:16 Improve "select count(*)" query - takes more than 30 mins for some large tables Raj kumar <rajkumar820999@gmail.com>
2022-07-11 07:23 ` Re: Improve "select count(*)" query - takes more than 30 mins for some large tables Florents Tselai <florents.tselai@gmail.com>
2022-07-12 12:51 ` Re: Improve "select count(*)" query - takes more than 30 mins for some large tables Mladen Gogala <gogala.mladen@gmail.com>
2022-07-12 14:31 ` Re: Improve "select count(*)" query - takes more than 30 mins for some large tables MichaelDBA Vitale <michaeldba@sqlexec.com>
2022-07-12 17:57 ` Re: Improve "select count(*)" query - takes more than 30 mins for some large tables Mladen Gogala <gogala.mladen@gmail.com>
2022-07-12 18:13 ` RE: [EXT] Re: Improve "select count(*)" query - takes more than 30 mins for some large tables Pierson Patricia L (Contractor) <Patricia.L.Pierson@irs.gov>
2022-07-12 18:25 ` RE: [EXT] Re: Improve "select count(*)" query - takes more than 30 mins for some large tables MichaelDBA Vitale <michaeldba@sqlexec.com>
@ 2022-07-13 02:54 ` Mladen Gogala <gogala.mladen@gmail.com>
1 sibling, 0 replies; 15+ messages in thread
From: Mladen Gogala @ 2022-07-13 02:54 UTC (permalink / raw)
To: MichaelDBA Vitale <michaeldba@sqlexec.com>; Pierson Patricia L (Contractor) <Patricia.L.Pierson@irs.gov>; +Cc: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
On 7/12/22 14:25, MichaelDBA Vitale wrote:
> 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.
>
> Regards,
> Michael Vitale
Well, not always:
mgogala=# explain select count(*) from emp;
QUERY PLAN
----------------------------------------------------------
Aggregate (cost=1.18..1.19 rows=1 width=8)
-> Seq Scan on emp (cost=0.00..1.14 rows=14 width=0)
(2 rows)
That is the classic "select count(*)". Now, let's see PK:
mgogala=# \d emp
Table "mgogala.emp"
Column | Type | Collation | Nullable | Default
----------+-----------------------------+-----------+----------+---------
empno | smallint | | not null |
ename | character varying(10) | | |
job | character varying(9) | | |
mgr | smallint | | |
hiredate | timestamp without time zone | | |
sal | double precision | | |
comm | double precision | | |
deptno | smallint | | |
Indexes:
"emp_pkey" PRIMARY KEY, btree (empno)
Foreign-key constraints:
"fk_deptno" FOREIGN KEY (deptno) REFERENCES dept(deptno)
Now, let's do scan on "count(empno)":
mgogala=# explain select count(empno) from emp;
QUERY PLAN
----------------------------------------------------------
Aggregate (cost=1.18..1.19 rows=1 width=8)
-> Seq Scan on emp (cost=0.00..1.14 rows=14 width=2)
(2 rows)
So far, so good. Sequential scan, despite the existence of the NOT NULL
primary key. Now comes the twist:
mgogala=# set enable_seqscan=off;
SET
mgogala=# explain select count(empno) from emp;
QUERY PLAN
--------------------------------------------------------------------------------
-
Aggregate (cost=12.38..12.39 rows=1 width=8)
-> Index Only Scan using emp_pkey on emp (cost=0.14..12.35 rows=14
width=2)
(2 rows)
Voila, we've got index only "count" scan. So, in some cases Postgres
does do index only scan. What is surprising is that pg_hint_plan doesn't
produce the desired results:
mgogala=# explain /*+ IndexScan(emp emp_pkey) */ select count(empno)
from emp;
QUERY PLAN
-------------------------------------------------------------------------------
Aggregate (cost=10000000001.17..10000000001.18 rows=1 width=8)
-> Seq Scan on emp (cost=10000000000.00..10000000001.14 rows=14
width=2)
JIT:
Functions: 3
Options: Inlining true, Optimization true, Expressions true,
Deforming true
(5 rows)
This is strange in more ways than one:
1. The hint didn't work, Postgres still performed sequential scan
2. JIT got involved for the reasons that are completely unclear.
3. The cost is enormous, despite the fact the the "emp" table has only
14 rows and is fully contained in a single block.
However, if we do GUC hint, the result behaves as expected:
mgogala=# explain /*+ set(enable_seqscan off) */ select count(empno)
from emp e;
QUERY PLAN
--------------------------------------------------------------------------------
---
Aggregate (cost=12.38..12.39 rows=1 width=8)
-> Index Only Scan using emp_pkey on emp e (cost=0.14..12.35
rows=14 width=2)
(2 rows)
So, it is possible to force index only scan. However, from purely
theoretic point of view, the speed difference is questionable. Indexes
are stored differently than tables, they have branch blocks and data
bocks with an ample free space in the data blocks to prevent block
splits and index level increase, with mandatory re-balancing. Indexes
are actually quite large structures, sometimes even larger than the
underlying tables. In general, going after the index-only scan doesn't
look like a good strategy.
After all this work, I also have a question: does PostgreSQL read index
blocks in batches or it does single block scans? Some other databases
have 2 events: "db file scattered read" for sequential scan (called
"full table scan" in that specific vernacular) and "db file sequential
read" for index scans. Those database only do single block reads from
indexes while the tables can be read using batches of blocks. Does
PostgreSQL do the same thing?
--
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
https://dbwhisperer.wordpress.com
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Improve "select count(*)" query - takes more than 30 mins for some large tables
2022-07-11 07:16 Improve "select count(*)" query - takes more than 30 mins for some large tables Raj kumar <rajkumar820999@gmail.com>
@ 2022-07-11 08:33 ` Holger Jakobs <holger@jakobs.com>
1 sibling, 0 replies; 15+ messages in thread
From: Holger Jakobs @ 2022-07-11 08:33 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
Hi Raj,
Since PostgreSQL uses MVCC there is no current number of rows as an
absolute truth. Every COUNT(*) has to check the visibility of each row
for the current transaction. This makes it slow for large tables.
If you don't need the exact figure but just an estimation, you can get
this from the internal statistics tables. After an ANALYZE they are
close to the "real value".
This show the number of rows for all non-empty tables in all visible
schemas.
SELECT nspname AS schemaname,relname,reltuples::numeric
FROM pg_class C LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
WHERE
nspname IN (SELECT unnest(current_schemas(false))) AND
relkind='r' AND reltuples > 0
ORDER BY reltuples DESC;
Regards,
Holger
Am 11.07.22 um 09:16 schrieb Raj kumar:
> Hi,
>
> How can I improve "select count(*)" for larger tables? I'm doing a db
> migration and need to validate the data count.
> "select count(*) " queries are taking more than 30 minutes for some
> tables which is more than the downtime we have.
> Will work_mem increase help? or how can i speed up this row count?
>
> Thanks,
> Raj
--
Holger Jakobs, Bergisch Gladbach, Tel. +49-178-9759012
Attachments:
OpenPGP_signature (application/pgp-signature, 202B) download
^ permalink raw reply [nested|flat] 15+ messages in thread
end of thread, other threads:[~2022-07-13 13:16 UTC | newest]
Thread overview: 15+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2022-07-11 07:16 Improve "select count(*)" query - takes more than 30 mins for some large tables Raj kumar <rajkumar820999@gmail.com>
2022-07-11 07:23 ` Florents Tselai <florents.tselai@gmail.com>
2022-07-11 09:15 ` Raj kumar <rajkumar820999@gmail.com>
2022-07-12 12:51 ` Mladen Gogala <gogala.mladen@gmail.com>
2022-07-12 14:31 ` MichaelDBA Vitale <michaeldba@sqlexec.com>
2022-07-12 17:57 ` Mladen Gogala <gogala.mladen@gmail.com>
2022-07-12 18:13 ` Pierson Patricia L (Contractor) <Patricia.L.Pierson@irs.gov>
2022-07-12 18:25 ` Holger Jakobs <holger@jakobs.com>
2022-07-12 18:25 ` MichaelDBA Vitale <michaeldba@sqlexec.com>
2022-07-12 19:13 ` Laurenz Albe <laurenz.albe@cybertec.at>
2022-07-13 02:12 ` MichaelDBA <MichaelDBA@sqlexec.com>
2022-07-13 02:28 ` Scott Ribe <scott_ribe@elevated-dev.com>
2022-07-13 13:16 ` Ron <ronljohnsonjr@gmail.com>
2022-07-13 02:54 ` Mladen Gogala <gogala.mladen@gmail.com>
2022-07-11 08:33 ` Holger Jakobs <holger@jakobs.com>
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