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

[Luca Maranzano]

Hi Tatsuo,
actually this behaviour was reported to me by developers and I blindly
trusted their analysis, so I asked them how to reproduce the problem.
In the meantime I performed the same exact test with the EXPLAIN and came
to the same conclusion :-)
I'll keep you posted.
Thanks a lot
Regards
Luca


On Mon, Oct 25, 2021 at 11:53 AM Tatsuo Ishii <ishii at sraoss.co.jp> wrote:

> > 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
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.pgpool.net/pipermail/pgpool-general/attachments/20211025/91e22002/attachment.htm>