[prev in list] [next in list] [prev in thread] [next in thread]
List: postgresql-general
Subject: Re: Why does the planner reduce the planned rows when filtering single values in an array
From: Isaiah Langford <ilangford () ushrauto ! com>
Date: 2023-04-21 23:02:55
Message-ID: SN6PR15MB2397318FC09522BA7B2030D2D4609 () SN6PR15MB2397 ! namprd15 ! prod ! outlook ! com
[Download RAW message or body]
Thank you Tom,
Every tool is a work in progress. Knowing it's just a fact of life for now is really \
helpful, I appreciate the response. ________________________________
From: Tom Lane <tgl@sss.pgh.pa.us>
Sent: Saturday, April 22, 2023 12:43 AM
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
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
[Attachment #3 (text/html)]
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=us-ascii">
<style type="text/css" style="display:none;"> P {margin-top:0;margin-bottom:0;} \
</style> </head>
<body dir="ltr">
<div style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; \
color: rgb(0, 0, 0);" class="elementToProof"> <span style="font-size:12pt" \
class="ContentPasted0">Thank you Tom,</span> <div style="font-size:12pt"><br \
class="ContentPasted0"> </div>
<span style="font-size:12pt" class="ContentPasted0">Every tool is a work in progress. \
Knowing it's just a fact of life for now is really helpful, I appreciate the \
response.</span><br> </div>
<div id="appendonsend"></div>
<hr style="display:inline-block;width:98%" tabindex="-1">
<div id="divRplyFwdMsg" dir="ltr"><font face="Calibri, sans-serif" \
style="font-size:11pt" color="#000000"><b>From:</b> Tom Lane \
<tgl@sss.pgh.pa.us><br> <b>Sent:</b> Saturday, April 22, 2023 12:43 AM<br>
<b>To:</b> Isaiah Langford <ilangford@ushrauto.com><br>
<b>Cc:</b> pgsql-general@lists.postgresql.org \
<pgsql-general@lists.postgresql.org><br> <b>Subject:</b> Re: Why does the \
planner reduce the planned rows when filtering single values in an array</font> \
<div> </div> </div>
<div class="BodyFragment"><font size="2"><span style="font-size:11pt;">
<div class="PlainText">Isaiah Langford <ilangford@ushrauto.com> writes:<br>
> 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?<br> <br>
I think you're running into a couple of issues here:<br>
<br>
* ANALYZE fails to record any useful stats for a single-row table.<br>
It can't form a histogram with only one entry, but it also fails to<br>
put the value into the MCV list, because there's a heuristic that an<br>
MCV must appear more than once. Possibly we could think harder<br>
about what to do with such cases, but at the moment the planner has<br>
no knowledge that the only value in single_value_table is "1".<br>
<br>
* Even if it did know that, the logic in scalararraysel() is quite<br>
inadequate for the case of "variable = ANY(variable)". It looks<br>
like that's only been fleshed out for cases where one side or<br>
the other is a constant.<br>
<br>
Lot of unfinished work here :-(<br>
<br>
\
regards, tom lane<br> </div>
</span></font></div>
</body>
</html>
[prev in list] [next in list] [prev in thread] [next in thread]