Date: Mon, 13 May 2024 14:46:12 +0200 (CEST)
From: Dimitrios Apostolou <jimis@gmx.net>
To: David Rowley <dgrowleyml@gmail.com>
cc: Tom Lane <tgl@sss.pgh.pa.us>, pgsql-general@lists.postgresql.org
Subject: Re: SELECT DISTINCT chooses parallel seqscan instead of indexscan
 on huge table with 1000 partitions
In-Reply-To: <3efce60b-48c0-160d-0444-474b02f76739@gmx.net>
Message-ID: <8c923989-7944-d139-5ade-3d9f2588a11d@gmx.net>
References: <7886a68f-b466-2131-1747-f69f0fb71a37@gmx.net> <69077f15-4125-2d63-733f-21ce6eac4f01@gmx.net> <559b0e40-63e6-fa9a-6b03-d1eba10f30f8@gmx.net> <1629463.1715372568@sss.pgh.pa.us> <6a2f3906-3d7a-6924-7403-8f77d57a18e4@gmx.net>
 <CAApHDvrtTKfh7HgAyXBd3KN0s-jxiHzW7sWdm-sFEjP6fGPCkg@mail.gmail.com> <3efce60b-48c0-160d-0444-474b02f76739@gmx.net>
MIME-Version: 1.0
Content-Type: text/plain; charset=US-ASCII; format=flowed
UI-OutboundReport: notjunk:1;M01:P0:uQ3XpJczc/4=;6PB2+1l5QHS23ipEN2nUQltH8Rf
 wW2CrKsnbkOtk+LhjGzcUVKBfofLemsfWX7eWPh5Gh0hAQYSnDcohhEFjPYpCkFx4yojY2jKN
 cVuhaJzKk90YloGeZNJypXlvETX43Z+auQXI6g2A1HrIu6PPEgkU5RrPc7J8VJP01mOP37GEa
 bjY9G/pIlSsOM99Buy+L9NuCwv2uJ+pWNyMvPBMFHKvcAvrtYnWzKo/TTq6EtasBrI4J1s3ZA
 DwmkhVHllIduAugA1mCCwq2xgQfwYCoj0RBtZeYiJoxaP2zpKUuGuFhTz3ghVibxRhESRWUu5
 ptW+YzJquWBwrNebfaeqhlNt04ViTsubVaz944J3GNZ0lp7uvnidAuVr3tb5EL/d/ZTVkRGVK
 VkryBnD1Z1489k4ut/mgSOW0qs6nbFVJzlONn5t1SYjPGopkX/4sSFxUTfKaqvUBXF+pLbhRg
 LJRaxVE80cTctEFz1e8fIq0C6dT6yvCjdbJ7oimStp7SZ6o0gmNMRSjKRH26RdPFkZoMBXrNS
 +TxrcuLnOGe5Xd7Zfzq1BOV6Sygb4c9M+R1P5vsgPB/8ORjw5K5LQ9so3wR2o+IAKJSDlwg7L
 xNDNYWBhmmdHZUcpPiGA6klqyUmngZ5rStwO4lr9YlPacDwyqbWomENdormFqwcFbAOmU9bZ1
 PSRpiOAHfIezYsOI9nmXCKfPD98YRk8M/z/shCajIOy+kLqOvAiB3TVcUiYQa5X3dXr8qQSEP
 nQ/IANN6nvZy+ZRH+0+mnFPqD5T8IlsTfI4p1UMGCLuZIdB2mxMU94wcvPOWGWC5SeW6Q3FSB
 CjXh5m/IZ7TpcQnO9LTNTxJIs2F0SAuvsTVMG3T8jyH+Y=
Content-Transfer-Encoding: quoted-printable
Archived-At: <https://www.postgresql.org/message-id/8c923989-7944-d139-5ade-3d9f2588a11d%40gmx.net>
Precedence: bulk

On Mon, 13 May 2024, Dimitrios Apostolou wrote:

> On Sat, 11 May 2024, David Rowley wrote:
>
>>  On Sat, 11 May 2024 at 13:11, Dimitrios Apostolou <jimis@gmx.net> wrot=
e:
>>>  Indeed that's an awful estimate, the table has more than 1M of unique
>>>  values in that column. Looking into pg_stat_user_tables, I can't see =
the
>>>  partitions having been vacuum'd or analyzed at all. I think they shou=
ld
>>>  have been auto-analyzed, since they get a ton of INSERTs
>>>  (no deletes/updates though) and I have the default autovacuum setting=
s.
>>>  Could it be that autovacuum starts, but never
>>>  finishes? I can't find something in the logs.
>>
>>  It's not the partitions getting analyzed you need to worry about for
>>  an ndistinct estimate on the partitioned table. It's auto-analyze or
>>  ANALYZE on the partitioned table itself that you should care about.
>>
>>  If you look at [1], it says "Tuples changed in partitions and
>>  inheritance children do not trigger analyze on the parent table."
>
> Thanks

Do I read that correctly, that I have to setup cron jobs to manually
analyze partitioned tables?


Dimitris