Re: [EXT] Re: Why using a partial index is doing slightly more logical I/O than a normal index

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?