From: Tom Lane <tgl@sss.pgh.pa.us>
To: Tomas Vondra <tomas.vondra@enterprisedb.com>
Cc: Richard Guo <guofenglinux@gmail.com>
Cc: Martijn van Oosterhout <kleptog@gmail.com>
Cc: PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
Subject: Re: BUG #17564: Planner bug in combination of generate_series(), unnest() and ORDER BY
Date: Wed, 03 Aug 2022 15:50:20 -0400
Message-ID: <3109432.1659556220@sss.pgh.pa.us> (raw)
In-Reply-To: <3002973.1659538573@sss.pgh.pa.us>
References: <17564-c7472c2f90ef2da3@postgresql.org>
<2184335.1659392231@sss.pgh.pa.us>
<CADWG95s03YZsRy3+q0bQFXW_v_f-9vMvcFOo6KUL08DvqbXhqg@mail.gmail.com>
<CAMbWs49AgGUophtyThqHhSUyDTu3h6oATg-7GgowCtvHWCh9qQ@mail.gmail.com>
<3002973.1659538573@sss.pgh.pa.us>
I wrote:
> FWIW, this reproduces the bug for me in v13 and v14, but not v15 or HEAD.
A bit of bisecting later, I find that the behavior changed at
commit db0d67db2401eb6238ccc04c6407a4fd4f985832
Author: Tomas Vondra <tomas.vondra@postgresql.org>
Date: Thu Mar 31 00:09:11 2022 +0200
Optimize order of GROUP BY keys
I think that either that commit is buggy, or the commit message
omitted so many relevant facts as to verge on a lie. The query
we are dealing with here has no GROUP BY whatsoever, but nonetheless
that commit produces a totally different plan (with a significantly
cheaper cost estimate) than its immediate predecessor. I don't
see anything specifically about SRFs in that patch, but I think
what must have happened is that it changed cost estimates for
this scenario enough to accidentally cause selection of a
non-buggy plan.
The plan produced as of that commit, which is the same as what
you get in current HEAD, is
Sort (cost=2946.79..3096.79 rows=60000 width=36)
Sort Key: (((unnest('{}'::jsonb[])) ->> 'timestamp'::text))
-> Result (cost=90.08..1446.79 rows=60000 width=36)
-> ProjectSet (cost=90.08..396.79 rows=60000 width=36)
-> Hash Join (cost=90.08..96.19 rows=60 width=0)
Hash Cond: (groups.group_id = results.group_id)
-> Seq Scan on groups (cost=0.00..5.66 rows=166 width=4)
-> Hash (cost=89.33..89.33 rows=60 width=4)
-> Index Only Scan using results_pkey on results (cost=0.28..89.33 rows=60 width=4)
Index Cond: (search_id = 3336)
I can't easily produce a nice EXPLAIN result for the previous plan,
since it fails in executor startup, but a heavily-trimmed pprint
dump is enough to show that it's totally different:
{RESULT
:startup_cost 4071.64
:total_cost 5445.55
:plan_rows 60000
:lefttree
{PROJECTSET
:startup_cost 4071.64
:total_cost 4395.55
:plan_rows 60000
:lefttree
{NESTLOOP
:startup_cost 4071.64
:total_cost 4094.95
:plan_rows 60
:lefttree
{GATHERMERGE
:startup_cost 4071.48
:total_cost 4078.32
:plan_rows 60
:lefttree
{SORT
:startup_cost 3071.47
:total_cost 3071.56
:plan_rows 35
:lefttree
{SEQSCAN
:startup_cost 0.00
:total_cost 3070.57
:plan_rows 35
:parallel_aware true
:parallel_safe true
:targetlist (
...
{TARGETENTRY
:expr
{OPEXPR
:opno 3477
:opfuncid 3214
:opresulttype 25
:opretset false
:opcollid 100
:inputcollid 100
:args (
{FUNCEXPR
:funcid 2331
:funcresulttype 3802
:funcretset true -- OOPS
...
:scanrelid 1 -- this scan is on "results"
...
}
:righttree
{MEMOIZE
:startup_cost 0.15
:total_cost 0.31
:plan_rows 1
:lefttree
{INDEXONLYSCAN
:startup_cost 0.14
:total_cost 0.30
:plan_rows 1
:scanrelid 2 -- this scan is on "groups"
}
:righttree <>
}
So I'd like to know why an ostensibly unrelated commit changed
cost estimates by 43% for a query that hasn't even got a GROUP BY.
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 using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: tgl@sss.pgh.pa.us, tomas.vondra@enterprisedb.com, guofenglinux@gmail.com, kleptog@gmail.com, pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #17564: Planner bug in combination of generate_series(), unnest() and ORDER BY
In-Reply-To: <3109432.1659556220@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