From: Christopher Baines <mail@cbaines.net>
To: Michael Lewis <mlewis@entrata.com>
Cc: Pgsql Performance <pgsql-performance@lists.postgresql.org>
Subject: Re: Slow recursive CTE query questions, with row estimate and n_distinct issues
Date: Tue, 29 Dec 2020 22:40:39 +0000
Message-ID: <875z4k9qxk.fsf@cbaines.net> (raw)
In-Reply-To: <CAHOFxGo2qtDSbBh6xqQ-DZpCRSR=d0sXdU9xAOyTDxH-iPVo4A@mail.gmail.com>
References: <87sg7q9e7k.fsf@cbaines.net>
<CAHOFxGo2qtDSbBh6xqQ-DZpCRSR=d0sXdU9xAOyTDxH-iPVo4A@mail.gmail.com>
Michael Lewis <mlewis@entrata.com> writes:
> On Mon, Dec 28, 2020 at 7:51 AM Christopher Baines <mail@cbaines.net> wrote:
>
>> derivation_inputs:
>> COUNT(*): 285422539
>> reltuples: 285422528
>>
>> derivation_id:
>> COUNT(DISTINCT): 7508610
>> n_distinct: 4336644 (~57% of the true value)
>>
>> derivation_output_id:
>> COUNT(DISTINCT): 5539406
>> n_distinct: 473762 (~8% of the true value)
>>
>
> If you expect the ratio of distinct of derivation_output_id values to be
> roughly linear going forward, you can set a custom value for n_distinct on
> the column (currently seems like -.0194, aka distinct count
> of derivation_output_id divided by reltuples of the table). You could also
> do this analysis every month or six and set the custom value as needed.
>
> https://www.postgresql.org/docs/current/sql-altertable.html
>
> I am not sure if it will resolve your query problems though.
Thanks Michael, I didn't realise a custom value could be set, but I'll
look in to this.
I actually managed to speed the query up enough by increasing
work_mem/shared_buffers. I didn't realise one of the sequential scans
was executing 14 times, but giving PostgreSQL more resources means it
just executes once, which really helps.
Thanks again,
Chris
Attachments:
[application/pgp-signature] signature.asc (987B, ../875z4k9qxk.fsf@cbaines.net/2-signature.asc)
download
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: pgsql-performance@postgresql.org
Cc: mail@cbaines.net, mlewis@entrata.com, pgsql-performance@lists.postgresql.org
Subject: Re: Slow recursive CTE query questions, with row estimate and n_distinct issues
In-Reply-To: <875z4k9qxk.fsf@cbaines.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox