Re: [PERF] Improve Cardinality Estimation for Joins with GROUP BY Having Single Clause

Hi, 

I initially applied this patch on the master branch at commit f95da9f, but I have noticed that the latest master branch already includes a fix for this issue. A similar patch was recently committed in e983ee9 successfully resolving this issue. Thanks for the update and assistance!

Thanks & Regards,

Ravi Revathy

Member Technical Staff

ZOHO Corporation

---- On Tue, 18 Mar 2025 14:56:48 +0530 Ilia Evdokimov <ilya(dot)evdokimov(at)tantorlabs(dot)com> wrote ---

Hi,

On commit 122a9af I can't see any problem with query:

explain analyze select * from t1 left join (select a, max(b) from t2
group by a) t2 on t1.a = t2.a;
                                                     QUERY PLAN
---------------------------------------------------------------------------------------------------------------------
 Hash Right Join  (cost=397.65..669.04 rows=1000 width=12) (actual
time=9.008..11.549 rows=1000.00 loops=1)
   Hash Cond: (t2.a = t1.a)
   Buffers: shared read=90
   ->  HashAggregate  (cost=370.15..560.25 rows=19010 width=8) (actual
time=8.103..10.160 rows=19010.00 loops=1)
         Group Key: t2.a
         Batches: 1  Memory Usage: 2321kB
         Buffers: shared read=85
         ->  Seq Scan on t2  (cost=0.00..275.10 rows=19010 width=8)
(actual time=0.015..1.907 rows=19010.00 loops=1)
               Buffers: shared read=85
   ->  Hash  (cost=15.00..15.00 rows=1000 width=4) (actual
time=0.365..0.366 rows=1000.00 loops=1)
         Buckets: 1024  Batches: 1  Memory Usage: 44kB
         Buffers: shared read=5
         ->  Seq Scan on t1  (cost=0.00..15.00 rows=1000 width=4)
(actual time=0.033..0.187 rows=1000.00 loops=1)
               Buffers: shared read=5
 Planning:
   Buffers: shared hit=31 read=16
 Planning Time: 0.751 ms
 Execution Time: 12.599 ms
(18 rows)

--
Best regards,
Ilia Evdokimov,
Tantor Labs LLC.