From: | Ranier Vilela <ranier(dot)vf(at)gmail(dot)com> |
---|---|
To: | Richard Guo <guofenglinux(at)gmail(dot)com> |
Cc: | PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: Redundant Result node |
Date: | 2024-08-22 13:02:20 |
Message-ID: | CAEudQArO-eZw4FR2_RTEbhaRjLoZagTzN2t5hjeMVs0B5vkuzg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
Hi.
Em qui., 22 de ago. de 2024 às 04:34, Richard Guo <guofenglinux(at)gmail(dot)com>
escreveu:
> I ran into a query plan where the Result node seems redundant to me:
>
> create table t (a int, b int, c int);
> insert into t select i%10, i%10, i%10 from generate_series(1,100)i;
> create index on t (a, b);
> analyze t;
>
> set enable_hashagg to off;
> set enable_seqscan to off;
>
> explain (verbose, costs off)
> select distinct b, a from t order by a, b;
> QUERY PLAN
> ---------------------------------------------------------
> Result
> Output: b, a
> -> Unique
> Output: a, b
> -> Index Only Scan using t_a_b_idx on public.t
> Output: a, b
> (6 rows)
>
> What I expect is that both the Scan node and the Unique node output
> 'b, a', and we do not need an additional projection step, something
> like:
>
> explain (verbose, costs off)
> select distinct b, a from t order by a, b;
> QUERY PLAN
> ---------------------------------------------------
> Unique
> Output: b, a
> -> Index Only Scan using t_a_b_idx on public.t
> Output: b, a
> (4 rows)
>
> I looked into this a little bit and found that in function
> create_ordered_paths, we decide whether a projection step is needed
> based on a simple pointer comparison between sorted_path->pathtarget
> and final_target.
>
> /* Add projection step if needed */
> if (sorted_path->pathtarget != target)
> sorted_path = apply_projection_to_path(root, ordered_rel,
> sorted_path, target);
>
> This does not seem right to me, as PathTargets are not canonical, so
> we cannot guarantee that two identical PathTargets will have the same
> pointer. Actually, for the query above, the two PathTargets are
> identical but have different pointers.
>
Could memcmp solve this?
With patch attached, using memcmp to compare the pointers.
select distinct b, a from t order by a, b;
QUERY PLAN
----------------------------------
Sort
Output: b, a
Sort Key: t.a, t.b
-> HashAggregate
Output: b, a
Group Key: t.a, t.b
-> Seq Scan on public.t
Output: a, b, c
(8 rows)
attached patch for consideration.
best regards,
Ranier Vilela
Attachment | Content-Type | Size |
---|---|---|
0001-avoid-redudant-result-node.patch | application/octet-stream | 974 bytes |
From | Date | Subject | |
---|---|---|---|
Next Message | Alvaro Herrera | 2024-08-22 14:36:38 | Re: MultiXact\SLRU buffers configuration |
Previous Message | Robert Haas | 2024-08-22 12:43:18 | Re: On disable_cost |