From: | Richard Guo <guofenglinux(at)gmail(dot)com> |
---|---|
To: | PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Query results vary depending on the plan cache mode used |
Date: | 2024-08-01 08:44:22 |
Message-ID: | CAMbWs4-ts-1z6XNA+ghC4qzbUey6+BH_EuWUC=YPPyonGmaBLA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
While working on the grouping sets patches for queries with GROUP BY
items that are constants, I noticed $subject on master. As an
example, consider
prepare q1(int) as
select $1 as c1, $1 as c2 from generate_series(1,2) t group by rollup(c1);
set plan_cache_mode to force_custom_plan;
execute q1(3);
c1 | c2
----+----
3 | 3
| 3
(2 rows)
set plan_cache_mode to force_generic_plan;
execute q1(3);
c1 | c2
----+----
3 | 3
|
(2 rows)
The reason can be seen in the plans under different modes.
-- force_custom_plan
explain (verbose, costs off) execute q1(3);
QUERY PLAN
-----------------------------------------------------
GroupAggregate
Output: (3), 3
Group Key: 3
Group Key: ()
-> Function Scan on pg_catalog.generate_series t
Output: 3
Function Call: generate_series(1, 2)
(7 rows)
-- force_generic_plan
explain (verbose, costs off) execute q1(3);
QUERY PLAN
-----------------------------------------------------
GroupAggregate
Output: ($1), ($1)
Group Key: $1
Group Key: ()
-> Function Scan on pg_catalog.generate_series t
Output: $1
Function Call: generate_series(1, 2)
(7 rows)
In custom mode, the target entry 'c2' is a Const expression, and
setrefs.c does not replace it with an OUTER_VAR, despite there happens
to be an identical Const below. As a result, when this OUTER_VAR goes
to NULL due to the grouping sets, 'c2' remains as constant 3. Look at
this code in search_indexed_tlist_for_non_var:
/*
* If it's a simple Const, replacing it with a Var is silly, even if there
* happens to be an identical Const below; a Var is more expensive to
* execute than a Const. What's more, replacing it could confuse some
* places in the executor that expect to see simple Consts for, eg,
* dropped columns.
*/
if (IsA(node, Const))
return NULL;
In generic mode, the target entry 'c2' is a Param expression, and is
replaced with the OUTER_VAR (indicated by the parentheses around the
second '$1'). So it goes to NULL when we're grouping by the set that
does not contain this Var.
Is this inconsistent behavior in different plan cache modes expected,
or does it indicate a bug that needs to be fixed?
Thanks
Richard
From | Date | Subject | |
---|---|---|---|
Next Message | Amit Kapila | 2024-08-01 08:55:50 | Re: Remove duplicate table scan in logical apply worker and code refactoring |
Previous Message | Peter Smith | 2024-08-01 08:32:21 | Re: Pgoutput not capturing the generated columns |