From: Tom Lane <tgl@sss.pgh.pa.us>
To: Dirschel, Steve <steve.dirschel@thomsonreuters.com>
Cc: Jeff Janes <jeff.janes@gmail.com>
Cc: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
Subject: Re: [EXT] Re: Why using a partial index is doing slightly more logical I/O than a normal index
Date: Wed, 03 May 2023 15:00:00 -0400
Message-ID: <3002223.1683140400@sss.pgh.pa.us> (raw)
In-Reply-To: <DM6PR03MB4332404ECDC8900B09117A5FFA6C9@DM6PR03MB4332.namprd03.prod.outlook.com>
References: <0eda9b7d13e34cc786d30c602510f3e4@lmco.com>
<DM6PR03MB4332464839279C5AEA419526FA6C9@DM6PR03MB4332.namprd03.prod.outlook.com>
<CAMkU=1yJqBB3b4Xr51eUrWWMoJsN0d6G1+8AV+RbVnPZmFgB7A@mail.gmail.com>
<DM6PR03MB4332404ECDC8900B09117A5FFA6C9@DM6PR03MB4332.namprd03.prod.outlook.com>
"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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-general@postgresql.org
Cc: tgl@sss.pgh.pa.us, steve.dirschel@thomsonreuters.com, jeff.janes@gmail.com
Subject: Re: [EXT] Re: Why using a partial index is doing slightly more logical I/O than a normal index
In-Reply-To: <3002223.1683140400@sss.pgh.pa.us>
References: <0eda9b7d13e34cc786d30c602510f3e4@lmco.com> <DM6PR03MB4332464839279C5AEA419526FA6C9@DM6PR03MB4332.namprd03.prod.outlook.com> <CAMkU=1yJqBB3b4Xr51eUrWWMoJsN0d6G1+8AV+RbVnPZmFgB7A@mail.gmail.com> <DM6PR03MB4332404ECDC8900B09117A5FFA6C9@DM6PR03MB4332.namprd03.prod.outlook.com> <3002223.1683140400@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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