From: Benjamin Coutu <ben.coutu@zeyos.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-performance@postgresql.org
Subject: Re: Unclamped row estimates whith OR-ed subplans
Date: Fri, 19 Jun 2020 22:33:15 +0200
Message-ID: <20200619203316.68FB05FB27@mx.zeyos.com> (raw)
> While you're waiting, you might think about recasting the query to
> avoid the OR. Perhaps you could do a UNION of two scans of the
> transactions table?
Thanks for the hint, I am well aware of the workaround for OR via UNION. I am not trying to improve this query per se as it is the small root of a very complex generated query and it's unfeasible to rewrite it to a UNION in this case.
The point of my message to the list was to highlight the misestimation, which I couldn't wrap my head around. Maybe this discussion can give some food for thought to someone who might tackle this in the future. It would surely be great to have selectivity estimate smarts for the generic case of OR-ed SubPlans someday.
>
> > Btw, I don't quite understand why the nested loop on contract only is expected to yield 31662 rows, when the null_frac of field transactions.contract is 1. Shouldn't that indicate zero rows or some kind of default minimum estimate for that query?
>
> That I don't understand. I get a minimal rowcount estimate for an
> all-nulls outer table, as long as I'm using just one IN rather than
Yeah, that might be worth digging into. Is there any other info apart from those stats that I could provide?
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: ben.coutu@zeyos.com, tgl@sss.pgh.pa.us
Subject: Re: Unclamped row estimates whith OR-ed subplans
In-Reply-To: <20200619203316.68FB05FB27@mx.zeyos.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