Query results vary depending on the plan cache mode used

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

Browse pgsql-hackers by date

  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