Re: PATCH: use foreign keys to improve join estimates v1 - Mailing list pgsql-hackers

On 08/20/2015 03:49 AM, Tomas Vondra wrote:
> Then on current master, I get these estimates (showing just rows,
> because that's what matter):
>
>
> while with the patch I get this:

And of course I forgot to include the plans from master, so here we go:

select * from f join a on (f1 = a1 and f2 = a2);
                                QUERY PLAN
------------------------------------------------------------------------ Hash Join  (rows=10) (actual rows=100000)
HashCond: ((f.f1 = a.a1) AND (f.f2 = a.a2))   ->  Seq Scan on f  (rows=100000) (actual rows=100000)   ->  Hash
(rows=10000)(actual rows=10000)         Buckets: 16384  Batches: 1  Memory Usage: 519kB         ->  Seq Scan on a
(rows=10000)(actual rows=10000)
 


select * from f join a on (f1 = a1 and f2 = a2)                join b on (f3 = b1 and f4 = b2);
                                QUERY PLAN
------------------------------------------------------------------------ Nested Loop  (rows=1) (actual rows=100000)
-> Hash Join  (rows=10) (actual rows=100000)         Hash Cond: ((f.f1 = a.a1) AND (f.f2 = a.a2))         ->  Seq Scan
onf  (rows=100000) (actual rows=100000)         ->  Hash  (rows=10000) (actual rows=10000)               Buckets: 16384
Batches: 1  Memory Usage: 519kB               ->  Seq Scan on a  (rows=10000) (actual rows=10000)   ->  Index Only Scan
usingb_pkey on b  (rows=1) (actual rows=1)         Index Cond: ((b1 = f.f3) AND (b2 = f.f4))
 

regards

--
Tomas Vondra                  http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services