[prev in list] [next in list] [prev in thread] [next in thread]
List: postgresql-general
Subject: Re: pb with join plan
From: Marc Millas <marc.millas () mokadb ! com>
Date: 2023-06-27 20:58:21
Message-ID: CADX_1abOJdQzjP==BnhJEnZ6FW6LM7T-TnCqxpKtMvAExobSTA () mail ! gmail ! com
[Download RAW message or body]
On Tue, Jun 27, 2023 at 8:12 PM Kirk Wolak <wolakk@gmail.com> wrote:
> On Wed, Jun 21, 2023 at 12:10 PM Marc Millas <marc.millas@mokadb.com>
> wrote:
>
>> Marc MILLAS
>> On Wed, Jun 21, 2023 at 12:43 PM Tomas Vondra <
>> tomas.vondra@enterprisedb.com> wrote:
>>
>>> On 6/21/23 00:26, Marc Millas wrote:
>>> >
>>> >
>>> > On Tue, Jun 20, 2023 at 11:19 PM David Rowley <dgrowleyml@gmail.com
>>> > <mailto:dgrowleyml@gmail.com>> wrote:
>>> >
>>> > On Wed, 21 Jun 2023 at 08:34, Marc Millas <marc.millas@mokadb.com
>>> > <mailto:marc.millas@mokadb.com>> wrote:
>>> > >
>>> > > On Tue, Jun 20, 2023 at 10:14 PM David Rowley
>>> > <dgrowleyml@gmail.com <mailto:dgrowleyml@gmail.com>> wrote:
>>> > >>
>>> > >> On Wed, 21 Jun 2023 at 07:42, Marc Millas <
>>> marc.millas@mokadb.com
>>> > <mailto:marc.millas@mokadb.com>> wrote:
>>> > >> > But if I do the same with clause one OR clause 2, I have to
>>> > kill the request after an hour, seeing the filesystem showing more
>>> > than 140 Mb of increased usage.
>>>
>>> It's a bit weird the "victor" table is joined seemingly without any join
>>> conditions, leading to a cross join (which massively inflates the cost
>>> for joins above it). Maybe the anonymized plan mangles it somehow.
>>>
>>
>> So I did try to simplify my pb.
>> I create a table with the result of the first 3 joins.
>> That table do have 15M lines. all tables have been vacuum analyze
>>
>> Now if I do an explain analyze of a simple join between that table and my
>> original table 4
>> using a simple = clause, I get a result in one second (around). and the
>> planner guesses for rows seems in line with the observed values .
>> if I use a substr(table1.a)= table2.b, the explain analyze get a result
>> in 21 seconds and the planner estimates a 65M rows result set while the
>> observed is 330 k rows
>> so here its 20 times slower and the discrepency between planner rows
>> guess and reality is a 200 ratio.
>>
>> Now, if I try an explain analyze with join on a=b or substr(c)=d or e=f
>> then... I kill the query after a quarter an hour without any answer.
>> if I try to just explain the query, the planner rows guess becomes more
>> than 2 Billions....
>> the extremely simple query and plan are here, without automatic
>> obfuscation
>> https://explain.depesz.com/s/b8Ll
>>
>
> First, I am not sure why you cannot send us the explain analyze. But
> moving on...
>
Kirk, the explain analyze, with the SQL query is directly accessible on
the explain.depesz link .
>
> substr() is a function that mutilates a value such that the index becomes
> useless...
> If you are looking for the LEFT() of the value, then an INDEX can be used.
> I have COLLATION "C" and when I query:
> WHERE fld like fld_b||"%"
>
there are NO indexes on those columns. One of the reasons is that the
simplest index on one column is 380 GB on disk
So to put indexes on each criteria, I must add around 1 TB of disk just
for ttt....
the full scan is not a problem. Its fast.. The problem is the nested loop
which do compare each of the 15M lines of ttt to each of the 30K lines of
inc_pha_r.
its an operation done 450 000 000 000 times. so if each comparison is 1
microsecond long, the nested loop is 125 hours long.
And I am not sure that the comparison is done in 1 microsecond...
>
> The optimizer constructs a query that uses the index on "fld"...
> But when I try:
>
> WHERE fld like CONCAT_WS("", fld_b,"%")
> It doesn't use the index version. (because the function call is too
> complicated to see through)
>
> When using functions in where clauses, indexes either have to be made on
> those functions, or often times the index cannot be used.
>
> BTW, I noted the COLLATION. That turned out to be important, because my
> first DB test did NOT use that collation, and the result
> of the LIKE was the non-indexed version...
>
> I hope you find something useful in here.
>
Thanks for trying
>
> Also, WHERE fld <> 72... (unless you have a heavily skewed set of
> statistics, I read that as. SCAN everything, and check later,
> because this should filter very few rows), whereas fld = 72 will be
> blazingly fast.
>
> Kirk
>
[Attachment #3 (text/html)]
<div dir="ltr"><div dir="ltr"><br clear="all"><div><div dir="ltr" \
class="gmail_signature"><div dir="ltr"><div \
dir="ltr"><div><br></div></div></div></div></div><br></div><br><div \
class="gmail_quote"><div dir="ltr" class="gmail_attr">On Tue, Jun 27, 2023 at \
8:12 PM Kirk Wolak <<a href="mailto:wolakk@gmail.com">wolakk@gmail.com</a>> \
wrote:<br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px \
0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div dir="ltr"><div \
dir="ltr">On Wed, Jun 21, 2023 at 12:10 PM Marc Millas <<a \
href="mailto:marc.millas@mokadb.com" target="_blank">marc.millas@mokadb.com</a>> \
wrote:<br></div><div class="gmail_quote"><blockquote class="gmail_quote" \
style="margin:0px 0px 0px 0.8ex;border-left:1px solid \
rgb(204,204,204);padding-left:1ex"><div dir="ltr"><div dir="ltr"><div><div dir="ltr" \
class="gmail_signature"><div dir="ltr"><div dir="ltr">Marc MILLAS<div>On Wed, Jun 21, \
2023 at 12:43 PM Tomas Vondra <<a href="mailto:tomas.vondra@enterprisedb.com" \
target="_blank">tomas.vondra@enterprisedb.com</a>> \
wrote:<br></div></div></div></div></div></div><div class="gmail_quote"><blockquote \
class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid \
rgb(204,204,204);padding-left:1ex">On 6/21/23 00:26, Marc Millas wrote:<br> > <br>
> <br>
> On Tue, Jun 20, 2023 at 11:19 PM David Rowley <<a \
href="mailto:dgrowleyml@gmail.com" target="_blank">dgrowleyml@gmail.com</a><br> > \
<mailto:<a href="mailto:dgrowleyml@gmail.com" \
target="_blank">dgrowleyml@gmail.com</a>>> wrote:<br> > <br>
> On Wed, 21 Jun 2023 at 08:34, Marc Millas <<a \
href="mailto:marc.millas@mokadb.com" target="_blank">marc.millas@mokadb.com</a><br> \
> <mailto:<a href="mailto:marc.millas@mokadb.com" \
target="_blank">marc.millas@mokadb.com</a>>> wrote:<br> > ><br>
> > On Tue, Jun 20, 2023 at 10:14 PM David Rowley<br>
> <<a href="mailto:dgrowleyml@gmail.com" \
target="_blank">dgrowleyml@gmail.com</a> <mailto:<a \
href="mailto:dgrowleyml@gmail.com" target="_blank">dgrowleyml@gmail.com</a>>> \
wrote:<br> > >><br>
> >> On Wed, 21 Jun 2023 at 07:42, Marc Millas <<a \
href="mailto:marc.millas@mokadb.com" target="_blank">marc.millas@mokadb.com</a><br> \
> <mailto:<a href="mailto:marc.millas@mokadb.com" \
target="_blank">marc.millas@mokadb.com</a>>> wrote:<br> > >> \
> But if I do the same with clause one OR clause 2, I have to <br> > \
kill the request after an hour, seeing the filesystem showing more<br> > \
than 140 Mb of increased usage.<br><br> It's a bit weird the "victor" \
table is joined seemingly without any join<br> conditions, leading to a cross join \
(which massively inflates the cost<br> for joins above it). Maybe the anonymized plan \
mangles it somehow.<br></blockquote><div><br></div><div>So I did try to simplify my \
pb.</div><div>I create a table with the result of the first 3 joins.</div><div>That \
table do have 15M lines. all tables have been vacuum \
analyze</div><div><br></div><div>Now if I do an explain analyze of a simple join \
between that table and my original table 4</div><div>using a simple = clause, I get a \
result in one second (around). and the planner guesses for rows seems in line with \
the observed values .</div><div>if I use a substr(table1.a)= table2.b, the explain \
analyze get a result in 21 seconds and the planner estimates a 65M rows result set \
while the observed is 330 k rows</div><div>so here its 20 times slower and the \
discrepency between planner rows guess and reality is a 200 \
ratio.</div><div><br></div><div>Now, if I try an explain analyze with join on a=b or \
substr(c)=d or e=f</div><div>then... I kill the query after a quarter an hour without \
any answer.</div><div>if I try to just explain the query, the planner rows guess \
becomes more than 2 Billions....<br></div><div>the extremely simple query and plan \
are here, without automatic obfuscation</div><div><a \
href="https://explain.depesz.com/s/b8Ll" \
target="_blank">https://explain.depesz.com/s/b8Ll</a></div></div></div></blockquote><div><br></div><div>First, \
I am not sure why you cannot send us the explain analyze. But moving \
on...</div></div></div></blockquote><div>Kirk, the explain analyze, with the SQL \
query is directly accessible on the explain.depesz link .</div><blockquote \
class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid \
rgb(204,204,204);padding-left:1ex"><div dir="ltr"><div \
class="gmail_quote"><div><br></div><div>substr() is a function that mutilates a value \
such that the index becomes useless...</div><div>If you are looking for the LEFT() of \
the value, then an INDEX can be used.</div><div>I have COLLATION "C" and \
when I query:</div><div>WHERE fld like \
fld_b||"%"</div></div></div></blockquote><div><br></div><div>there are NO \
indexes on those columns. One of the reasons is that the simplest index on one \
column is 380 GB on disk</div><div>So to put indexes on each criteria, I must add \
around 1 TB of disk just for ttt.... </div><div>the full scan is not a problem. \
Its fast.. The problem is the nested loop which do compare each of the 15M lines of \
ttt to each of the 30K lines of inc_pha_r.</div><div>its an operation done 450 000 \
000 000 times. so if each comparison is 1 microsecond long, the nested loop is 125 \
hours long.</div><div>And I am not sure that the comparison is done in 1 \
microsecond...</div><blockquote class="gmail_quote" style="margin:0px 0px 0px \
0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div dir="ltr"><div \
class="gmail_quote"><div><br></div><div>The optimizer constructs a query that uses \
the index on "fld"...</div><div>But when I try:<br><br>WHERE fld like \
CONCAT_WS("", fld_b,"%")<br>It doesn't use the index version. \
(because the function call is too complicated to see through)<br><br>When using \
functions in where clauses, indexes either have to be made on those functions, or \
often times the index cannot be used.<br><br>BTW, I noted the COLLATION. That \
turned out to be important, because my first DB test did NOT use that collation, and \
the result</div><div>of the LIKE was the non-indexed version... <br><br>I hope you \
find something useful in here.<br></div></div></div></blockquote><div>Thanks for \
trying </div><blockquote class="gmail_quote" style="margin:0px 0px 0px \
0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div dir="ltr"><div \
class="gmail_quote"><div><br>Also, WHERE fld <> 72... (unless you have a \
heavily skewed set of statistics, I read that as. SCAN everything, and check \
later,</div><div>because this should filter very few rows), whereas fld = 72 will be \
blazingly fast.<br><br>Kirk</div></div></div> </blockquote></div></div>
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic