From: Tom Lane <tgl@sss.pgh.pa.us>
To: jacobmitash@gmail.com
Cc: pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #16824: Planner chooses poor path on query with Merge Join and pagination
Date: Wed, 13 Jan 2021 19:16:39 -0500
Message-ID: <1343668.1610583399@sss.pgh.pa.us> (raw)
In-Reply-To: <16824-cac06100763db5ae@postgresql.org>
References: <16824-cac06100763db5ae@postgresql.org>
PG Bug reporting form <noreply@postgresql.org> writes:
> My understanding of this ID pagination is that it should be very quick as it
> needs only find the ID in the index, and then scan the next 50 entries.
Well, that's what it's doing, so far as the "sub s" scan is concerned.
Your beef is with the subsequent join.
> 1. Why is the Merge Join performing so slowly?
> It seems to be because the planner doesn't recognize that it can apply the
> subscription_id index condition on the inner table. If I explicitly tell it:
> "AND si.subscription_id > '7ca1...'", then it applies an index condition and
> is almost instant.
We don't attempt to infer derived inequalities. Given "a = b AND b = c",
the planner will deduce "a = c". However, given "a = b AND b > c", we
do not deduce "a > c". This is an empirical decision based on the
frequency with which such a deduction would help versus the planner
cycles that would be spent looking for such cases.
> 2. Why does the planner believe that Merge Join (as-is) is optimal here?
The cost is estimated to be slightly lower. It might be right, so
far as the time to run the join to completion (without a LIMIT) is
concerned. Large nestloop joins tend to suck :-(. But the reason
that it then makes the wrong choice with the LIMIT applied,
fundamentally, is that the fraction of the total cost that will
actually be incurred with the LIMIT present is nonlinear, and it
doesn't know that. Doing better is a research problem.
In short, there's nothing here that I'd call a bug that we're likely
to fix anytime soon. In the meantime, if you can improve matters
by manually injecting the extra inequality, that seems like the
thing to do.
regards, tom lane
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-bugs@postgresql.org
Cc: tgl@sss.pgh.pa.us, jacobmitash@gmail.com, pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #16824: Planner chooses poor path on query with Merge Join and pagination
In-Reply-To: <1343668.1610583399@sss.pgh.pa.us>
* 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