Re: Planner misestimation for JOIN with VARCHAR

> On 9. Jun 2020, at 21:30, Michael Lewis <mlewis(at)entrata(dot)com> wrote:
>
>> On Tue, Jun 9, 2020 at 12:34 PM Sebastian Dressler <sebastian(at)swarm64(dot)com> wrote:
>> - Add an index on top of the whole PK
>> - Add indexes onto other columns trying to help the JOIN
>> - Add additional statistics on two related columns
>>
>> Another idea I had was to make use of generated columns and hash the PKs together to an BIGINT and solely use this for the JOIN. However, this would not work when not all columns of the PK are used for the JOIN.
>
> Can you expand on the additional statistics you created? Why was it on only two columns? Did you include MCVs type of extended stats?

Sure, for the MCVs specifically I tried to select which columns belong together to also have value for the end-user when running a query. Basically in accordance what the docs suggest, i.e. [1] section 14.2.2.3 last paragraph. Given the nature of the data I however think this can be very difficult to do without requiring further user input. Likewise, as the others suggested, it did not help for this particular case.

Cheers,
Sebastian

[1]: https://www.postgresql.org/docs/current/planner-stats.html#PLANNER-STATS-EXTENDED

--

Sebastian Dressler, Solution Architect
+49 30 994 0496 72 | sebastian(at)swarm64(dot)com

Swarm64 AS
Parkveien 41 B | 0258 Oslo | Norway
Registered at Brønnøysundregistrene in Norway under Org.-Number 911 662 787
CEO/Geschäftsführer (Daglig Leder): Thomas Richter; Chairman/Vorsitzender (Styrets Leder): Dr. Sverre Munck

Swarm64 AS Zweigstelle Hive
Ullsteinstr. 120 | 12109 Berlin | Germany
Registered at Amtsgericht Charlottenburg - HRB 154382 B