PostgreSQL - Row Estimates Leading to Nested Loop Being Selected Too Often, How To Improve Row Estimates

Question

I am running PG10 (soon to move to PG13), and I am having performance trouble with a particular query. I have also tested in PG13 and get similar results. Most of the joins in this larger query are selected for Nested Joins, but it seems a Hash Join would be more appropriate for most.

I see that the first nodes joined might be driving the statistics of later joins, where row estimates are quite low. The first joins are as follows: https://explain.dalibo.com/plan/b5045eb7cffdf0dh

SELECT * from workflowable_resource where sample_type_id in (SELECT resource_id from resource where name = 'Test Order')

While this smaller query is quite fast, I see many examples where Nested Loop is selected incorrectly where a Hash Join might perform better. (Row estimate is 1, or close to 1, but actual rows are orders of magnitude greater)

What are some general techniques for improving row estimates in these cases? I've tried increasing default_statistics_target to 10000 and analyzing all tables again, but I did not see any improvement on row estimates.

In the larger query, on the scaled up database, there is one node taking all the time. There is an Index scan on the table, but it is performed in a Nested Loop as the left hand side is estimated at 1 row, but at this point is running with 178,000 rows. It seems like errors from previous row estimates are being carried forward.

Answer 1

For the first query, it doesn't know which particular resource_id is associated with the of name of 'Test Order' until after the planning is over with. So it cannot use that specific value to estimate the number of rows from workflowable_resource it will find. So instead it has to use the generic estimate, which basically is going to be #number of rows in workflowable_resource / n_distinct for sample_type_id. There isn't much you can do about this with that query, no amount of additional statistics is going to help you as the estimate is generic anyway (unless n_distinct is way off). You could break it into two queries, plugging the output of one into the parameter of the other. But you have to do this manually, there is no way to instruct PostgreSQL to execute the inner query and then replan the outer one based on the results. Also, it likely doesn't matter, as I see no reason to think a better estimate would result in a better plan.

For the larger query, maybe a better estimate would lead to a better plan, but the information provided is inadequate to speculate. We have neither the actual plan nor the actual query.

Answer 2

The problem with your much larger query is rather different than with the first one, so I am adding a 2nd answer to discuss this one.

The first place where the selectivity is massively off is here:

->  Hash Join  (cost=542.37..2099.91 rows=1 width=12) (actual time=0.340..20.327 rows=6576 loops=1)                                                                                                         
  ->  Seq Scan on three_echo kilo_five  (cost=0.00..1406.89 rows=57389 width=8) (actual time=0.020..8.036 rows=57395 loops=1)
  ->  Hash  (cost=540.67..540.67 rows=136 width=4) (actual time=0.111..0.121 rows=3 loops=1)

So it thinks that of the 60,000 rows from the seq scan, only one will match to any row from the hash table. But instead, 1/10 of them find a partner in the hash table. Even though the hash table is like 40 times smaller than it thinks it will be. So I am guessing the filter applied when building the hash table selectively pulls out rows which have values which are very common the seq-scanned table (so this part actually is rather similar to the problem in the first query). Maybe you could write a much simpler query which embodies this hash join and nothing else, see of teh selectivity problem still exists, and then explore the pg_stats entries for the relevant columns of the relevant tables. Maybe you could share the unobfuscated version of that more focused query, as the obfuscation really makes it hard to reason about what is happening.

However, I'm not sure this row estimate problem is really the main culprit in the slow performance.

If the look at the plan-row which actually takes all the time:

-> Index Scan using oscar_mike on echo_four kilo_golf (cost=0.42..1,427.16 rows=11 width=12) (actual time=2.775..3.445 rows=4 loops=178,773)
   Index Cond: (kilo_golf.delta_two = alpha_xray_charlie2.four_kilo)
   Buffers: shared hit=93,849,709 dirtied=1

This index actually returns 4*178773 rows. Why does it take 93849709/4/178773 = 131 buffers hits for every row being returned? That is like 60 times more buffer accesses than what seems reasonable to me. I guess the index is full of dead tuples which have not yet been removed, or maybe it just horribly fragmented or something. I would manually VACUUM VERBOSE this table and see if that fixed (or substantially improved) the issue. If not, then I would REINDEX this specific index and see if that did it. If neither of them work, then please share with us the output of the VACUUM VERBOSE.

The blog post you reference in the comment seems a bit garbled to me, but in any case doesn't really seem relevant to you. His example has 13 buffers hits for each loop (1,299,990/100,000) and each loop returns 10 row (on average, rounded to the nearest integer). 13 is exactly what I would expect, as it usually takes 3 buffers accesses to descend the index, and then 1 buffer access for each of the 10 rows (assuming the rows are randomly scattered), 3+10=13.

You on the other hand are getting 525 buffer access per loop, which is on a whole other scale. Using the same logic as before, 3 to descend the index and one for each of 4 tuples, I would predict only 7 buffer accesses per loop, nearly 100 fold less than you are actually seeing.

Does looping over the same Shared Buffer page many times indicate a possible performance problem?

Absolutely. Finding and pinning a buffer which is already in cache is a CPU-intensive operation. It requires juggling a lot of book keeping data, and doing so under locks or atomic operations (to prevent concurrent backends from corrupting each other). That locking activity makes things slow, even when the locks are not actually contested. Now, this is no where near as slow as actually reading data from disk would be. But among non-IO operations, it is very much a heavy-weight. And, if you do it 60 times more often than is reasonable (for some still-mysterious reason), yeah it is going to cause problems.