Skip to main content
Database Administrators

Return to Answer

deleted 1 character in body
Source Link
jjanes
jjanes
  • 41.3k
  • 3
  • 40
  • 54

The problem is that your buffers which have low created_date are selectively depleted in things which match your WHERE clause. So it needs to walk more and more of the index before it accumulates 10 rows which match the WHERE. Presumably the reason it is selectively depleted is that once you get the results of your query, you do something to the rows which cause them to no longer match the WHERE clause next time the query gets run. I'm guessing based on thatthe column names that that thing is setting "completed_date" to something which no longer NULL? If so, you might want to create in index on (completed_date, created_date). Or maybe (shop_id,completed_date, created_date).

The reason that changing the sort order improves the query is that it starts using part of the index not selectively depleted. If you were to adopt that sort order in production, you would find that its performance quickly degrades as that part of the index would become depleted.

You showed us the definition of one index, but clearly that is not your only index, and it not clear what you believe the significance of that index is.

The problem is that your buffers which have low created_date are selectively depleted in things which match your WHERE clause. So it needs to walk more and more of the index before it accumulates 10 rows which match the WHERE. Presumably the reason it is selectively depleted is that once you get the results of your query, you do something to the rows which cause them to no longer match the WHERE clause next time the query gets run. I'm guessing based on that column names that that thing is setting "completed_date" to something which no longer NULL? If so, you might want to create in index on (completed_date, created_date). Or maybe (shop_id,completed_date, created_date).

The reason that changing the sort order improves the query is that it starts using part of the index not selectively depleted. If you were to adopt that sort order in production, you would find that its performance quickly degrades as that part of the index would become depleted.

You showed us the definition of one index, but clearly that is not your only index, and it not clear what you believe the significance of that index is.

The problem is that your buffers which have low created_date are selectively depleted in things which match your WHERE clause. So it needs to walk more and more of the index before it accumulates 10 rows which match the WHERE. Presumably the reason it is selectively depleted is that once you get the results of your query, you do something to the rows which cause them to no longer match the WHERE clause next time the query gets run. I'm guessing based on the column names that that thing is setting "completed_date" to something which no longer NULL? If so, you might want to create in index on (completed_date, created_date). Or maybe (shop_id,completed_date, created_date).

The reason that changing the sort order improves the query is that it starts using part of the index not selectively depleted. If you were to adopt that sort order in production, you would find that its performance quickly degrades as that part of the index would become depleted.

You showed us the definition of one index, but clearly that is not your only index, and it not clear what you believe the significance of that index is.

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

The problem is that your buffers which have low created_date are selectively depleted in things which match your WHERE clause. So it needs to walk more and more of the index before it accumulates 10 rows which match the WHERE. Presumably the reason it is selectively depleted is that once you get the results of your query, you do something to the rows which cause them to no longer match the WHERE clause next time the query gets run. I'm guessing based on that column names that that thing is setting "completed_date" to something which no longer NULL? If so, you might want to create in index on (completed_date, created_date). Or maybe (shop_id,completed_date, created_date).

The reason that changing the sort order improves the query is that it starts using part of the index not selectively depleted. If you were to adopt that sort order in production, you would find that its performance quickly degrades as that part of the index would become depleted.

You showed us the definition of one index, but clearly that is not your only index, and it not clear what you believe the significance of that index is.