Horton

Re: Wrong results with grouping sets

Richard Guo <guofenglinux@gmail.com>

View thread
From: Richard Guo <guofenglinux@gmail.com>
To: Ashutosh Bapat <ashutosh.bapat.oss@gmail.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>, PostgreSQL-development <pgsql-hackers@postgresql.org>
Date: 2024-07-15T02:45:16Z
Lists: pgsql-hackers

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. Remove the RTE_GROUP RTE if we drop the groupClause

  2. Mark expressions nullable by grouping sets

  3. Introduce an RTE for the grouping step

FWIW, in addition to fixing wrong result issues for queries with
grouping sets, the changes in 0001 also improve performance for
queries that have subqueries in the grouping expressions, because
different instances of the same subquery would need to be executed
only once.  As a simple example, consider

create table t (a int, b int);
insert into t select i, i from generate_series(1,10000)i;
analyze t;

-- on patched
explain (analyze, costs off)
select (select t1.b from t t2 where a = t1.a) as s1,
       (select t1.b from t t2 where a = t1.a) as s2,
       (select t1.b from t t2 where a = t1.a) as s3
from t t1
group by a, s1;
                                     QUERY PLAN
------------------------------------------------------------------------------------
 Group (actual time=20475.028..20480.543 rows=10000 loops=1)
   Group Key: t1.a, ((SubPlan 1))
   ->  Sort (actual time=20475.017..20475.821 rows=10000 loops=1)
         Sort Key: t1.a, ((SubPlan 1))
         Sort Method: quicksort  Memory: 697kB
         ->  Seq Scan on t t1 (actual time=7.435..20468.599 rows=10000 loops=1)
               SubPlan 1
                 ->  Seq Scan on t t2 (actual time=1.022..2.045 rows=1
loops=10000)
                       Filter: (a = t1.a)
                       Rows Removed by Filter: 9999
 Planning Time: 1.561 ms
 Execution Time: 20481.933 ms
(12 rows)

-- on master
explain (analyze, costs off)
select (select t1.b from t t2 where a = t1.a) as s1,
       (select t1.b from t t2 where a = t1.a) as s2,
       (select t1.b from t t2 where a = t1.a) as s3
from t t1
group by a, s1;
                                     QUERY PLAN
------------------------------------------------------------------------------------
 Group (actual time=20779.318..62233.526 rows=10000 loops=1)
   Group Key: t1.a, ((SubPlan 1))
   ->  Sort (actual time=20775.125..20777.936 rows=10000 loops=1)
         Sort Key: t1.a, ((SubPlan 1))
         Sort Method: quicksort  Memory: 697kB
         ->  Seq Scan on t t1 (actual time=7.492..20770.060 rows=10000 loops=1)
               SubPlan 1
                 ->  Seq Scan on t t2 (actual time=1.037..2.075 rows=1
loops=10000)
                       Filter: (a = t1.a)
                       Rows Removed by Filter: 9999
   SubPlan 2
     ->  Seq Scan on t t2_1 (actual time=1.037..2.071 rows=1 loops=10000)
           Filter: (a = t1.a)
           Rows Removed by Filter: 9999
   SubPlan 3
     ->  Seq Scan on t t2_2 (actual time=1.037..2.071 rows=1 loops=10000)
           Filter: (a = t1.a)
           Rows Removed by Filter: 9999
 Planning Time: 1.286 ms
 Execution Time: 62235.753 ms
(20 rows)

We can see that with the 0001 patch, this query runs ~3 times faster,
which is no surprise because there are 3 instances of the same
subquery in the targetlist.

Thanks
Richard