Hi hackers, In previous versions, changing the column order in the SELECT clause also resulted in a different query_id.The test results are in the attached queryidtest.txt file.
If TargetEntry.resjunk is not marked as query_jumble_ignore, it feels a bit off that the query_id differs even when the execution plan is identical, including the output order. However, it is also true that queries with different column specifications in the SQL SELECT clause are collected as identical SQL. To distinguish and collect information on these, modifications like this patch are necessary. -- Keisuke Kuroda NTT DOCOMO SOLUTIONS, Inc.
## PostgreSQL 18.3 CREATE TABLE t(c1 int,c2 int); postgres=# EXPLAIN(VERBOSE) SELECT c1, c2 FROM t ORDER BY c1,c2; QUERY PLAN ------------------------------------------------------------------ Sort (cost=158.51..164.16 rows=2260 width=8) Output: c1, c2 Sort Key: t.c1, t.c2 -> Seq Scan on public.t (cost=0.00..32.60 rows=2260 width=8) Output: c1, c2 Query Identifier: -4675081474881745094 (6 rows) postgres=# EXPLAIN(VERBOSE) SELECT c2, c1 FROM t ORDER BY c1,c2; QUERY PLAN ------------------------------------------------------------------ Sort (cost=158.51..164.16 rows=2260 width=8) Output: c2, c1 Sort Key: t.c1, t.c2 -> Seq Scan on public.t (cost=0.00..32.60 rows=2260 width=8) Output: c2, c1 Query Identifier: -3269814016130981549 (6 rows) postgres=# EXPLAIN(VERBOSE) SELECT c1 FROM t ORDER BY c1,c2; QUERY PLAN ------------------------------------------------------------------ Sort (cost=158.51..164.16 rows=2260 width=8) Output: c1, c2 Sort Key: t.c1, t.c2 -> Seq Scan on public.t (cost=0.00..32.60 rows=2260 width=8) Output: c1, c2 Query Identifier: -4675081474881745094 (6 rows) postgres=# EXPLAIN(VERBOSE) SELECT c2 FROM t ORDER BY c1,c2; QUERY PLAN ------------------------------------------------------------------ Sort (cost=158.51..164.16 rows=2260 width=8) Output: c2, c1 Sort Key: t.c1, t.c2 -> Seq Scan on public.t (cost=0.00..32.60 rows=2260 width=8) Output: c2, c1 Query Identifier: -3269814016130981549 (6 rows) ## PostreSQL 19dev(d841ca2d149666b8) with Patch CREATE TABLE t(c1 int,c2 int); postgres=# EXPLAIN(VERBOSE) SELECT c1, c2 FROM t ORDER BY c1,c2; QUERY PLAN ------------------------------------------------------------------ Sort (cost=158.51..164.16 rows=2260 width=8) Output: c1, c2 Sort Key: t.c1, t.c2 -> Seq Scan on public.t (cost=0.00..32.60 rows=2260 width=8) Output: c1, c2 Query Identifier: 3650220897747797500 (6 rows) postgres=# EXPLAIN(VERBOSE) SELECT c2, c1 FROM t ORDER BY c1,c2; QUERY PLAN ------------------------------------------------------------------ Sort (cost=158.51..164.16 rows=2260 width=8) Output: c2, c1 Sort Key: t.c1, t.c2 -> Seq Scan on public.t (cost=0.00..32.60 rows=2260 width=8) Output: c2, c1 Query Identifier: -3038222286574995023 (6 rows) postgres=# EXPLAIN(VERBOSE) SELECT c1 FROM t ORDER BY c1,c2; QUERY PLAN ------------------------------------------------------------------ Sort (cost=158.51..164.16 rows=2260 width=8) Output: c1, c2 Sort Key: t.c1, t.c2 -> Seq Scan on public.t (cost=0.00..32.60 rows=2260 width=8) Output: c1, c2 Query Identifier: -8371868852328991336 (6 rows) postgres=# EXPLAIN(VERBOSE) SELECT c2 FROM t ORDER BY c1,c2; QUERY PLAN ------------------------------------------------------------------ Sort (cost=158.51..164.16 rows=2260 width=8) Output: c2, c1 Sort Key: t.c1, t.c2 -> Seq Scan on public.t (cost=0.00..32.60 rows=2260 width=8) Output: c2, c1 Query Identifier: -8689818593596346321 (6 rows)
