References: <87sg7q9e7k.fsf@cbaines.net>
 <CAEtnbpUPTpbsjU4GKy5Z7jQV3m38=8bzTCi+R5fmbEEbmJVwXA@mail.gmail.com>
User-agent: mu4e 1.4.13; emacs 27.1
From: Christopher Baines <mail@cbaines.net>
To: Greg Spiegelberg <gspiegelberg@gmail.com>
Cc: pgsql-performance@lists.postgresql.org
Subject: Re: Slow recursive CTE query questions, with row estimate and
 n_distinct issues
In-reply-to: 
 <CAEtnbpUPTpbsjU4GKy5Z7jQV3m38=8bzTCi+R5fmbEEbmJVwXA@mail.gmail.com>
Date: Tue, 29 Dec 2020 22:45:55 +0000
Message-ID: <8735zo9qos.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


Greg Spiegelberg <gspiegelberg@gmail.com> writes:

> On Mon, Dec 28, 2020 at 7:51 AM Christopher Baines <mail@cbaines.net> wrote:
>
>> Hi,
>>
>> I have a performance problem with a query. I've uploaded it, along with
>> the EXPLAIN ANALYZE output here [1].
>>
>> 1: https://explain.depesz.com/s/w5vP
>>
>> I think the query is taking longer than I'd like, as PostgreSQL is not
>> generating a great plan, in particular the estimated rows in parts of
>> the plan are way off from the actual number of rows produced, and I
>> think PostgreSQL might pick a better approach if it made more reasonable
>> estimates.
>>
>> Looking at the row numbers on [1], I think things start to go wrong at
>> row 11. The first part of the recursive CTE is estimated to generate
>> 22,122 rows, and this isn't far off the 15,670 rows it generates. The
>> WorkTable scan expects to generate 10x that though, at 221,220. Maybe
>> that's reasonable, I'm unsure?
>>
>> Things seem to continue getting out of hand from this point. The nested
>> loop on line 10 generates 232x less rows than expected, and this results
>> in an overall overestimate of 4,317x.
>>
>> My theory as to why the row estimates is way off is that the planner is
>> using the n_distinct counts for the derivation_inputs table, and they
>> are way off [2]. I've set the stats target to the highest value for this
>> table, so I'm unsure what I can do to improve these estimates?
>>
>> I've included relevant numbers for the two important tables below
>> [2][3], as well as descriptions of the tables [4][5].
>>
>> Does anyone know if my theory as to why the row estimates in the plan is
>> right, or have any ideas as how I can make the query more performant?

> Hi Chris
>
> What is your realistic target maximum execution time for the query?

It's for a web page. It can be slow, so 10 seconds is a reasonable
target.

> Ideas off the top of my head sans all the usual tuning of shared_buffers et
> al...
>
> When was the last time autovacuum vacuumed and analyzed the source tables?
>
> Can you leverage a materialized view refreshing when necessary?  Thinking
> for the 3 joined tables but could be applied to the whole result.
>
> If you can guarantee no duplicate rows before and after current UNION,
> change to UNION ALL.
>
> If you have the luxury of faster disk, such as NVMe, create a tablespace
> and move data and/or indexes there.  Adjust storage planner options
> accordingly.
>
> Personally, I like to see guardrails on RECURSIVE CTE's but you know your
> data and clients better than I so may not be necessary.  Suggestions
> include:
>  1. Limit recursion depth.  It does not seem to be the intent here though
> but ask if really the whole is necessary.  Not knowing the application but
> say 10 deep is all that is needed initially.  If the need for more is
> required, run the query again but has a different guix_revisions.commit
> starting point.  Think of it as pagination.
>  2. Cycle detection / prevention but depends on the hierarchy represented
> by the data.  Doesn't seem to be the case here since the query as-is does
> return.

Thanks for your suggestions Greg. Turns out that 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.

There's probably still room for improvement, but it's "fast enough" now.

Thanks again,

Chris

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

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

iQKlBAEBCgCPFiEEPonu50WOcg2XVOCyXiijOwuE9XcFAl/rsaNfFIAAAAAALgAo
aXNzdWVyLWZwckBub3RhdGlvbnMub3BlbnBncC5maWZ0aGhvcnNlbWFuLm5ldDNF
ODlFRUU3NDU4RTcyMEQ5NzU0RTBCMjVFMjhBMzNCMEI4NEY1NzcRHG1haWxAY2Jh
aW5lcy5uZXQACgkQXiijOwuE9XdWSA/6AsPYyVpQMpGVD7394UXw7X2dQCQZq9tU
35Am/YtoPsGue3UYaGF2Kd7DgdGTP77lbifB8hSbisbG4nZXGxNGXrxBdYjqhZJO
br0A93/zIpdSuWrepWi5Pk42sPyJl7+ndslOpF7NnQC9y6Zio5oMlz9S0ix2XWZC
yjGeRmRBqnth0UAiIOaUUt7wHh0WhdL2/0Vl6l+FKyX1PJJwmftkNBk0HNVaKOjy
qg7jioncNVWObw8EtOV8xphvdpGVEUBgzNJWEMVB7+JOkUHma1D+YxVJbvJNUWxH
HKer04OmkGggxta90lNNj+VC2BXMobPuGX9HHbkhRKzvnSAHa+CRseEnraOfpZz5
H6bAPUO48zjNQL/iQgAnAQyU3tx0Px8EJP/tR7l28u2Okj0taYwoBwA6o2acNgfv
aPlzkn91mCUjVSNvb+KGBILSnirA7gfr6Z+oFDFyo9C3Vb4YTmOaN3m/43IVItsL
Q8R6hof6PdeOqe/ZfqlzUJP7GArE7koLx40SoJ/oqtcYkWOPqxrYmiuNKUMNEQPf
ebGnRE5egxBEHV+Z2If/H85V1K+BVy3vu8Pnagr/PFwF2hH8sW/AF2NCphTZOv/n
sbqhUKRTpyvZtih4gnIGzuwYlm1NyirGarlpL/cOG6EZXwamCRGiaTAbYW9nsPFt
73EuGWEHWhU=
=xn5L
-----END PGP SIGNATURE-----
--=-=-=--