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

[Holger Jakobs <holger@xxxxxxxxxx>]

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

Attachment: OpenPGP_signature
Description: OpenPGP digital signature