Thanks for the reply Jeff. Yes- more of an academic question. Regarding this part:
Index Cond: ((workflow_id = 1070) AND ((status)::text = ANY ('{NOT_STARTED,PAUSED,PENDING,RUNNING}'::text[])))
Filter: (deleted_millis <= 0)
Buffers: shared hit=24
For this usage, the =ANY is applied as an "in-index filter". It only descends the index once, to where workflow_id=1070, and then scans forward applying the =ANY to each index-tuple until it exhausts the =1070 condition. As long as all the =1070 entries fit into just a few buffers, the count of buffers accessed by doing this is fewer than doing the re-descents. (Stepping from tuple to tuple in the same index page doesn't count as a new access. While a re-descent releases and reacquires the buffer)
There are 2,981,425 rows where workflow_id = 1070. Does that change your theory of using an “in-index filter” for that plan? When you say there was a bit of speculation on the “boundard condition” vs “in-index filter” is the speculation on if Postgres has 2 different ways of processing a =ANY filter or is the speculation that one is being used by one plan and the other is being used by the other plan?
