Date: Fri, 10 May 2024 21:35:57 +0200 (CEST)
From: Dimitrios Apostolou <jimis@gmx.net>
To: pgsql-general@lists.postgresql.org
Subject: Re: SELECT DISTINCT chooses parallel seqscan instead of indexscan
 on huge table with 1000 partitions
In-Reply-To: <69077f15-4125-2d63-733f-21ce6eac4f01@gmx.net>
Message-ID: <559b0e40-63e6-fa9a-6b03-d1eba10f30f8@gmx.net>
References: <7886a68f-b466-2131-1747-f69f0fb71a37@gmx.net> <69077f15-4125-2d63-733f-21ce6eac4f01@gmx.net>
MIME-Version: 1.0
Content-Type: text/plain; charset=US-ASCII; format=flowed
UI-OutboundReport: notjunk:1;M01:P0:OMl8+Ln2Hx8=;ohcHKjYKFdDoivyEBNLcWkqf91t
 LbpgeivpQF/xu21ORTmEjEkGMHcX7OG+9ddwPqHNM4jJTO4TPlCQa1FEg+tc9csPOpPl4gOKX
 K8x/6aMvUWAmYc7qr3SfJ9YX9PRBvlmqJfzI5da/TE3W7ruzXodSoI0hDcOf6y8h6Nk17YktL
 Lkeap9z+UJVIGhsMAgRPLo8Q8dIgsCGHomAC0s0WzNH2ZlIMrukQE7XLFe6GFVRMtD2oUy8HW
 jDciWg7yS4SjGd7qrjncHPnyUhU2DKZYjxMlY5dRQkx6ASG9GwHDa+H7fg0n0qeuWJLVbnnPG
 3RT2FZqc2h6093QbQfMxSAkeQDeWTCFzkYuWdh6A/NrJx+g+rruc+ZwQdTE+U7KazTq1VvVEt
 vnjTHoGFEHQjoNHW6aH+BLNRLUvxhBAtVJcq1cQrt1HV4fnTKLvpcEPBTrB9tzLTqcA2d5gwy
 Aybv7vF1Edm6XONpQ51qshjCtZ0Bg2vJuvp9+hH+qCW26rUyGJ3vjmrhdGdyfWva5c1UNakVq
 fsgCD4OFBlNsHHnFYkDRHRb6btT70sDTUH5xCkoVX26CFg2C8oG14YOC16AkP+RxxAxPX7/9W
 hQlV5+lJlXsL1QPhb9ML0zN6rAOzNdr5eL8MYaZyONQV5mrynB1gb98P4E+Jbztde7WrZCsVh
 za+sbERPg8kBjLUCL763pYPg8ZVD/dePDAbOBO/I2kZNxCAWM1Iuljtbzpg/Tvg3VMTW7w41B
 aq50KKN5IckWtR4r8/GlkeIWLfHi1ejWkA3IzH2CwjreUpuR+PgnPOHKY0FUjMs59hgAaDQSL
 kddR9w44dQ/fpr/H0hRqNKIA+iE8WIQgP8dRNasCS5USc=
Content-Transfer-Encoding: quoted-printable
Archived-At: <https://www.postgresql.org/message-id/559b0e40-63e6-fa9a-6b03-d1eba10f30f8%40gmx.net>
Precedence: bulk


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=3D363.84..1134528847.47 rows=3D10 width=3D4)
    ->  Unique  (cost=3D363.84..22690570036.41 rows=3D200 width=3D4)
          ->  Append  (cost=3D363.84..22527480551.58 rows=3D65235793929 wi=
dth=3D4)
                ->  Index Only Scan using test_runs_raw__part_max20k_pkey =
on test_runs_raw__part_max20k test_runs_raw_1  (cost=3D0.12..2.34 rows=3D1=
 width=3D4)
                ->  Index Only Scan using test_runs_raw__part_max40k_pkey =
on test_runs_raw__part_max40k test_runs_raw_2  (cost=3D0.12..2.34 rows=3D1=
 width=3D4)
[...]
                ->  Index Only Scan using test_runs_raw__part_max1780k_pke=
y on test_runs_raw__part_max1780k test_runs_raw_89  (cost=3D0.57..53587294=
.65 rows=3D106088160 width=3D4)
                ->  Index Only Scan using test_runs_raw__part_max1800k_pke=
y on test_runs_raw__part_max1800k test_runs_raw_90  (cost=3D0.57..98943539=
.74 rows=3D96214080 width=3D4)
                ->  Index Only Scan using test_runs_raw__part_max1820k_pke=
y on test_runs_raw__part_max1820k test_runs_raw_91  (cost=3D0.57..97495653=
.34 rows=3D193248960 width=3D4)
                ->  Index Only Scan using test_runs_raw__part_max1840k_pke=
y on test_runs_raw__part_max1840k test_runs_raw_92  (cost=3D0.57..11020520=
5.07 rows=3D218440928 width=3D4)
                ->  Index Only Scan using test_runs_raw__part_max1860k_pke=
y on test_runs_raw__part_max1860k test_runs_raw_93  (cost=3D0.57..50164056=
.28 rows=3D99431760 width=3D4)
[...]


The total cost on the 1st line (cost=3D363.84..1134528847.47) has a much
higher upper limit than the total cost when
max_parallel_workers_per_gather is 4 (cost=3D853891608.79..853891608.99).

This explains the planner's choice. But I wonder why the cost estimation
is so far away from reality.


Dimitris