pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Why using a partial index is doing slightly more logical I/O than a normal index
5+ messages / 3 participants
[nested] [flat]

* Why using a partial index is doing slightly more logical I/O than a normal index
@ 2023-05-03 13:16  Dirschel, Steve <steve.dirschel@thomsonreuters.com>
  0 siblings, 1 reply; 5+ messages in thread

From: Dirschel, Steve @ 2023-05-03 13:16 UTC (permalink / raw)
  To: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>

Table definition:

workflow_db=> \d workflow_execution_test
                            Table "public.workflow_execution_test"
              Column              |           Type           | Collation | Nullable | Default
----------------------------------+--------------------------+-----------+----------+---------
execution_id                     | bigint                   |           |          |
state_machine_id                 | bigint                   |           |          |
workflow_id                      | bigint                   |           |          |
started_datetime                 | timestamp with time zone |           |          |
completed_datetime               | timestamp with time zone |           |          |
status                           | character varying(50)    |           |          |
execution_context_s3_arn         | character varying(200)   |           |          |
ol_version                       | integer                  |           |          |
created_datetime                 | timestamp with time zone |           |          |
updated_datetime                 | timestamp with time zone |           |          |
deleted_millis                   | bigint                   |           |          |
acquisition_channel_id           | bigint                   |           |          |
correlation_id                   | character varying(36)    |           |          |
result                           | character varying(50)    |           |          |
state_machine_execution_arn      | character varying(200)   |           |          |
created_by_id                    | bigint                   |           |          |
updated_by_id                    | bigint                   |           |          |
acquired_gcs_s3_object           | text                     |           |          |
sqs_trigger_id                   | bigint                   |           |          |
trigger_message                  | text                     |           |          |
acquired_gcs_s3_object_uuid      | character varying(36)    |           |          |
api_trigger_id                   | bigint                   |           |          |
scheduled_trigger_id             | bigint                   |           |          |
notification_trigger_workflow_id | bigint                   |           |          |
acquired_object_name             | text                     |           |          |
subscription_guid                | uuid                     |           |          |
processing_class_code            | character varying(50)    |           |          |
root_execution_guid              | uuid                     |           |          |
Indexes:
    "test_workflow_execution_active_pending_ordered_by_created_date_" btree (workflow_id, status, deleted_millis, created_datetime) WHERE (status::text = ANY (ARRAY['ACTION_NEEDED'::character varying, 'NOT_STARTED'::character varying, 'PAUSED'::character varying, 'PENDING'::character varying, 'RUNNING'::character varying]::text[])) AND deleted_millis <= 0
    "test_workflow_execution_initial_ui_tabs" btree (workflow_id, status, result, completed_datetime DESC NULLS LAST)

I created/populated this table with 22 million rows.  Afterwards I then created the 2 indexes.  So those indexes are packed tightly.

As is the optimizer decides to use the partial index.  Below shows it did 33 logical reads.  This index should be very good for this query-  the leading 3 columns of the index are on the 3 criteria in the WHERE clause and the partial part is only storing rows that match the status and deleted_millis filters.

explain (analyze, buffers)
select * from workflow_execution_test
where workflow_id = 1070
AND status in ('NOT_STARTED','PAUSED','PENDING','RUNNING')
and deleted_millis <= 0;
                                                                                             QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Index Scan using test_workflow_execution_active_pending_ordered_by_created_date_ on workflow_execution_test  (cost=0.43..15824.82 rows=4335 width=1309) (actual time=0.040..0.095 rows=56 loops=1)
   Index Cond: ((workflow_id = 1070) AND ((status)::text = ANY ('{NOT_STARTED,PAUSED,PENDING,RUNNING}'::text[])))
  Buffers: shared hit=33
 Planning:
   Buffers: shared hit=2
Planning Time: 0.321 ms
Execution Time: 0.117 ms

If I hint the query to use the other index it does less work-  it does 24 logical reads vs 33 using the partial index.

/*+ IndexScan(workflow_execution_test test_workflow_execution_initial_ui_tabs) */
explain (analyze, buffers)
select * from workflow_execution_test
where workflow_id = 1070
AND status in ('NOT_STARTED','PAUSED','PENDING','RUNNING')
and deleted_millis <= 0;
                                                                                 QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Index Scan using test_workflow_execution_initial_ui_tabs on workflow_execution_test  (cost=0.56..15820.19 rows=4335 width=1309) (actual time=0.049..0.106 rows=56 loops=1)
   Index Cond: ((workflow_id = 1070) AND ((status)::text = ANY ('{NOT_STARTED,PAUSED,PENDING,RUNNING}'::text[])))
   Filter: (deleted_millis <= 0)
   Buffers: shared hit=24
Planning:
   Buffers: shared hit=2
Planning Time: 0.373 ms
Execution Time: 0.129 ms

It may make sense to me if the query did the same amount of work using either of the 2 indexes but it does not make sense to me why the partial index is doing more work.  Could anyone explain this behavior?

One other thing to note-  when using the partial index the cost is .43 .. 15824.82.  When using the other index the cost is .56 .. 15820.19.  So the lower end cost (I believe the cost to find the first row) is slightly lower for the partial index but the higher end cost (I believe to find the last row) is higher for the partial index.  Since there is no LIMIT clause why wouldn't the optimizer use the lowest cost to find all rows (which in this case would be to use the non-partial index)?

Thanks
This e-mail is for the sole use of the intended recipient and contains information that may be privileged and/or confidential. If you are not an intended recipient, please notify the sender by return e-mail and delete this e-mail and any attachments. Certain required legal entity disclosures can be accessed on our website: https://www.thomsonreuters.com/en/resources/disclosures.html

^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: Why using a partial index is doing slightly more logical I/O than a normal index
@ 2023-05-03 17:37  Jeff Janes <jeff.janes@gmail.com>
  parent: Dirschel, Steve <steve.dirschel@thomsonreuters.com>
  0 siblings, 1 reply; 5+ messages in thread

From: Jeff Janes @ 2023-05-03 17:37 UTC (permalink / raw)
  To: Dirschel, Steve <steve.dirschel@thomsonreuters.com>; +Cc: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>

Because both the actual times and the expected costs are so similar to each
other, I am assuming you are asking this as more of an academic question
than a practical one.  If it is actually a practical matter, you should
find a better example to present to us.

On Wed, May 3, 2023 at 9:17 AM Dirschel, Steve <
steve.dirschel@thomsonreuters.com> wrote:



>  Index Cond: ((workflow_id = 1070) AND ((status)::text = ANY
> ('{NOT_STARTED,PAUSED,PENDING,RUNNING}'::text[])))
>
  Buffers: shared hit=33
>

For this usage, the =ANY is a "boundary condition".  It re-descends the
index for each value in the array, and each of those re-descents incurs
buffer accesses.  They cost very little, as the planner thinks they will
mostly be cached already (and indeed, they are), but the difference still
shows up in the Buffers tabulation.

   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)

This is a bit of speculation on my part, as nothing in the plan output
distinguishes boundary condition usages from in-index-filter usages.  This
speculation is based on the fact that I was recently investigating a
similar situation and did extensive work on it with a debugger and by
adding new experimental log messages.  Also, It isn't clear to me why it
chooses one usage for one plan and the other usage for the other one in
your case, as it seems that both would be eligible for the "boundary
condition" treatment.  But presumably for some reason invisible to us it
just thinks one approach is faster for one index and the other approach for
the other index.


One other thing to note-  when using the partial index the cost is .43 ..
> 15824.82.  When using the other index the cost is .56 .. 15820.19.  So the
> lower end cost (I believe the cost to find the first row) is slightly lower
> for the partial index but the higher end cost (I believe to find the last
> row) is higher for the partial index.  Since there is no LIMIT clause why
> wouldn’t the optimizer use the lowest cost to find all rows (which in this
> case would be to use the non-partial index)?
>

There is some code in the planner which deems plans to be tied if their
costs are within a small difference (1%, I think it is).  This allows some
branches of the tree of all possible plans to be pruned off early, which
can save a lot of time in planning.

Cheers,

Jeff

>

^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* RE: [EXT] Re: Why using a partial index is doing slightly more logical I/O than a normal index
@ 2023-05-03 18:00  Dirschel, Steve <steve.dirschel@thomsonreuters.com>
  parent: Jeff Janes <jeff.janes@gmail.com>
  0 siblings, 2 replies; 5+ messages in thread

From: Dirschel, Steve @ 2023-05-03 18:00 UTC (permalink / raw)
  To: Jeff Janes <jeff.janes@gmail.com>; +Cc: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>

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?

Thanks again for your reply.  It is helpful.
Steve


^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: [EXT] Re: Why using a partial index is doing slightly more logical I/O than a normal index
@ 2023-05-03 19:00  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Dirschel, Steve <steve.dirschel@thomsonreuters.com>
  1 sibling, 0 replies; 5+ messages in thread

From: Tom Lane @ 2023-05-03 19:00 UTC (permalink / raw)
  To: Dirschel, Steve <steve.dirschel@thomsonreuters.com>; +Cc: Jeff Janes <jeff.janes@gmail.com>; pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>

"Dirschel, Steve" <steve.dirschel@thomsonreuters.com> writes:
> 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?

I don't believe the intelligence Jeff is postulating actually exists.
I see only one code path for this in nbtree.c, and what it's doing is
what he called the "boundary condition" implementation.  That is, it runs
one index search for "workflow_id = 1070 AND status = 'NOT_STARTED'",
then one for "workflow_id = 1070 AND status = 'PAUSED'", etc,
re-descending the index tree at the start of each of those four scans.

I'm not inclined to ascribe any great significance to the varying numbers
of buffer hits you observed.  I think that's likely explained by chance
layout of the two indexes' contents, so that some of these searches cross
different numbers of index pages even though they visit the same number of
index entries overall.  In particular, it doesn't look like the partial
index is buying anything for this specific test case.  The index's
constraint on "status" matters not at all, because in neither index will
we ever visit any regions of the index where other values of "status"
appear (save the one following entry where we detect that the status value
no longer matches in each scan; but it doesn't really matter what that
entry is).  The constraint on "deleted_millis" could help, but your second
EXPLAIN result shows that it didn't actually eliminate any rows:

>> Index Scan using test_workflow_execution_initial_ui_tabs on workflow_execution_test  (cost=0.56..15820.19 rows=4335 width=1309) (actual time=0.049..0.106 rows=56 loops=1)
>>    Index Cond: ((workflow_id = 1070) AND ((status)::text = ANY ('{NOT_STARTED,PAUSED,PENDING,RUNNING}'::text[])))
>>    Filter: (deleted_millis <= 0)

(note the lack of any "Rows Removed by Filter" line).  We can therefore
conclude that the index regions satisfying the workflow_id+status
conditions had no entries with deleted_millis <= 0 either.  So these
two test cases visited exactly the same number of index entries, and
any differences in "buffers hit" had to be chance layout effects, or
possibly the result of different index entry sizes.  How large is
that "result" column in reality?

			regards, tom lane





^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: [EXT] Re: Why using a partial index is doing slightly more logical I/O than a normal index
@ 2023-05-04 00:20  Jeff Janes <jeff.janes@gmail.com>
  parent: Dirschel, Steve <steve.dirschel@thomsonreuters.com>
  1 sibling, 0 replies; 5+ messages in thread

From: Jeff Janes @ 2023-05-04 00:20 UTC (permalink / raw)
  To: Dirschel, Steve <steve.dirschel@thomsonreuters.com>; +Cc: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>

On Wed, May 3, 2023 at 2:00 PM Dirschel, Steve <
steve.dirschel@thomsonreuters.com> wrote:

> 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?
>

Yes.  There is no way that that many index tuples will fit in just 24 index
leaf pages, so I think it must be using the re-descending method for both
plans.  Then I don't know why there is a difference in the number of
buffer accesses.  But the difference seems trivial, so I wouldn't put much
effort into investigating it.



> 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?
>

The speculation was that this applied to your query.  But going back to
some of my original tests, I see that I remembered some of the details
wrong on the broader topic as well.  When it voluntarily doesn't use the
=ANY as a boundary condition, that shows up in the plan as having the
condition evicted from "Index Cond" line and instead show up in a "Filter"
line, at least in my one test case (which means it is no longer an in-index
filter, as it jumps to the table and verifies visibility before applying
the filter).  So the thing that the plans really don't distinguish is
between when it just chooses not to use the extra index column for cost
reasons, from when it thinks it is unable to use it for correctness/safety
reasons.

Cheers,

Jeff

^ permalink  raw  reply  [nested|flat] 5+ messages in thread


end of thread, other threads:[~2023-05-04 00:20 UTC | newest]

Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-05-03 13:16 Why using a partial index is doing slightly more logical I/O than a normal index Dirschel, Steve <steve.dirschel@thomsonreuters.com>
2023-05-03 17:37 ` Jeff Janes <jeff.janes@gmail.com>
2023-05-03 18:00   ` Dirschel, Steve <steve.dirschel@thomsonreuters.com>
2023-05-03 19:00     ` Tom Lane <tgl@sss.pgh.pa.us>
2023-05-04 00:20     ` Jeff Janes <jeff.janes@gmail.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox