public inbox for [email protected]
help / color / mirror / Atom feedFrom: Dimitrios Apostolou <[email protected]>
To: [email protected]
Subject: Re: SELECT DISTINCT chooses parallel seqscan instead of indexscan on huge table with 1000 partitions
Date: Fri, 10 May 2024 21:35:57 +0200 (CEST)
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
<[email protected]>
On Fri, 10 May 2024, Dimitrios Apostolou wrote:
> On Fri, 10 May 2024, Dimitrios Apostolou wrote:
>
> Update: even the simplest SELECT DISTINCT query shows similar behaviour:
Further digging into this simple query, if I force the non-parallel plan
by setting max_parallel_workers_per_gather TO 0, I see that the query
planner comes up with a cost much higher:
Limit (cost=363.84..1134528847.47 rows=10 width=4)
-> Unique (cost=363.84..22690570036.41 rows=200 width=4)
-> Append (cost=363.84..22527480551.58 rows=65235793929 width=4)
-> Index Only Scan using test_runs_raw__part_max20k_pkey on test_runs_raw__part_max20k test_runs_raw_1 (cost=0.12..2.34 rows=1 width=4)
-> Index Only Scan using test_runs_raw__part_max40k_pkey on test_runs_raw__part_max40k test_runs_raw_2 (cost=0.12..2.34 rows=1 width=4)
[...]
-> Index Only Scan using test_runs_raw__part_max1780k_pkey on test_runs_raw__part_max1780k test_runs_raw_89 (cost=0.57..53587294.65 rows=106088160 width=4)
-> Index Only Scan using test_runs_raw__part_max1800k_pkey on test_runs_raw__part_max1800k test_runs_raw_90 (cost=0.57..98943539.74 rows=96214080 width=4)
-> Index Only Scan using test_runs_raw__part_max1820k_pkey on test_runs_raw__part_max1820k test_runs_raw_91 (cost=0.57..97495653.34 rows=193248960 width=4)
-> Index Only Scan using test_runs_raw__part_max1840k_pkey on test_runs_raw__part_max1840k test_runs_raw_92 (cost=0.57..110205205.07 rows=218440928 width=4)
-> Index Only Scan using test_runs_raw__part_max1860k_pkey on test_runs_raw__part_max1860k test_runs_raw_93 (cost=0.57..50164056.28 rows=99431760 width=4)
[...]
The total cost on the 1st line (cost=363.84..1134528847.47) has a much
higher upper limit than the total cost when
max_parallel_workers_per_gather is 4 (cost=853891608.79..853891608.99).
This explains the planner's choice. But I wonder why the cost estimation
is so far away from reality.
Dimitris
view thread (17+ 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]
Subject: Re: SELECT DISTINCT chooses parallel seqscan instead of indexscan on huge table with 1000 partitions
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