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 1oyaos-0008Kl-5C for pgsql-general@arkaria.postgresql.org; Fri, 25 Nov 2022 15:40:50 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1oyaoq-00016a-Ia for pgsql-general@arkaria.postgresql.org; Fri, 25 Nov 2022 15:40:48 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1oyaoq-00016K-7y for pgsql-general@lists.postgresql.org; Fri, 25 Nov 2022 15:40:48 +0000 Received: from sss.pgh.pa.us ([66.207.139.130]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1oyaoj-0005pi-IW for pgsql-general@lists.postgresql.org; Fri, 25 Nov 2022 15:40:47 +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 2APFeZqa4056384; Fri, 25 Nov 2022 10:40:35 -0500 From: Tom Lane To: =?UTF-8?B?QW5uYSBCLg==?= cc: pgsql-general@lists.postgresql.org Subject: Re: =?UTF-8?B?SW5kZXgtb25seSBzY2FuIG5vdCB3b3JraW5nIHdoZW4gSU4gY2xhdXNlIGhh?= =?UTF-8?B?cyAyIG9yIG1vcmUgdmFsdWVz?= In-reply-to: <1669301102.998685549@f535.i.mail.ru> References: <1669301102.998685549@f535.i.mail.ru> Comments: In-reply-to =?UTF-8?B?QW5uYSBCLg==?= message dated "Thu, 24 Nov 2022 17:45:03 +0300" MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-ID: <4056382.1669390835.1@sss.pgh.pa.us> Content-Transfer-Encoding: quoted-printable Date: Fri, 25 Nov 2022 10:40:35 -0500 Message-ID: <4056383.1669390835@sss.pgh.pa.us> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk =3D?UTF-8?B?QW5uYSBCLg=3D=3D?=3D writes: > create index "ix-transaction-client-trans_dttm-division" > on transaction (client_id, > trans_dttm desc, > division_code); > = > EXPLAIN (ANALYZE, BUFFERS) > select * > from transaction > where client_id =3D 123456 > and (trans_dttm between TO_DATE('01.01.2020', 'dd.mm.yyyy') and TO_DAT= E('31.12.2022', 'dd.mm.yyyy')) > and (division_code in > ('not_existing_code1', 'not_existing_code2')) > order by trans_dttm desc > limit 50 offset 0; The reason you get a plan like this: > " -> Index Scan using ""ix-transaction-client-trans_dttm-division"" on= transaction (cost=3D0.57..8350814.66 rows=3D28072 width=3D2675) (actual = time=3D703291.834..703291.835 rows=3D0 loops=3D1)" > " Index Cond: ((client_id =3D '123456'::numeric) AND (trans_dttm = >=3D to_date('01.01.2020'::text, 'dd.mm.yyyy'::text)) AND (trans_dttm <=3D= to_date('31.12.2022'::text, 'dd.mm.yyyy'::text)))" > " Filter: ((division_code)::text =3D ANY ('{not_existing_code1,no= t_existing_code2}'::text[]))" is that if the =3DANY clause were an index condition, it would result in multiple scans of the index, therefore the output would (in all probability) not be sorted in index order. To produce the demanded result, the plan would have to read the entire index scan and sort its output. The planner estimates that that would be slower than what it has done here. In practice it looks like you're reading the whole scan output anyway because there are less than 50 matching rows, but the planner didn't know that. The problem with =3DANY producing unordered output can be dodged if the =3DANY is on the first index column; but I suppose that does not help you here, since making division_code the first index column would defeat getting output that's sorted by trans_dttm anyway. You might try making extended stats on these three columns to see if that helps the planner to get a better rowcount estimate. If it understood that there were fewer than 50 matching rows, it might opt for the use-the-=3DANY-and-sort plan type. regards, tom lane