Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1puHhx-0001oC-9m for pgsql-general@arkaria.postgresql.org; Wed, 03 May 2023 19:00:09 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1puHhv-00068J-7q for pgsql-general@arkaria.postgresql.org; Wed, 03 May 2023 19:00:07 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1puHhu-000689-Sl for pgsql-general@lists.postgresql.org; Wed, 03 May 2023 19:00:06 +0000 Received: from sss.pgh.pa.us ([66.207.139.130]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1puHhr-000OZa-IQ for pgsql-general@lists.postgresql.org; Wed, 03 May 2023 19:00:06 +0000 Received: from sss1.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.pgh.pa.us (8.15.2/8.15.2) with ESMTP id 343J00nX3002224; Wed, 3 May 2023 15:00:01 -0400 From: Tom Lane To: "Dirschel, Steve" cc: Jeff Janes , "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 In-reply-to: References: <0eda9b7d13e34cc786d30c602510f3e4@lmco.com> Comments: In-reply-to "Dirschel, Steve" message dated "Wed, 03 May 2023 18:00:26 -0000" MIME-Version: 1.0 Content-Type: text/plain; charset="UTF-8" Content-ID: <3002222.1683140400.1@sss.pgh.pa.us> Content-Transfer-Encoding: quoted-printable Date: Wed, 03 May 2023 15:00:00 -0400 Message-ID: <3002223.1683140400@sss.pgh.pa.us> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk "Dirschel, Steve" writes: > There are 2,981,425 rows where workflow_id =3D 1070. Does that change y= our theory of using an =E2=80=9Cin-index filter=E2=80=9D for that plan? W= hen you say there was a bit of speculation on the =E2=80=9Cboundard condit= ion=E2=80=9D vs =E2=80=9Cin-index filter=E2=80=9D is the speculation on if= Postgres has 2 different ways of processing a =3DANY filter or is the spe= culation 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 =3D 1070 AND status =3D 'NOT_STARTED'", then one for "workflow_id =3D 1070 AND status =3D '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_ex= ecution_test (cost=3D0.56..15820.19 rows=3D4335 width=3D1309) (actual tim= e=3D0.049..0.106 rows=3D56 loops=3D1) >> Index Cond: ((workflow_id =3D 1070) AND ((status)::text =3D ANY ('{N= OT_STARTED,PAUSED,PENDING,RUNNING}'::text[]))) >> Filter: (deleted_millis <=3D 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 <=3D 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