Such situation. PostgreSQL 9.4 is used. There is a table messages_conversation, in which there are indices:

CREATE INDEX messages_conversation_multi_index ON messages_conversation USING btree (app_id, replied, closed, assignee_id, tags) WHERE replied = TRUE CREATE INDEX messages_conversation_last_update_f0131d4a8e80d76_uniq ON messages_conversation USING btree (last_update) CREATE INDEX messages_conversation_f382adfe ON messages_conversation USING btree (app_id)

And there are 2 queries to this table:

 EXPLAIN ANALYSE SELECT "messages_conversation"."id", "messages_conversation"."created", "messages_conversation"."last_update", "messages_conversation"."user_id", "messages_conversation"."app_id", "messages_conversation"."read", "messages_conversation"."replied", "messages_conversation"."clicked", "messages_conversation"."unsubscribed", "messages_conversation"."bounced", "messages_conversation"."spam", "messages_conversation"."goal_completed", "messages_conversation"."goal_profit", "messages_conversation"."external_service", "messages_conversation"."external_id", "messages_conversation"."settings", "messages_conversation"."replies_count", "messages_conversation"."parts_count", "messages_conversation"."part_last_id", "messages_conversation"."reply_last_id", "messages_conversation"."reply_last_type", "messages_conversation"."user_unread_count", "messages_conversation"."last_django_user_id", "messages_conversation"."tags", "messages_conversation"."closed", "messages_conversation"."message_id", "messages_conversation"."assignee_id", "messages_conversation"."type", "messages_conversation"."reply_type" FROM "messages_conversation" WHERE ("messages_conversation"."app_id" = 5038 AND "messages_conversation"."replied" = true AND "messages_conversation"."closed" = false) ORDER BY "messages_conversation"."last_update" DESC, replied DESC LIMIT 20; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Limit (cost=7569.71..7569.76 rows=20 width=169) (actual time=0.363..0.366 rows=12 loops=1) -> Sort (cost=7569.71..7749.36 rows=71860 width=169) (actual time=0.360..0.362 rows=12 loops=1) Sort Key: last_update, replied Sort Method: quicksort Memory: 27kB -> Index Scan using messages_conversation_multi_index on messages_conversation (cost=0.43..5657.54 rows=71860 width=169) (actual time=0.030..0.336 rows=12 loops=1) Index Cond: ((app_id = 5038) AND (replied = true) AND (closed = false)) Filter: (NOT closed) Planning time: 0.150 ms Execution time: 0.403 ms (9 rows)

And the second:

 EXPLAIN ANALYZE SELECT "messages_conversation"."id", "messages_conversation"."created", "messages_conversation"."last_update", "messages_conversation"."user_id", "messages_conversation"."app_id", "messages_conversation"."read", "messages_conversation"."replied", "messages_conversation"."clicked", "messages_conversation"."unsubscribed", "messages_conversation"."bounced", "messages_conversation"."spam", "messages_conversation"."goal_completed", "messages_conversation"."goal_profit", "messages_conversation"."external_service", "messages_conversation"."external_id", "messages_conversation"."settings", "messages_conversation"."replies_count", "messages_conversation"."parts_count", "messages_conversation"."part_last_id", "messages_conversation"."reply_last_id", "messages_conversation"."reply_last_type", "messages_conversation"."user_unread_count", "messages_conversation"."last_django_user_id", "messages_conversation"."tags", "messages_conversation"."closed", "messages_conversation"."message_id", "messages_conversation"."assignee_id", "messages_conversation"."type", "messages_conversation"."reply_type" FROM "messages_conversation" WHERE ("messages_conversation"."app_id" = 5038 AND "messages_conversation"."replied" = true AND "messages_conversation"."closed" = false) ORDER BY "messages_conversation"."last_update" DESC LIMIT 20; QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Limit (cost=0.56..985.38 rows=20 width=169) (actual time=0.045..108297.855 rows=7 loops=1) -> Index Scan Backward using messages_conversation_last_update_f0131d4a8e80d76_uniq on messages_conversation (cost=0.56..3538428.71 rows=71860 width=169) (actual time=0.045..108297.849 rows=7 loops=1) Filter: (replied AND (NOT closed) AND (app_id = 5038)) Rows Removed by Filter: 56438067 Planning time: 0.149 ms Execution time: 108297.886 ms (6 rows)

As you can see from the queries, the only difference is ORDER BY replied DESC. This is a boolean, for which in WHERE hard is replied = true. All where fields are subscripted above.

And now, attention to the question: Why in 2 case Index Cond was not applied, although the WHERE fields did not change? Why does removing sorting affect the internal condition at all?

Well, I didn’t quite understand: why in 1 request Filter: (NOT closed), if it is in the index?

  • No one will answer the question why the plan is exactly like this except for postrgesql optimizer developers. All others can only guess what logic was applied. To select a specific index and execution path, the optimizer is guided by a large amount of additional information, which is not visible from the table structure and execution plans. For example, statistics on the number of those or other values ​​in the columns. He calculates the cost for different ways and chooses the one that he thinks is better. - Mike
  • Actually, the question is not why the index is so built, but how to remove the extra sorting without losing the index? And how to make the optimizer use it without a crutch sort? - Mikhail Shvein

0