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
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? |