From: Ancoron Luciferis <ancoron.luciferis@googlemail.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-performance@lists.postgresql.org
Subject: Re: Huge generated UNION ALL faster than JOIN?
Date: Mon, 13 May 2019 00:23:14 +0200
Message-ID: <a850f693-2167-8a2b-f720-b27852d905c4@googlemail.com> (raw)
In-Reply-To: <23129.1557684481@sss.pgh.pa.us>
References: <965737b8-0e86-9e61-e006-1f4d927e3c4f@googlemail.com>
<23129.1557684481@sss.pgh.pa.us>
On 12/05/2019 20:08, Tom Lane wrote:
> Ancoron Luciferis <ancoron.luciferis@googlemail.com> writes:
>> One of the things consuming most of the time was an Index Only Scan
>> executed millions of times. And on top came the Nested Loop which
>> finally reduced the rows but also took a lot of time to do so.
>
>> Explain plan: https://explain.depesz.com/s/4GYT
>
> The core problem you've got there is the misestimation of the join size:
>
> Nested Loop (cost=0.71..0.30 rows=72,839,557 width=33) (actual time=19,504.096..315,933.158 rows=274 loops=1)
>
> Anytime the planner is off by a factor of 250000x, it's not going to end
> well. In this case, it's imagining that the LIMIT will kick in after just
> a very small part of the join is executed --- but in reality, the LIMIT
> is larger than the join output, so that we have to execute the whole join.
> With a more accurate idea of the join result size, it would have chosen
> a different plan.
>
> What you ought to look into is why is that estimate so badly off.
> Maybe out-of-date stats, or you need to raise the stats target for
> one or both tables?
>
I thought so as well and that's why I started investigating, but after
creating my own data set and a final analyze of both tables I ended up
with similar difference in estimation vs. actual:
https://explain.depesz.com/s/R7jp
Nested Loop (cost=25.17..514,965,251.12 rows=27,021,979 width=56)
(actual time=0.568..5.686 rows=274 loops=1)
...but this was fast due to the Merge Append being used and pushed-down
LIMIT.
>> My question now is why can't the optimizer generate a plan that in this
>> case does 114 loops of "events" scans instead of a million loops on the
>> "subscription_signal"?
>
> I don't see any "events" table in that query, so this question isn't
> making a lot of sense to me. But in any case, the answer probably boils
> down to "it's guessing that a plan like this will stop early without
> having to scan all of the large table".
>
> regards, tom lane
>
Yes, Timescale extension is mangling the partition names quite a lot. I
wonder if it would be possible to hold the result of the estimated
smaller reference data (114 subscription_signal.signal_id entries in
this case) in a VALUES list and then use that to filter the table with
the larger estimate instead of looping over.
Cheers,
Ancoron
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: ancoron.luciferis@googlemail.com, tgl@sss.pgh.pa.us, pgsql-performance@lists.postgresql.org
Subject: Re: Huge generated UNION ALL faster than JOIN?
In-Reply-To: <a850f693-2167-8a2b-f720-b27852d905c4@googlemail.com>
* 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