public inbox for [email protected]  

help / color / mirror / Atom feed

From: Andrei Lepikhov <[email protected]>
To: FREYBURGER Simon (SNCF VOYAGEURS / DIRECTION GENERALE TGV / DM RMP YIELD MANAGEMENT) <[email protected]>
To: [email protected] <[email protected]>
To: Peter Geoghegan <[email protected]>
Subject: Re: How to solve my slow disk i/o throughput during index scan
Date: Fri, 5 Jul 2024 09:04:31 +0700
Message-ID: <[email protected]> (raw)
In-Reply-To: <MR1P264MB34739538259E7FAF3A9DE4249DDE2@MR1P264MB3473.FRAP264.PROD.OUTLOOK.COM>
References: <MR1P264MB34732DC3639E0B6656B43DFD9DDE2@MR1P264MB3473.FRAP264.PROD.OUTLOOK.COM>
	<[email protected]>
	<MR1P264MB34739538259E7FAF3A9DE4249DDE2@MR1P264MB3473.FRAP264.PROD.OUTLOOK.COM>

On 7/4/24 22:23, FREYBURGER Simon (SNCF VOYAGEURS / DIRECTION GENERALE 
TGV / DM RMP YIELD MANAGEMENT) wrote:
> Hello,
> 
> Thank you, splitting in “OR” query definitely enables bitmap heap scans, 
> and thus parallelized read to disk 😃! I though did not understand your 
> second point, what is parallel append, and how to enable it ?
Just for example:

DROP TABLE IF EXISTS t CASCADE;
CREATE TABLE t (id int not null, payload text) PARTITION BY RANGE (id);
CREATE TABLE p1 PARTITION OF t FOR VALUES FROM (0) TO (1000);
CREATE TABLE p2 PARTITION OF t FOR VALUES FROM (1000) TO (2000);
CREATE TABLE p3 PARTITION OF t FOR VALUES FROM (2000) TO (3000);
CREATE TABLE p4 PARTITION OF t FOR VALUES FROM (3000) TO (4000);
INSERT INTO t SELECT x % 4000, repeat('a',128) || x FROM 
generate_series(1,1E5) AS x;
ANALYZE t;

SET enable_parallel_append = on;
SET parallel_setup_cost = 0.00001;
SET parallel_tuple_cost = 0.00001;
SET max_parallel_workers_per_gather = 8;
SET min_parallel_table_scan_size = 0;
SET min_parallel_index_scan_size = 0;

EXPLAIN (COSTS OFF)
SELECT t.id, t.payload FROM t WHERE t.id % 2 = 0
GROUP BY t.id, t.payload;

  Group
    Group Key: t.id, t.payload
    ->  Gather Merge
          Workers Planned: 6
          ->  Sort
                Sort Key: t.id, t.payload
                ->  Parallel Append
                      ->  Parallel Seq Scan on p1 t_1
                            Filter: ((id % 2) = 0)
                      ->  Parallel Seq Scan on p2 t_2
                            Filter: ((id % 2) = 0)
                      ->  Parallel Seq Scan on p3 t_3
                            Filter: ((id % 2) = 0)
                      ->  Parallel Seq Scan on p4 t_4
                            Filter: ((id % 2) = 0)

Here the table is scanned in parallel. It also works with IndexScan.

-- 
regards, Andrei Lepikhov

view thread (9+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected], [email protected]
  Subject: Re: How to solve my slow disk i/o throughput during index scan
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox