From: Tom Lane <tgl@sss.pgh.pa.us>
To: Ancoron Luciferis <ancoron.luciferis@googlemail.com>
cc: pgsql-performance@lists.postgresql.org
Subject: Re: Huge generated UNION ALL faster than JOIN?
In-reply-to: <965737b8-0e86-9e61-e006-1f4d927e3c4f@googlemail.com>
References: <965737b8-0e86-9e61-e006-1f4d927e3c4f@googlemail.com>
Comments: In-reply-to Ancoron Luciferis <ancoron.luciferis@googlemail.com>
	message dated "Sun, 12 May 2019 16:08:44 +0200"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <23128.1557684481.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Sun, 12 May 2019 14:08:01 -0400
Message-ID: <23129.1557684481@sss.pgh.pa.us>
Precedence: bulk

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=3D0.71..0.30 rows=3D72,839,557 width=3D33) (actual time=3D=
19,504.096..315,933.158 rows=3D274 loops=3D1)

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?

> 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