pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Tom Lane <tgl@sss.pgh.pa.us>
To: marko@joh.to
Cc: pgsql-bugs@lists.postgresql.org, David Rowley <david.rowley@2ndquadrant.com>
Subject: Re: BUG #15383: Join Filter cost estimation problem in 10.5
Date: Thu, 13 Sep 2018 13:57:45 -0400
Message-ID: <12829.1536861465@sss.pgh.pa.us> (raw)
In-Reply-To: <153683552113.22350.18441286362867559841@wrigleys.postgresql.org>
References: <153683552113.22350.18441286362867559841@wrigleys.postgresql.org>

=?utf-8?q?PG_Bug_reporting_form?= <noreply@postgresql.org> writes:
>                                     QUERY PLAN
> ----------------------------------------------------------------------------------
>  Hash Join  (cost=303.19..315.81 rows=333 width=4)
>    Hash Cond: (gs2.i = gs1.i)
>    Join Filter: (expensive_func((gs1.i + gs2.i)) > 0)
>    ->  Function Scan on generate_series gs2  (cost=0.00..10.00 rows=1000
> width=4)
>    ->  Hash  (cost=159.75..159.75 rows=11475 width=4)
>          ->  Seq Scan on unique_inner gs1  (cost=0.00..159.75 rows=11475
> width=4)
> (6 rows)

> (Notice how even though the function is expected to be called at least 333
> times, the cost doesn't account for even a single call.)

Yeah.  This evidently got broken sometime during v10 development,
because 9.6 and below generate a more reasonable cost:

 Hash Join  (cost=270.00..25298.75 rows=333 width=4)
   Hash Cond: (gs2.i = gs1.i)
   Join Filter: (expensive_func((gs1.i + gs2.i)) > 0)
   ->  Function Scan on generate_series gs2  (cost=0.00..10.00 rows=1000 width=4)
   ->  Hash  (cost=145.00..145.00 rows=10000 width=4)
         ->  Seq Scan on unique_inner gs1  (cost=0.00..145.00 rows=10000 width=4)

> Dropping the primary key constraint makes the costs more reasonable

Interesting.  That sort of points the finger in the direction of the
inner_unique patch, though it could be elsewhere.

Will look into it if nobody beats me to it.

			regards, tom lane




view thread (28+ messages)  latest in thread

Message-ID: <12829.1536861465@sss.pgh.pa.us>
Permalink:  ../12829.1536861465@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/12829.1536861465@sss.pgh.pa.us

 · 

reply

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, marko@joh.to, david.rowley@2ndquadrant.com
  Subject: Re: BUG #15383: Join Filter cost estimation problem in 10.5
  In-Reply-To: <12829.1536861465@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