Re: SELECT DISTINCT chooses parallel seqscan instead of indexscan on huge table with 1000 partitions - Mailing list pgsql-general

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