Re: hashjoins, index loops to retrieve pk/ux constrains in pg12

Hi Michael,

Thanks for the answer.

I agree that the tables behind the views makes the query processing
challenging. What makes it even more challenging to us is that this query
is generated by a third party library that we use to operationalize the
schema changes.

I am trying to figure out what went wrong with query planning that
hashjoins perform worse compared to index/sort joins. It looks to me that
this is mostly because (1) the temporal space for creating a hashtable is a
lot larger compared to sort/index joins and (2) it is *not *that the
predicted selectivity is way off compared to the actual selectivity. W.r.t
(1) in almost all cases the IOs needed to do hashing is way bigger compared
to indexes (see in red if your email client supports html formatting, only
in one parameter the hash joins "win" against the index/sort joins see in
green, and the actual times are always worse, see in blue):

-> Hash Join (cost=415.40..494.06
rows=263 width=136) (actual time=0.007..0.869 rows=1707 loops=1672)
Output: c_5.conname,
c_5.connamespace, r_5.relname, r_5.relnamespace
Inner Unique: true
Hash Cond: (c_5.conrelid
= r_5.oid)
Buffers: shared hit=87218

vs. corresponding index/sort join:

-> Nested Loop (cost=0.28..171.05 rows=1
width=136) (actual time=0.024..1.976 rows=595 loops=2)
Output: c_4.conname,
c_4.connamespace, r_5.relname, r_5.relnamespace
Inner Unique: true
Buffers: shared hit=3674

or looking at the global level:

Nested Loop (cost=2174.36..13670.47 rows=1 width=320) (actual
time=5499.728..26310.137 rows=2 loops=1)
Output: "*SELECT* 1".table_name,
(a.attname)::information_schema.sql_identifier, "*SELECT* 1_1".table_name,
(a_1.attname)::information_schema.sql_identifier,
(con.conname)::information_schema.sql_identifier
Inner Unique: true
Buffers: shared hit=1961035

vs

Nested Loop (cost=1736.10..18890.44 rows=1 width=320) (actual
time=30.780..79.572 rows=2 loops=1)
Output: "*SELECT* 1".table_name,
(a.attname)::information_schema.sql_identifier, "*SELECT* 1_1".table_name,
(a_1.attname)::information_schema.sql_identifier,
(con.conname)::information_schema.sql_identifier
Inner Unique: true
Buffers: shared hit=9018

Which makes me wonder why hash join was chosen at all. Looks like a bug
somewhere in query optimization.

Cheers,
Arturas

On Fri, Sep 24, 2021 at 7:34 AM Michael Lewis <mlewis(at)entrata(dot)com> wrote:

> I believe that this is a planning problem with the number of tables/joins
> involved in the query you have written. If you take a look at the
> definition of the views in information_schema that you are using and read
> about from_collapse_limit/join_collapse_limit, you may see that this is a
> bit painful for the planner. It might be cumbersome to use the actual
> system tables underneath, but that would certainly lead to much better
> performance. Otherwise, I would look at perhaps putting the view that has a
> WHERE condition on it as the FROM to encourage the planner to perhaps
> filter that set first and join the other tables after. If that didn't help,
> I might even use a materialized CTE to force the issue.
>
> Hopefully a real expert will chime in with a better explanation of the
> challenges or preferred solution.
>