References: <87sg7q9e7k.fsf@cbaines.net>
 <CAHOFxGo2qtDSbBh6xqQ-DZpCRSR=d0sXdU9xAOyTDxH-iPVo4A@mail.gmail.com>
User-agent: mu4e 1.4.13; emacs 27.1
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
In-reply-to: 
 <CAHOFxGo2qtDSbBh6xqQ-DZpCRSR=d0sXdU9xAOyTDxH-iPVo4A@mail.gmail.com>
Date: Tue, 29 Dec 2020 22:40:39 +0000
Message-ID: <875z4k9qxk.fsf@cbaines.net>
MIME-Version: 1.0
Content-Type: multipart/signed; boundary="=-=-=";
	micalg=pgp-sha512; protocol="application/pgp-signature"
Precedence: bulk

--=-=-=
Content-Type: text/plain


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

--=-=-=
Content-Type: application/pgp-signature; name="signature.asc"

-----BEGIN PGP SIGNATURE-----

iQKlBAEBCgCPFiEEPonu50WOcg2XVOCyXiijOwuE9XcFAl/rsGdfFIAAAAAALgAo
aXNzdWVyLWZwckBub3RhdGlvbnMub3BlbnBncC5maWZ0aGhvcnNlbWFuLm5ldDNF
ODlFRUU3NDU4RTcyMEQ5NzU0RTBCMjVFMjhBMzNCMEI4NEY1NzcRHG1haWxAY2Jh
aW5lcy5uZXQACgkQXiijOwuE9XeNRBAAnPXoTBHkvRdgbvmImo5gnfhvIeztYb0w
ktNFNF8p5ZZ6j5R98FmWmx66tjxcZHTEII6LLRBOZQpG2glxDIrdmrB7gKQPduCz
waNQaQsltHYRvUN91SWutTnOWUHoxm4gQO1/gQVlcM4MGykA/YsyiTTrm57Kubne
RwHq3b6SQ0RfC4LlXrCobbvgbqprJ/l7drC54HPqc61eKEzc6gNv3/4+ToHCUbH+
lfcGxN3VG9KuD0+IpssTlKDw6f8OVlLA4WGNfhrfYWA4Mws4tjMppRY5k9WqI61l
rJKN9dmoUmQooFyRqnS8HYHf2BaXzzyozclGPDR1Sfjapy4LAXW4G3mrurMt7Ty2
ykyI6G9yRWC8NfLdHOeGNa5HANgofNlAvh02EDv9/2B7hC76fGNil2ud9kftsUaf
Mbs3c8zm8p4m927ClIlRZ6FoRX31t+bQGhzUR1U3DxCFZS+LTBs9R8GXm83A20Oq
AtZ14y8L1lLDSfmV36oHDFizbNK6gr/vGZltUON7Pd9AbV7WbKlh2/t02OuaYthD
eACFeyMu6/FILUwuu5LHYtI6Vkq1V9Mlufj6CZFAFvp7pvO3cFKB+s+GN196jsiT
Zx4Rvzr+0W+WbcACOJs9YLRsYQ4bU5yFWhguFnk0SzmXyudxADmSLJRZ4SECSi8y
Jeonp6EA9MM=
=HTXk
-----END PGP SIGNATURE-----
--=-=-=--