Re: PATCH: use foreign keys to improve join estimates v1

From: Tomas Vondra <tomas(dot)vondra(at)2ndquadrant(dot)com>
To: David Rowley <david(dot)rowley(at)2ndquadrant(dot)com>
Cc: PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: PATCH: use foreign keys to improve join estimates v1
Date: 2015-08-20 02:25:36
Message-ID: 55D53AA0.7040705@2ndquadrant.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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)
Hash Cond: ((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 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)
-> Index Only Scan using b_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

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Amit Kapila 2015-08-20 03:06:31 Re: Proposal: Implement failover on libpq connect level.
Previous Message Peter Geoghegan 2015-08-20 02:24:26 Using quicksort for every external sort run