Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1vHKSe-003xY9-Mo for pgsql-performance@arkaria.postgresql.org; Fri, 07 Nov 2025 11:16:56 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1vHKSc-00Dljp-BM for pgsql-performance@arkaria.postgresql.org; Fri, 07 Nov 2025 11:16:54 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1vHKSb-00Dljh-Uj for pgsql-performance@lists.postgresql.org; Fri, 07 Nov 2025 11:16:54 +0000 Received: from mail-ej1-x629.google.com ([2a00:1450:4864:20::629]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1vHKSZ-006Ty2-1j for pgsql-performance@lists.postgresql.org; Fri, 07 Nov 2025 11:16:53 +0000 Received: by mail-ej1-x629.google.com with SMTP id a640c23a62f3a-b4736e043f9so84487266b.0 for ; Fri, 07 Nov 2025 03:16:50 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=pgmustard.com; s=google; t=1762514210; x=1763119010; darn=lists.postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=0IpOjI7x9BsVLhDrP3qSA04sz9R78rmIIWtWvKOoBSY=; b=A4Gl1hZkl0zxT+UcuVTdLcFo9zvOgkRtDHqMj0awP8dCLczgKjUwTIzA0KUObRWPGJ xinK2f41Lx6KfszWYy91LqnWXmWgLhXHSYyIeiGCfIt0J2axjAdd1d5e+OIP7X3LuSye l1ljvKT/wxPZvT+0+toaaN8/qoAxSaHEq1R3aimFUVcdsMyHo2lf1VGNW32fNLpTKsZh Q0pdvbQD3yC5NRcBlQIvua0RvMae2QYx3wSn2ObPOx0PanV6zcqj6X6VligJ6A45FVhX CWTcQKeDuHC3bhlM2KErqWSmbPn64hFI3aSp+taVcbFegrTJqC2GaLF9QGZtJWPyQfEd uYAQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1762514210; x=1763119010; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-gg:x-gm-message-state:from:to:cc:subject:date :message-id:reply-to; bh=0IpOjI7x9BsVLhDrP3qSA04sz9R78rmIIWtWvKOoBSY=; b=qW5WotERAc/kHJTLhQUWw+1TZkdfvngLXMl2uD+Z9iXGPXB0OCdNuOvhqzHW6LkhyX Re0wCReRKgEspRILpscrmnhjrtp4iINU8wsvNsC14U21OMGQZ64u9+AMMoOctJMd8KT8 a5p+5h5eUB2/KrsmRkBCRAdjPcJHCvaHKOBwovAczKw3EOqZ2MFmKX1iciBdhMPfnDxV 3piCR1HR4t8tSLaozhioFZoLSF1/pe7twck3gJwjERiyjiWqNQLNdxAvd73dRzh9VLXw QTlW0r58R3x97U4haTf0nyf+3zQiYsD2cy91lMy3m5n0o/cHSvoYJWGmvONy0Xq4Xa4i GAhA== X-Gm-Message-State: AOJu0Yxn/Dufq7hokennaihIiqPVchw/LiPGqijuPbdut7BqNGhRTR62 j9ahji9g9Rz0JVTqniud47PUsQbhFImBQbtK7pUIz9txTQ2hlQdmHo5QY86xhahABWV+kQJ+SJr m1iMmcKVrty3OgdBPeUb/JyOJB2bYXxa+JLo4fOt62ULu6Fm7ttHAqApSWaA9alg= X-Gm-Gg: ASbGnctmQsnmYBeRDpBQk9Q+nAnXl8H2GpYZ53rNCFcAGwcjWBt6azpBDXaJMX7603p Ke+NVOtLY4dCIVw9wy3Evxk52oy21DJZN5nS75Iz0ZYs0fFxHOje0rD1ER052+r/IBfSBmrxIAE QDvrMsFLRAABOb4UOAuJTcM/VpSfMcKDW2RU3huo6KV+/euiLO0bv0zsTQ1EynzFCLev4rPyERP EiWmcgqVn+85fEofIRZIe2xhn+ZyhGoYReS2C+b369iY2ZZtnAZLHq+8HnO4P5dVR3DGFXD0te0 RZnm7m4= X-Google-Smtp-Source: AGHT+IFoR8FAk0WAkREbjcONL4/IPGbd68pGsLLaPal0F0pTcOaojJn+2xplWTLh7S8xmIgKX/1MbHSGpDzOgNAWJaM= X-Received: by 2002:a17:907:3daa:b0:b5f:c2f6:a172 with SMTP id a640c23a62f3a-b72c0ac2cbemr278437266b.30.1762514209906; Fri, 07 Nov 2025 03:16:49 -0800 (PST) MIME-Version: 1.0 References: In-Reply-To: From: Michael Christofides Date: Fri, 7 Nov 2025 11:16:34 +0000 X-Gm-Features: AWmQ_bnUGg_fx-a3PSJeuO5-EeVhxn6BJt3_UaFWUhxmPhxfqhhIamRyx2RK8MY Message-ID: Subject: Re: Index Searches higher than expected for skip scan To: Peter Geoghegan Cc: pgsql-performance@lists.postgresql.org Content-Type: multipart/alternative; boundary="00000000000064ad100642ff51fd" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --00000000000064ad100642ff51fd Content-Type: text/plain; charset="UTF-8" Thank you for the incredibly helpful (and fast) replies Peter. > Attached is its output when I run your test query. The issue here is that skip scan thinks that there are 4 distinct skip array values that it must use: 1. SK_BT_MINVAL 2. false 3. true 4. SK_ISNULL This output in particular really helped it make sense to me. > But if the column *was* nullable, adding IS NOT NULL would cut the number of index searches by 1. > Nice idea. Once it sunk in, I realised I could try the explicit "AND boolean_field IN (true, false)" and got it down to 2 index searches: EXPLAIN (ANALYZE, BUFFERS, VERBOSE, SETTINGS) SELECT boolean_field FROM example WHERE integer_field = 5432 AND boolean_field IN (true, false); QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------------- Index Only Scan using bool_int_idx on public.example (cost=0.29..8.79 rows=10 width=1) (actual time=0.060..0.077 rows=12.00 loops=1) Output: boolean_field Index Cond: ((example.boolean_field = ANY ('{t,f}'::boolean[])) AND (example.integer_field = 5432)) Heap Fetches: 0 Index Searches: 2 Buffers: shared hit=5 Planning Time: 0.265 ms Execution Time: 0.115 ms Thanks again, Michael --00000000000064ad100642ff51fd Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Thank you for the incredibly helpful (and fas= t) replies Peter.=C2=A0<= /div>
=C2=A0
Attached is its output when I run yo= ur test query. The issue here is=C2=A0
that sk= ip scan thinks that there are 4 distinct skip array values that=C2=A0
it must use:=C2=A0
=C2= =A0
1. SK_BT_MINVAL=C2=A0
2. false=C2=A0
3. true=C2=A0
4. SK_ISNULL

This outp= ut in particular really helped it make sense to me.
=C2=A0
But if the column *was* nullable, adding IS NOT NULL wo= uld cut the=C2=A0
number of index searches by= 1.

Nice idea. Once it sunk in, I reali= sed I could try the explicit "AND boolean_field IN (true, false)"= and got it down to 2 index searches:

EXPLAIN (ANA= LYZE, BUFFERS, VERBOSE, SETTINGS)
SELECT boolean_field FROM example WHER= E integer_field =3D 5432 AND boolean_field IN (true, false);

= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 QUERY= PLAN =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 <= br>------------------------------------------------------------------------= ---------------------------------------------------------------
=C2=A0In= dex Only Scan using bool_int_idx on public.example =C2=A0(cost=3D0.29..8.79= rows=3D10 width=3D1) (actual time=3D0.060..0.077 rows=3D12.00 loops=3D1)=C2=A0 =C2=A0Output: boolean_field
=C2=A0 =C2=A0Index Cond: ((example.= boolean_field =3D ANY ('{t,f}'::boolean[])) AND (example.integer_fi= eld =3D 5432))
=C2=A0 =C2=A0Heap Fetches: 0
=C2=A0 =C2=A0Index Search= es: 2
=C2=A0 =C2=A0Buffers: shared hit=3D5
=C2=A0Planning Time: 0.265= ms
=C2=A0Execution Time: 0.115 ms

Thanks again= ,
Michael
--00000000000064ad100642ff51fd--