[pgpool-general: 7824] Re: Why running select count(*) from a huge table via pgpool is using only one worker?

[Tatsuo Ishii]

> Hi all,
> we noticed that if we run a select count(*) from a huge table connecting to
> pgpool, postgres will start only 1 worker, while if we start the same query
> with a direct connection to the DB postgres will start 5 workers, so
> obviously execution time will be 5x time slower via pgpool.
> PGpool should pass the statement to postgres so the execution on the
> backend should be the same.
> 
> I'm missing the reason for this behaviour, can anyone explain?
> 
> Current versions of the components are PostgreSQL 12.8 and PGPool-II 4.2.3.

That's really strang and of course I was not able to reproduce it.

[directly connect to PostgreSQL]

t-ishii$ psql -p 11002 test
psql (14.0)
Type "help" for help.

test=# explain analyze select count(*) from pgbench_accounts;
                                                                                      QUERY PLAN                                                                                       
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Finalize Aggregate  (cost=22188.97..22188.98 rows=1 width=8) (actual time=74.360..77.116 rows=1 loops=1)
   ->  Gather  (cost=22188.76..22188.97 rows=2 width=8) (actual time=74.301..77.108 rows=3 loops=1)
         Workers Planned: 2
         Workers Launched: 2
         ->  Partial Aggregate  (cost=21188.76..21188.77 rows=1 width=8) (actual time=72.055..72.056 rows=1 loops=3)
               ->  Parallel Index Only Scan using pgbench_accounts_pkey on pgbench_accounts  (cost=0.42..20147.09 rows=416667 width=0) (actual time=0.038..49.776 rows=333333 loops=3)
                     Heap Fetches: 0
 Planning Time: 0.288 ms
 Execution Time: 77.182 ms
(9 rows)

[via Pgpool-II]

t-ishii$ psql -p 11000 test
psql (14.0)
Type "help" for help.

test=# explain analyze select count(*) from pgbench_accounts;
                                                                                      QUERY PLAN                                                                                       
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Finalize Aggregate  (cost=22188.97..22188.98 rows=1 width=8) (actual time=82.885..84.496 rows=1 loops=1)
   ->  Gather  (cost=22188.76..22188.97 rows=2 width=8) (actual time=82.829..84.487 rows=3 loops=1)
         Workers Planned: 2
         Workers Launched: 2
         ->  Partial Aggregate  (cost=21188.76..21188.77 rows=1 width=8) (actual time=80.456..80.457 rows=1 loops=3)
               ->  Parallel Index Only Scan using pgbench_accounts_pkey on pgbench_accounts  (cost=0.42..20147.09 rows=416667 width=0) (actual time=0.053..58.590 rows=333333 loops=3)
                     Heap Fetches: 0
 Planning Time: 0.346 ms
 Execution Time: 84.571 ms
(9 rows)

How do you issue the query exactly?
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp