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 1vIHtc-002PbV-R2 for pgsql-performance@arkaria.postgresql.org; Mon, 10 Nov 2025 02:44:45 +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 1vIHta-00D8Tz-KP for pgsql-performance@arkaria.postgresql.org; Mon, 10 Nov 2025 02:44:42 +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 1vIHta-00D8Tq-7v for pgsql-performance@lists.postgresql.org; Mon, 10 Nov 2025 02:44:42 +0000 Received: from mail-lj1-f182.google.com ([209.85.208.182]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1vIHtX-006u7K-29 for pgsql-performance@lists.postgresql.org; Mon, 10 Nov 2025 02:44:41 +0000 Received: by mail-lj1-f182.google.com with SMTP id 38308e7fff4ca-37775ed97daso20711441fa.0 for ; Sun, 09 Nov 2025 18:44:39 -0800 (PST) X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1762742678; x=1763347478; h=to:subject:message-id:date:from:mime-version:x-gm-gg :x-gm-message-state:from:to:cc:subject:date:message-id:reply-to; bh=8yHUPTM5/zFtHazY8z91o5TbYHFczLWdPmQpFusK/Yw=; b=ZXHc8s6cMHnxPlvLrzW8HdH8s9idYjg+Waie8nwan7QkzwHR1Cj/Eptm/iit5CAIPT h5gZxTUryDPxugsPHthdSHYaL3QmVFMIcftPrxKUIKhHmHzr12ntwG9W5KHLsVoCDCfu hAVacaDbW4dcWiZ3r5cqvlcGTFjjW4OFkAa3lEKaODcJ1ui8w2Tu/hI/Ts7nCK8b/Lhu 5KlWDoJlIPVNsLut57rBtpUXz5a4p5oTmFfayl44+tyvL3vjI56AYHp+/PvWsZryYR3r WEB03sfn/x6n/MVFZLd0aFQyEa8Vvd1kJTeuZEAX8I0CnPJwElnlncfR93o+b5UpIQ1h hfPw== X-Gm-Message-State: AOJu0YzCLBG7bh3mR23BajoxnIxDfPv9E7WTcBio4iEPh5ij/zXp+y1r vSmsa37ldE0RYj6HtDU9xd/oxpUlr2N2mRnrh2kCw8XN49owH8FGQ7dkT2MSTg3iO9nNVOFtXBv GlDyRxhA5EXymVQd03a7JwYH8qOPhsFdXeWlY X-Gm-Gg: ASbGncugmDycz3EtqqyoEp6X+nkTQ0vrveU1YlUFJ6fEefVsvU5x50dAXMpyuvutAiS JKVlrN3B9dDhn7/QX+Yx8qMJc0AUiKJcrY5VCj5NTawVYQAH/CiShsTIjwTT1c9KmYaxeis0+oH ooBOuyoHt/ryMh8gfZrSOaNGyIHWpYPa33f5+yVc+ztRM1QrkUTpiJITopZrPqYdU9nSY/Q2Cak 4defKkXRP9ipkwaSSna7KVV9b8I+7Qdegc2Iup0Pg3mM+GsRdQfgYRcNxgb X-Google-Smtp-Source: AGHT+IE5H1sjlQ8cT3f2r6F20FaeMxQbV6aYx7R1yBgTBrIaRoECVZ/5B2un+nbiF1UVyNHXNbGfqm82pzNX9xENzTg= X-Received: by 2002:a05:6512:10cd:b0:594:3546:880b with SMTP id 2adb3069b0e04-594599dd638mr2165931e87.28.1762742677476; Sun, 09 Nov 2025 18:44:37 -0800 (PST) MIME-Version: 1.0 From: Vitalii Tymchyshyn Date: Sun, 9 Nov 2025 18:44:25 -0800 X-Gm-Features: AWmQ_bk0QqyrbX-oQJ4OBNiMPveN91IvnhtXzKTceUSOWt2QkLFkCXC6FWLbbIs Message-ID: Subject: Multicolumn index scan efficiency To: pgsql-performance@lists.postgresql.org Content-Type: multipart/alternative; boundary="0000000000001f197406433483cc" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000001f197406433483cc Content-Type: text/plain; charset="UTF-8" Hi. Since Friday I have been trying to diagnose the slowness of scanning the multicolumn index in Postgres. I figured out that multicolumns conditions like (a,b,c) > (x,y,z) with a,b and c being part of primary index work slow in postgresql 9.6 (original version in prod, I know it's old). I thought that it would be fast in 15 (one I had handy), but it was still slow. It was finally fast in 18. Note that all 3 are Google CloudSQL, but I believe those are pretty vanilla in terms of index scan internals. I am wondering about 2 things: 1) Does anyone know which specific change / version made it fast? 2) What was the proper way to do a range index scan like WHERE (a,b,c) between (x1,y1,z1) and (x2,y2,z2) before the improvement. Note that my tests can mostly be rewritten as equality at least for some columns (and this is what we'll do), but sometimes we do need a range scan like above, so understanding it would be important. Also I am curious :). The full test will be towards the end, but the query I started from is EXPLAIN (ANALYZE, BUFFERS, TIMING OFF) SELECT * FROM application_specs WHERE (namespace, application, version) > ('default', 'test_application_pipeline_with__simulated_long_name_075000', '') AND (namespace, application) <= ('default', 'test_application_pipeline_with__simulated_long_name_075000') AND (latest = true OR latest is NULL); Plans: --- 9.6 Index Scan using application_specs_pkey on application_specs (cost=0.56..6.33 rows=1 width=198) (actual rows=1 loops=1) Index Cond: ((ROW(namespace, application, version) > ROW('default'::text, 'test_application_pipeline_with__simulated_long_name_075000'::text, ''::text)) AND (ROW(namespace, application) <= ROW('default'::text, 'test_application_pipeline_with__simulated_long_name_075000'::text))) Filter: (latest OR (latest IS NULL)) Rows Removed by Filter: 29 Buffers: shared hit=35442 Planning time: 0.153 ms Execution time: 5049.123 ms (7 rows) --- 15 Index Scan using application_specs_pkey on application_specs (cost=0.56..6.33 rows=1 width=206) (actual rows=1 loops=1) Index Cond: ((ROW(namespace, application, version) > ROW('default'::text, 'test_application_pipeline_with__simulated_long_name_075000'::text, ''::text)) AND (ROW(namespace, application) <= ROW('default'::text, 'test_application_pipeline_with__simulated_long_name_075000'::text))) Filter: (latest OR (latest IS NULL)) Rows Removed by Filter: 29 Buffers: shared hit=45839 Planning Time: 0.171 ms Execution Time: 8190.116 ms (7 rows) --- 18 Index Scan using application_specs_pkey on application_specs (cost=0.56..6.33 rows=1 width=206) (actual rows=1.00 loops=1) Index Cond: ((ROW(namespace, application, version) > ROW('default'::text, 'test_application_pipeline_with__simulated_long_name_075000'::text, ''::text)) AND (ROW(namespace, application) <= ROW('default'::text, 'test_application_pipeline_with__simulated_long_name_075000'::text))) Filter: (latest OR (latest IS NULL)) Rows Removed by Filter: 29 Index Searches: 1 Buffers: shared hit=35 Planning: Buffers: shared hit=29 Planning Time: 0.243 ms Execution Time: 0.155 ms (10 rows) So, it's index scan in all cases, but for some reason index scan is very inefficient in the old versions and it has to scan a huge portion of index to find a few rows. --- Full test -- 0. DISABLE SEQSCAN to ensure index is used SET ENABLE_SEQSCAN=false; -- 1. SETUP: Clean slate DROP TABLE IF EXISTS application_specs; -- 2. SCHEMA CREATE TABLE application_specs ( namespace text NOT NULL, application text NOT NULL, version text NOT NULL, latest boolean, data text, PRIMARY KEY (namespace, application, version) WITH (fillfactor = 90) ); -- 3. GENERATE DATA: ~4.5 Million Rows Total -- 150,000 Applications * ~30 versions each INSERT INTO application_specs (namespace, application, version, latest, data) SELECT 'default', 'test_application_pipeline_with__simulated_long_name_' || lpad(a::text, 6, '0'), md5(random()::text || clock_timestamp()::text)::uuid::text, (v = 30), -- Make the 30th version the 'latest' one repeat('x', 100) FROM generate_series(1, 150000) a CROSS JOIN generate_series(1, 30) v; -- 4. ANALYZE to ensure planner has accurate stats VACUUM (ANALYZE, FREEZE) application_specs; -- ========================================= -- THE TEST -- Target a middle application: '...075000' -- ========================================= --------------------------------------------------- -- TEST A: Original Row-wise Query (Should be slow) --------------------------------------------------- EXPLAIN (ANALYZE, BUFFERS, TIMING OFF) SELECT * FROM application_specs WHERE (namespace, application, version) > ('default', 'test_application_pipeline_with__simulated_long_name_075000', '') AND (namespace, application) <= ('default', 'test_application_pipeline_with__simulated_long_name_075000') AND (latest = true OR latest is NULL); --------------------------------------------------- -- TEST B: Equality Query (Should be fast) --------------------------------------------------- EXPLAIN (ANALYZE, BUFFERS, TIMING OFF) SELECT * FROM application_specs WHERE namespace = 'default' AND application = 'test_application_pipeline_with__simulated_long_name_075000' AND version > '' AND (latest = true OR latest is NULL); --------------------------------------------------- -- TEST C: (Should be slow) --------------------------------------------------- EXPLAIN (ANALYZE, BUFFERS, TIMING OFF) SELECT * FROM application_specs WHERE (namespace, application) >= ('default', 'test_application_pipeline_with__simulated_long_name_075000') AND (namespace, application) <= ('default', 'test_application_pipeline_with__simulated_long_name_075000') AND (latest = true OR latest is NULL) AND version > ''; --------------------------------------------------- -- TEST D: (Should be fast) --------------------------------------------------- EXPLAIN (ANALYZE, BUFFERS, TIMING OFF) SELECT * FROM application_specs WHERE (namespace, application) = ('default', 'test_application_pipeline_with__simulated_long_name_075000') AND (latest = true OR latest is NULL) AND version > ''; --- End of test Best regards, Vitalii Tymchyshyn --0000000000001f197406433483cc Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Hi.

Since Friday I have been trying to = diagnose the slowness of scanning the multicolumn index in Postgres. I figu= red out that multicolumns=C2=A0conditions like (a,b,c) > (x,y,z) with a,= b and c being part of primary index work slow in postgresql 9.6 (original v= ersion in prod, I know it's old). I thought that it would be fast in 15= (one I had handy), but it was still slow. It was finally fast in 18. Note = that all 3 are Google CloudSQL, but I believe those are pretty vanilla in t= erms of index scan internals.

I am wondering about= 2 things:
1) Does anyone know which=C2=A0specific change / versi= on made it fast?
2) What was the proper way to do a range index s= can like WHERE (a,b,c) between (x1,y1,z1) and (x2,y2,z2) before the improve= ment.=C2=A0
Note that my tests can mostly be rewritten as equalit= y at least for some columns (and this is what we'll do), but sometimes = we do need a range scan like above, so understanding it would be important.= Also I am curious :).

The full test will be towar= ds the end, but the query I started from is=C2=A0
EXPLAIN (ANALYZ= E, BUFFERS, TIMING OFF)
SELECT * FROM application_specs
WHERE (namesp= ace, application, version) > ('default', 'test_application_p= ipeline_with__simulated_long_name_075000', '')
=C2=A0 AND (n= amespace, application) <=3D ('default', 'test_application_pi= peline_with__simulated_long_name_075000')
=C2=A0 AND (latest =3D tru= e OR latest is NULL);

Plans:
--- 9.6=
=C2=A0Index Scan using application_specs_pkey on application_spe= cs =C2=A0(cost=3D0.56..6.33 rows=3D1 width=3D198) (actual rows=3D1 loops=3D= 1)
=C2=A0 =C2=A0Index Cond: ((ROW(namespace, application, version) > = ROW('default'::text, 'test_application_pipeline_with__simulated= _long_name_075000'::text, ''::text)) AND (ROW(namespace, applic= ation) <=3D ROW('default'::text, 'test_application_pipeline_= with__simulated_long_name_075000'::text)))
=C2=A0 =C2=A0Filter: (lat= est OR (latest IS NULL))
=C2=A0 =C2=A0Rows Removed by Filter: 29
=C2= =A0 =C2=A0Buffers: shared hit=3D35442
=C2=A0Planning time: 0.153 ms
= =C2=A0Execution time: 5049.123 ms
(7 rows)
--- 15
= =C2=A0Index Scan using application_specs_pkey on application_specs =C2=A0(c= ost=3D0.56..6.33 rows=3D1 width=3D206) (actual rows=3D1 loops=3D1)
=C2= =A0 =C2=A0Index Cond: ((ROW(namespace, application, version) > ROW('= default'::text, 'test_application_pipeline_with__simulated_long_nam= e_075000'::text, ''::text)) AND (ROW(namespace, application) &l= t;=3D ROW('default'::text, 'test_application_pipeline_with__sim= ulated_long_name_075000'::text)))
=C2=A0 =C2=A0Filter: (latest OR (l= atest IS NULL))
=C2=A0 =C2=A0Rows Removed by Filter: 29
=C2=A0 =C2=A0= Buffers: shared hit=3D45839
=C2=A0Planning Time: 0.171 ms
=C2=A0Execu= tion Time: 8190.116 ms
(7 rows)

--- 18
=C2=A0Index Scan using application_specs_pkey on application_specs =C2=A0= (cost=3D0.56..6.33 rows=3D1 width=3D206) (actual rows=3D1.00 loops=3D1)
= =C2=A0 =C2=A0Index Cond: ((ROW(namespace, application, version) > ROW(&#= 39;default'::text, 'test_application_pipeline_with__simulated_long_= name_075000'::text, ''::text)) AND (ROW(namespace, application)= <=3D ROW('default'::text, 'test_application_pipeline_with__= simulated_long_name_075000'::text)))
=C2=A0 =C2=A0Filter: (latest OR= (latest IS NULL))
=C2=A0 =C2=A0Rows Removed by Filter: 29
=C2=A0 =C2= =A0Index Searches: 1
=C2=A0 =C2=A0Buffers: shared hit=3D35
=C2=A0Plan= ning:
=C2=A0 =C2=A0Buffers: shared hit=3D29
=C2=A0Planning Time: 0.24= 3 ms
=C2=A0Execution Time: 0.155 ms
(10 rows)

So, it's index scan in all cases, but for some reason index scan is v= ery inefficient in the old versions and it has to scan a huge portion of in= dex to find a few rows.

--- Full test
-- 0. DISABLE SEQSCAN to ensure index is used
SET ENABLE_SEQSCAN=3Dfal= se;

-- 1. SETUP: Clean slate
DROP TABLE IF EXISTS application_spe= cs;

-- 2. SCHEMA
CREATE TABLE application_specs (
=C2=A0 =C2= =A0 namespace text NOT NULL,
=C2=A0 =C2=A0 application text NOT NULL,=C2=A0 =C2=A0 version text NOT NULL,
=C2=A0 =C2=A0 latest boolean,
= =C2=A0 =C2=A0 data text,
=C2=A0 =C2=A0 PRIMARY KEY (namespace, applicati= on, version) WITH (fillfactor =3D 90)
);

-- 3. GENERATE DATA: ~4.= 5 Million Rows Total
-- 150,000 Applications * ~30 versions each
INSE= RT INTO application_specs (namespace, application, version, latest, data)SELECT
=C2=A0 =C2=A0 'default',
=C2=A0 =C2=A0 'test_app= lication_pipeline_with__simulated_long_name_' || lpad(a::text, 6, '= 0'),
=C2=A0 =C2=A0 md5(random()::text || clock_timestamp()::text)::u= uid::text,
=C2=A0 =C2=A0 (v =3D 30), -- Make the 30th version the 'l= atest' one
=C2=A0 =C2=A0 repeat('x', 100)
FROM generate_s= eries(1, 150000) a
CROSS JOIN generate_series(1, 30) v;

-- 4. ANA= LYZE to ensure planner has accurate stats
VACUUM (ANALYZE, FREEZE) appli= cation_specs;

-- =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D-- THE TEST
-- Target a middle application: '...075000'
-- = =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D

-------------------= --------------------------------
-- TEST A: Original Row-wise Query (Sho= uld be slow)
---------------------------------------------------
EXPL= AIN (ANALYZE, BUFFERS, TIMING OFF)
SELECT * FROM application_specs
WH= ERE (namespace, application, version) > ('default', 'test_ap= plication_pipeline_with__simulated_long_name_075000', '')
= =C2=A0 AND (namespace, application) <=3D ('default', 'test_a= pplication_pipeline_with__simulated_long_name_075000')
=C2=A0 AND (l= atest =3D true OR latest is NULL);

---------------------------------= ------------------
-- TEST B: Equality Query (Should be fast)
-------= --------------------------------------------
EXPLAIN (ANALYZE, BUFFERS, = TIMING OFF)
SELECT * FROM application_specs
WHERE namespace =3D '= default'
=C2=A0 AND application =3D 'test_application_pipeline_w= ith__simulated_long_name_075000'
=C2=A0 AND version > ''<= br>=C2=A0 AND (latest =3D true OR latest is NULL);

-----------------= ----------------------------------
-- TEST C: (Should be slow)
------= ---------------------------------------------
EXPLAIN (ANALYZE, BUFFERS,= TIMING OFF)
SELECT * FROM application_specs
WHERE (namespace, applic= ation) >=3D ('default', 'test_application_pipeline_with__sim= ulated_long_name_075000')
=C2=A0 AND (namespace, application) <= =3D ('default', 'test_application_pipeline_with__simulated_long= _name_075000')
=C2=A0 AND (latest =3D true OR latest is NULL) AND ve= rsion > '';

---------------------------------------------= ------
-- TEST D: (Should be fast)
----------------------------------= -----------------
EXPLAIN (ANALYZE, BUFFERS, TIMING OFF)
SELECT * FRO= M application_specs
WHERE (namespace, application) =3D ('default'= ;, 'test_application_pipeline_with__simulated_long_name_075000')=C2=A0 AND (latest =3D true OR latest is NULL) AND version > ''= ;

--- End of test

Bes= t regards, Vitalii Tymchyshyn

--0000000000001f197406433483cc--