Re: Ambigous Plan - Larger Table on Hash Side

On Mon, Mar 12, 2018 at 10:02 PM, Narendra Pradeep U U
<narendra(dot)pradeep(at)zohocorp(dot)com> wrote:
> Hi ,
>
> Recently I came across a case where the planner choose larger table on
> hash side. I am not sure whether it is an intended behavior or we are
> missing something.
>
> I have two tables (a and b) each with single column in it. One table
> 'a' is large with around 30 million distinct rows and other table 'b' has
> merely 70,000 rows with one-seventh (10,000) distinct rows. I have analyzed
> both the table. But while joining both the table I get the larger table on
> hash side.
>
> tpch=# explain select b from b left join a on a = b;
> QUERY PLAN
> ---------------------------------------------------------------------------------------------------------
> Hash Left Join (cost=824863.75..950104.42 rows=78264 width=4)
> Hash Cond: (b.b = a.a)o
> -> Foreign Scan on b (cost=0.00..821.64 rows=78264 width=4)
> CStore File:
> /home/likewise-open/pg96/data/cstore_fdw/1818708/1849879
> CStore File Size: 314587
> -> Hash (cost=321721.22..321721.22 rows=30667722 width=4)
> -> Foreign Scan on a (cost=0.00..321721.22 rows=30667722 width=4)
> CStore File:
> /home/likewise-open/pg96/data/cstore_fdw/1818708/1849876
> CStore File Size: 123236206
> (9 rows)
>
>
>
> I would like to know the reason for choosing this plan and Is there a easy
> fix to prevent such plans (especially like this one where it choose a larger
> hash table) ?

A plan with larger table being hashed doesn't necessarily bad
performing one. During partition-wise join analysis I have seen plans
with larger table being hashed perform better than the plans with
smaller table being hashed. But I have seen the other way around as
well. Although, I don't know an easy way to force which side of join
gets hashed. I tried that under the debugger. In your case, if you run
EXPLAIN ANALYZE on this query, produce outputs of two plans: one with
larger table being hashed and second with the smaller one being
hashed, you will see which of them performs better.

--
Best Wishes,
Ashutosh Bapat
EnterpriseDB Corporation
The Postgres Database Company