Message-ID: <3efe2d22-504a-a1bc-79fa-cff64c81e76a@enterprisedb.com>
Date: Tue, 20 Jun 2023 14:45:30 +0200
MIME-Version: 1.0
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101
 Thunderbird/102.11.1
Subject: Re: Merge David and Goliath tables efficiently
Content-Language: en-US
To: nicolas paris <nicolas.paris@riseup.net>,
 pgsql-performance@lists.postgresql.org
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>
From: Tomas Vondra <tomas.vondra@enterprisedb.com>
In-Reply-To: <63977a990ee6f3fbbedf4ca78e289591fe38d8a3.camel@riseup.net>
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 7bit
Archived-At: 
 <https://www.postgresql.org/message-id/3efe2d22-504a-a1bc-79fa-cff64c81e76a%40enterprisedb.com>
Precedence: bulk

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