From: Tomas Vondra <tomas.vondra@enterprisedb.com>
To: nicolas paris <nicolas.paris@riseup.net>
To: pgsql-performance@lists.postgresql.org
Subject: Re: Merge David and Goliath tables efficiently
Date: Tue, 20 Jun 2023 14:45:30 +0200
Message-ID: <3efe2d22-504a-a1bc-79fa-cff64c81e76a@enterprisedb.com> (raw)
In-Reply-To: <63977a990ee6f3fbbedf4ca78e289591fe38d8a3.camel@riseup.net>
References: <20230619074646.2zlxxaqjieiafgqi@alvherre.pgsql>
<27cdb9c5-6dc7-87ff-303e-b0d0d20edbc4@enterprisedb.com>
<dc4115489cd1bbddab1fea3cf0eeb8bdd06657e7.camel@riseup.net>
<36cb9fe9-b792-2345-65ed-586de44a48b9@enterprisedb.com>
<45316372426111f4b1dffa6de420804da3befa3d.camel@riseup.net>
<ee9a5c6e-c4a2-44e8-4c77-4fa5ce7b31cc@enterprisedb.com>
<63977a990ee6f3fbbedf4ca78e289591fe38d8a3.camel@riseup.net>
On 6/20/23 12:02, nicolas paris wrote:
>...
>
> Definitely this makes sense to add the part_col in the join columns.
> Also it helps the planner to choose a better plan, since now it goes
> with per partition nested loop without having to trick the costs
> (either enable_hashjoin/random_page_cost), with my current workload so
> far.
>
Right. With non-partitionwise join the nestloop inner lookup has to do
indexscan on every partition (it can't decide which of the partitions
will have a match, and for costing we assume there's at least 1 row in
each lookup). Which essentially amplifies the amount of random I/O by a
factor of 100x (or whatever the number of partitions is).
That is, instead of doing 100x nested loops like this:
-> Nested Loop Left Join (cost=0.29..33.42 rows=8 width=47)
-> Seq Scan on david_98 david_99 (cost=0.00..1.08
-> Index Scan using goliath_98_id_part_col_idx on
Index Cond: ((id = david_99.id) AND ...)
we end up doing one nested loop with an inner lookup like this
-> Append (cost=0.29..557.63 rows=100 width=14)
-> Index Scan using ... goliath_1 (cost=0.29..5.57 ...
Index Cond: (id = david.id)
...
And this is per-loop, of which there'll be 500 (because the small david
table has 500 rows).
regards
--
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tomas.vondra@enterprisedb.com, nicolas.paris@riseup.net, pgsql-performance@lists.postgresql.org
Subject: Re: Merge David and Goliath tables efficiently
In-Reply-To: <3efe2d22-504a-a1bc-79fa-cff64c81e76a@enterprisedb.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox