From: Tom Lane <tgl@sss.pgh.pa.us>
To: Isaiah Langford <ilangford@ushrauto.com>
Cc: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
Subject: Re: Why does the planner reduce the planned rows when filtering single values in an array
Date: Fri, 21 Apr 2023 10:43:34 -0400
Message-ID: <3341124.1682088214@sss.pgh.pa.us> (raw)
In-Reply-To: <SN6PR15MB23976154339C61940C0E7F37D4609@SN6PR15MB2397.namprd15.prod.outlook.com>
References: <SN6PR15MB23976154339C61940C0E7F37D4609@SN6PR15MB2397.namprd15.prod.outlook.com>
Isaiah Langford <ilangford@ushrauto.com> writes:
> Why is the planner expecting the number of rows to be reduced by the join filter? Is there any way I can correct the planner here?
I think you're running into a couple of issues here:
* ANALYZE fails to record any useful stats for a single-row table.
It can't form a histogram with only one entry, but it also fails to
put the value into the MCV list, because there's a heuristic that an
MCV must appear more than once. Possibly we could think harder
about what to do with such cases, but at the moment the planner has
no knowledge that the only value in single_value_table is "1".
* Even if it did know that, the logic in scalararraysel() is quite
inadequate for the case of "variable = ANY(variable)". It looks
like that's only been fleshed out for cases where one side or
the other is a constant.
Lot of unfinished work here :-(
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 from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-general@postgresql.org
Cc: tgl@sss.pgh.pa.us, ilangford@ushrauto.com
Subject: Re: Why does the planner reduce the planned rows when filtering single values in an array
In-Reply-To: <3341124.1682088214@sss.pgh.pa.us>
References: <SN6PR15MB23976154339C61940C0E7F37D4609@SN6PR15MB2397.namprd15.prod.outlook.com> <3341124.1682088214@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