Parallel Append Query

From: Luís Roberto Weck <luisroberto(at)siscobra(dot)com(dot)br>
To: pgsql-general(at)postgresql(dot)org
Subject: Parallel Append Query
Date: 2020-02-21 11:16:29
Message-ID: 5d461f71-85fd-c76c-323a-2f3604ff8fe3@siscobra.com.br
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi!

I'm seeing varying number of rows removed by filter on a parallel append
query:

CREATE TABLE usuario (
  id bigint generated always as identity,
  nome text
);

INSERT INTO usuario (nome)
SELECT md5(seq)::text
    FROM generate_series(1,500000,1) tab(seq);

ANALYZE usuario;

EXPLAIN ANALYZE
(SELECT * FROM usuario WHERE nome = 'c4ca4238a0b923820dcc509a6f75849b')
UNION ALL
(SELECT * FROM usuario WHERE nome = 'c81e728d9d4c2f636f067f89cc14862c')

Gather  (cost=1000.00..15574.56 rows=2 width=41) (actual
time=0.320..56.154 rows=2 loops=1)
  Workers Planned: 2
  Workers Launched: 2
  ->  Parallel Append  (cost=0.00..14574.36 rows=2 width=41) (actual
time=13.449..47.899 rows=1 loops=3)
        ->  Parallel Seq Scan on usuario (cost=0.00..7287.17 rows=1
width=41) (actual time=8.934..25.476 rows=0 *loops=3*)
              Filter: (nome = 'c81e728d9d4c2f636f067f89cc14862c'::text)
*Rows Removed by Filter: 166666*
        ->  Parallel Seq Scan on usuario usuario_1 (cost=0.00..7287.17
rows=1 width=41) (actual time=13.470..33.630 rows=0 *loops=2*)
              Filter: (nome = 'c4ca4238a0b923820dcc509a6f75849b'::text)
*Rows Removed by Filter: 250000*
Planning Time: 0.106 ms
Execution Time: 56.184 ms

The execution plan varies just by running the query a few times, and it
goes back and forth between these two:

Gather  (cost=1000.00..15574.56 rows=2 width=41) (actual
time=0.341..55.499 rows=2 loops=1)
  Workers Planned: 2
  Workers Launched: 2
  ->  Parallel Append  (cost=0.00..14574.36 rows=2 width=41) (actual
time=15.904..49.821 rows=1 loops=3)
        ->  Parallel Seq Scan on usuario (cost=0.00..7287.17 rows=1
width=41) (actual time=0.013..48.591 rows=1 *loops=1*)
              Filter: (nome = 'c81e728d9d4c2f636f067f89cc14862c'::text)
*Rows Removed by Filter: 499999*
        ->  Parallel Seq Scan on usuario usuario_1 (cost=0.00..7287.17
rows=1 width=41) (actual time=15.985..33.621 rows=0 *loops=3*)
              Filter: (nome = 'c4ca4238a0b923820dcc509a6f75849b'::text)
*Rows Removed by Filter: 166666*
Planning Time: 0.107 ms
Execution Time: 55.524 ms

But I can't understand why does it changes the number of loops from the
queries

Browse pgsql-general by date

  From Date Subject
Next Message amul sul 2020-02-21 11:50:39 Re: Can we have multiple tablespaces with in a database.
Previous Message stan 2020-02-21 10:42:54 How to get the name of the table taht fired a triigger?