From: | Narendra Pradeep U U <narendra(dot)pradeep(at)zohocorp(dot)com> |
---|---|
To: | "pgsql-hackers" <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Ambigous Plan - Larger Table on Hash Side |
Date: | 2018-03-12 16:32:52 |
Message-ID: | 1621b0eba9f.d7a723f423871.7326412906996936449@zohocorp.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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) ?
Thanks,
Pradeep
From | Date | Subject | |
---|---|---|---|
Next Message | Alvaro Herrera | 2018-03-12 16:34:15 | Re: pgsql: Allow UNIQUE indexes on partitioned tables |
Previous Message | Tom Lane | 2018-03-12 16:21:34 | Re: [patch] BUG #15005: ANALYZE can make pg_class.reltuples inaccurate. |