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

[Arturas Mazeika <mazeika@xxxxxxxxx>]

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@xxxxxxxxxxx> 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.