PostgreSQL Bugs

Collected from the PG bugs email list.

Bug ID15383
PG Version10.5
OSLinux
Opened2018-09-13 10:45:21+00
Reported byMarko Tiikkaja
StatusOpen

Body of first available message related to this bug follows.

The following bug has been logged on the website:

Bug reference:      15383
Logged by:          Marko Tiikkaja
Email address:      (redacted)
PostgreSQL version: 10.5
Operating system:   Linux
Description:        

I was looking at a problematic plan submitted by "sjamaan" on IRC and I
noticed that the join filter estimation seems completely wrong here:

create function expensive_func(int) returns int as $$ begin return 1; end $$
language plpgsql stable cost 10000;
create table unique_inner(a int primary key);
insert into unique_inner select generate_series(1, 10000);

explain select * from unique_inner gs1(i) join generate_series(1, 10) gs2(i)
using (i) where expensive_func(gs1.i + gs2.i) > 0;
                                    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.)

Dropping the primary key constraint makes the costs more reasonable (though
I'm still not sure how the planner arrives at these costs):

alter table unique_inner drop constraint unique_inner_pkey;
explain select * from unique_inner gs1(i) join generate_series(1, 10) gs2(i)
using (i) where expensive_func(gs1.i + gs2.i) > 0;
                                       QUERY PLAN
----------------------------------------------------------------------------------------
 Hash Join  (cost=22.50..1436880.94 rows=19125 width=4)
   Hash Cond: (gs1.i = gs2.i)
   Join Filter: (expensive_func((gs1.i + gs2.i)) > 0)
   ->  Seq Scan on unique_inner gs1  (cost=0.00..159.75 rows=11475
width=4)
   ->  Hash  (cost=10.00..10.00 rows=1000 width=4)
         ->  Function Scan on generate_series gs2  (cost=0.00..10.00
rows=1000 width=4)
(6 rows)

Messages

DateAuthorSubject
2018-09-13 10:45:21+00=?utf-8?q?PG_Bug_reporting_form?=BUG #15383: Join Filter cost estimation problem in 10.5
2018-09-13 17:57:45+00Tom LaneRe: BUG #15383: Join Filter cost estimation problem in 10.5
2018-09-14 01:02:14+00David RowleyRe: BUG #15383: Join Filter cost estimation problem in 10.5
2018-09-19 20:18:38+00Tom LaneRe: BUG #15383: Join Filter cost estimation problem in 10.5
2018-10-11 05:36:35+00David RowleyRe: BUG #15383: Join Filter cost estimation problem in 10.5
2019-03-08 23:04:16+00Tom LaneRe: BUG #15383: Join Filter cost estimation problem in 10.5
2019-04-03 17:07:41+00Tom LaneRe: BUG #15383: Join Filter cost estimation problem in 10.5
2019-04-04 04:37:03+00David RowleyRe: BUG #15383: Join Filter cost estimation problem in 10.5
2019-06-28 21:26:10+00Alvaro HerreraRe: BUG #15383: Join Filter cost estimation problem in 10.5
2019-06-28 22:05:57+00Tom LaneRe: BUG #15383: Join Filter cost estimation problem in 10.5
2019-06-28 22:10:49+00Alvaro HerreraRe: BUG #15383: Join Filter cost estimation problem in 10.5
2019-07-01 02:14:47+00David RowleyRe: BUG #15383: Join Filter cost estimation problem in 10.5
2019-09-05 23:20:14+00Alvaro HerreraRe: BUG #15383: Join Filter cost estimation problem in 10.5
2019-09-16 11:32:57+00Peter BexRe: BUG #15383: Join Filter cost estimation problem in 10.5
2019-11-29 02:18:33+00Michael PaquierRe: BUG #15383: Join Filter cost estimation problem in 10.5
2019-11-30 17:32:38+00Tom LaneRe: BUG #15383: Join Filter cost estimation problem in 10.5
2020-02-27 19:24:55+00Alvaro HerreraRe: BUG #15383: Join Filter cost estimation problem in 10.5
2020-04-24 06:26:49+00David RowleyRe: BUG #15383: Join Filter cost estimation problem in 10.5