Skip to main content
add discussion of new query plans
Source Link
jjanes
  • 41.3k
  • 3
  • 40
  • 54

With your current indexes, there is no efficient way to reverse the nested loop. The index on (ts desc, event_id desc) cannot be used to efficiently find rows with a specific event_id, because it would have to scan through all the different ts in the given range (which is large) in order to identify them.

With an appropriate index, such as one on either (event_id) or (event_id, ts), then in my hands it makes appropriate choices when faced with either very common or very rare conditions on the event table. That is, it uses your plan for common ones, and for rare ones uses a bitmapOr to get all the rows from events which match the criteria, then looks up in timeline for each one of them and sorts the results. https://explain.depesz.com/s/6xgUk

I think the remaining bad plan (once you have added the index) is down to poor selectivity estimates. Notice that it is expecting 9229 rows with foo = 3809, but there are really zero. That is a huge difference, and that bad estimate will of course lead to bad planning choices. Based on this other line, "Heap Fetches: 109397" I think your must not have done the VACUUM ANALYZE that I asked for, and your statistics are most likely out of date. Some things are inherently hard to estimate, but a simple index scan on a single-column unfiltered btree index usually is not one of them.

If manual VACUUM ANALYZE does fix the problem, then the question might become, why isn't autovacuum doing a good enough job?

With your current indexes, there is no efficient way to reverse the nested loop. The index on (ts desc, event_id desc) cannot be used to efficiently find rows with a specific event_id, because it would have to scan through all the different ts in the given range (which is large) in order to identify them.

With an appropriate index, such as one on either (event_id) or (event_id, ts), then in my hands it makes appropriate choices when faced with either very common or very rare conditions on the event table. That is, it uses your plan for common ones, and for rare ones uses a bitmapOr to get all the rows from events which match the criteria, then looks up in timeline for each one of them and sorts the results. https://explain.depesz.com/s/6xgUk

With your current indexes, there is no efficient way to reverse the nested loop. The index on (ts desc, event_id desc) cannot be used to efficiently find rows with a specific event_id, because it would have to scan through all the different ts in the given range (which is large) in order to identify them.

With an appropriate index, such as one on either (event_id) or (event_id, ts), then in my hands it makes appropriate choices when faced with either very common or very rare conditions on the event table. That is, it uses your plan for common ones, and for rare ones uses a bitmapOr to get all the rows from events which match the criteria, then looks up in timeline for each one of them and sorts the results. https://explain.depesz.com/s/6xgUk

I think the remaining bad plan (once you have added the index) is down to poor selectivity estimates. Notice that it is expecting 9229 rows with foo = 3809, but there are really zero. That is a huge difference, and that bad estimate will of course lead to bad planning choices. Based on this other line, "Heap Fetches: 109397" I think your must not have done the VACUUM ANALYZE that I asked for, and your statistics are most likely out of date. Some things are inherently hard to estimate, but a simple index scan on a single-column unfiltered btree index usually is not one of them.

If manual VACUUM ANALYZE does fix the problem, then the question might become, why isn't autovacuum doing a good enough job?

Source Link
jjanes
  • 41.3k
  • 3
  • 40
  • 54

With your current indexes, there is no efficient way to reverse the nested loop. The index on (ts desc, event_id desc) cannot be used to efficiently find rows with a specific event_id, because it would have to scan through all the different ts in the given range (which is large) in order to identify them.

With an appropriate index, such as one on either (event_id) or (event_id, ts), then in my hands it makes appropriate choices when faced with either very common or very rare conditions on the event table. That is, it uses your plan for common ones, and for rare ones uses a bitmapOr to get all the rows from events which match the criteria, then looks up in timeline for each one of them and sorts the results. https://explain.depesz.com/s/6xgUk