Ancoron Luciferis <ancoron.luciferis@xxxxxxxxxxxxxx> writes: > One of the things consuming most of the time was an Index Only Scan > executed millions of times. And on top came the Nested Loop which > finally reduced the rows but also took a lot of time to do so. > Explain plan: https://explain.depesz.com/s/4GYT The core problem you've got there is the misestimation of the join size: Nested Loop (cost=0.71..0.30 rows=72,839,557 width=33) (actual time=19,504.096..315,933.158 rows=274 loops=1) Anytime the planner is off by a factor of 250000x, it's not going to end well. In this case, it's imagining that the LIMIT will kick in after just a very small part of the join is executed --- but in reality, the LIMIT is larger than the join output, so that we have to execute the whole join. With a more accurate idea of the join result size, it would have chosen a different plan. What you ought to look into is why is that estimate so badly off. Maybe out-of-date stats, or you need to raise the stats target for one or both tables? > My question now is why can't the optimizer generate a plan that in this > case does 114 loops of "events" scans instead of a million loops on the > "subscription_signal"? I don't see any "events" table in that query, so this question isn't making a lot of sense to me. But in any case, the answer probably boils down to "it's guessing that a plan like this will stop early without having to scan all of the large table". regards, tom lane
