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 1tzliT-006P8X-FT for pgsql-general@arkaria.postgresql.org; Wed, 02 Apr 2025 00:12:25 +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 1tzliQ-008CWm-4j for pgsql-general@arkaria.postgresql.org; Wed, 02 Apr 2025 00:12:22 +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 1tzliP-008CWd-Mp for pgsql-general@lists.postgresql.org; Wed, 02 Apr 2025 00:12:21 +0000 Received: from mail-ej1-x644.google.com ([2a00:1450:4864:20::644]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1tzliN-002r0F-16 for pgsql-general@lists.postgresql.org; Wed, 02 Apr 2025 00:12:21 +0000 Received: by mail-ej1-x644.google.com with SMTP id a640c23a62f3a-ac29af3382dso972321066b.2 for ; Tue, 01 Apr 2025 17:12:18 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1743552738; x=1744157538; darn=lists.postgresql.org; h=to:subject:message-id:date:from:mime-version:from:to:cc:subject :date:message-id:reply-to; bh=jDnUWzf6UB93x0d1NlAiKM+JnQjfrrGrCvpC7/h3oTs=; b=kuuF2OMnVr0EVjBKmlzu01ikRuV9IhDhK3yvczylnQuwgA7wtmv8W3IQf1ZToq5Va7 ukvGrsO7eK77c98Ip331oDkl/KvZFTEkf04PwFeQnqfAyVF3oGnuAVuIqtSRWPvK9Ewd AkXmwIUYLk8ZwJTN8KYt0qV3cyC88g4D8Fci54zykzOHfFOc1uGoRCRnjdlQwOcnHXRu DFrpJG8PBGUstw3K0uj4TQVTVg3clIaiGZx1ZJnBN7AxFTGgY3nhpJE3YS1RlNcT6WMX lT9l3DsGKUKoqZ1I1NADk6MUrzF1TprZzY1gRGZkvlhSfubobiYg+FYaZlaOMhYbLpnC IwZw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1743552738; x=1744157538; h=to:subject:message-id:date:from:mime-version:x-gm-message-state :from:to:cc:subject:date:message-id:reply-to; bh=jDnUWzf6UB93x0d1NlAiKM+JnQjfrrGrCvpC7/h3oTs=; b=ZM0HtQY5LDwNrdQdbdq3gT4tf5r2rnMR2+I26bWpXur1Xo0jK69XL+wv+T1qcsZ2Qk gxJQWD2wn535pHqeEwLRaYAqSPzsmUlORKMdLl9jSW1JdInYhG74FgnJawlp737HljV/ 3QC8yBHo/D/RCn1gBI4nJyhWwhZ61k/Uk7L8B8O1PhS8FJ153AbSPfMy/mlZnwbbMgUr 7l77NwLkBMap5PWsh0IyLejITS6fmnYgVc4Bnmcz69uUvwgtbfqRiAM0OgME+vMf44dg 8AG/xjWyjpBlxPzPHDIWGShKtfF2nms8QJ5S/b4HrRT8iqwdLssK68zlvuiO0YGT+0P1 yx9w== X-Gm-Message-State: AOJu0YzPizUkIUiNMRNDWlrDhL0B6qKiD8/amQIum/b4I1bqWPgPdUrB ygZiTxTrA/wb2p/nsZmdJ5zdg3d7rehEb0ZD4UsjQOqriKW4mHWAttDoV8l6K97x+eZAk8Y942Y q/DiLuc53MaQ0OnzvvkNadekw9K906fq1nOzRPMpmRRz5tw== X-Gm-Gg: ASbGncsa0g6D4k7JQVt8KV3Nq4vrFFaXpkNQcmfgwk8U3DlIMJ4ORI/oOdznYHeOc0Y GcHUmvmK4hvw5yb9MYvkOZIMANy7DOuiAeqybhMrHPEQolsXi+ZgsNEPwW9Of5jtf2sxogU+6ql YUeev9caecyQsGOvYQ758xwr+j/hVLujvuFvg= X-Google-Smtp-Source: AGHT+IH0bU5PDpVLMCm+FNbTgYjynTs9tcCkvxB20CWHy14wVa5Gr9gYlV+MQ0nrJvxobWs/Kcq2NZu5yxrrZlHZJ4M= X-Received: by 2002:a17:907:868a:b0:ac3:bd68:24eb with SMTP id a640c23a62f3a-ac7389e793cmr1544863766b.1.1743552737628; Tue, 01 Apr 2025 17:12:17 -0700 (PDT) MIME-Version: 1.0 From: Manikandan Swaminathan Date: Tue, 1 Apr 2025 17:12:04 -0700 X-Gm-Features: AQ5f1Jo8waXgKsdfqhzWfZynlBu8wBlAuONexZDyLhRtHaKkHmHv74_xMJ808Vg Message-ID: Subject: Postgres Query Plan using wrong index To: pgsql-general@lists.postgresql.org Content-Type: multipart/alternative; boundary="00000000000092dd860631c08132" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --00000000000092dd860631c08132 Content-Type: text/plain; charset="UTF-8" Hello, I'm running on the docker postgres:17.0 image and trying to test out the behavior of adding a new index to a table. Specifically, I wanted to verify that my new index is actually used by looking at the output of "EXPLAIN ANALYZE". However, I found that my index is often not being used and wanted to see the rationale of the query planner when choosing the index. Reproduction steps postgres=# select version(); version --------------------------------------------------------------------------------------------------------------------------- PostgreSQL 17.0 (Debian 17.0-1.pgdg120+1) on aarch64-unknown-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit (1 row) 1. Create database CREATE DATABASE test LOCALE_PROVIDER icu ICU_LOCALE "en-US-x-icu" LOCALE "en_US.utf8" TEMPLATE template0; 2. Create table and indices CREATE TABLE test_table ( col_a int, col_b INT NOT NULL ); CREATE INDEX IF NOT EXISTS idx_col_a_btree ON test_table(col_b); CREATE INDEX IF NOT EXISTS idx_col_a_brin ON test_table USING brin (col_b); CREATE INDEX IF NOT EXISTS idx_col_b_a ON test_table(col_a, col_b); 3. Load 10 million rows into table DO $$ DECLARE batch_count INT := 0; b_var INT := 0; a_var INT := 1; prev_a INT := 1; a_null BOOLEAN := FALSE; batch_size INT := 1000; BEGIN FOR i IN 1..10000000 LOOP IF batch_count = batch_size THEN b_var := b_var + 1; a_null := NOT a_null; IF NOT a_null THEN a_var := prev_a + 1; ELSE prev_a := a_var; a_var := NULL; END IF; batch_count := 0; END IF; INSERT INTO test_table (col_a, col_b) VALUES (a_var, b_var); batch_count := batch_count + 1; END LOOP; END $$; 4. When running the following query, I would expect the index "idx_col_b_a" to be used: select min(col_b) from test_table where col_a > 4996. I have a range-based filter on col_a, and am aggregating the result with min(col_b). Both columns are covered by "idx_col_b_a". However, explain analyze indicates otherwise: postgres=# explain analyze select min(col_b) from test_table where col_a > 4996; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------ Result (cost=63.86..63.87 rows=1 width=4) (actual time=587.550..587.550 rows=1 loops=1) InitPlan 1 -> Limit (cost=0.43..63.86 rows=1 width=4) (actual time=587.542..587.543 rows=1 loops=1) -> Index Scan using idx_col_a_btree on test_table (cost=0.43..259400.27 rows=4090 width=4) (actual time=587.541..587.541 rows=1 loops=1) Filter: (col_a > 4996) Rows Removed by Filter: 9992000 Planning Time: 0.305 ms Execution Time: 587.579 ms (8 rows) Instead of using idx_col_b_a, it does an index scan on idx_col_a_btree. This is a problem because of the way how data is structured in my table. The higher col_a values are associated with higher col_b values. As a result, the index scan ends up having to scan through most of the index before finding the first record that matches the critieria "col_a > 4996". When I DROP the idx_col_a_btree index, the resulting query plan looks much better because it's using the correct index on col_b: postgres=# explain analyze select min(col_b) from test_table where col_a > 4996; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------ Aggregate (cost=102.23..102.24 rows=1 width=4) (actual time=0.591..0.592 rows=1 loops=1) -> Index Only Scan using idx_col_b_a on test_table (cost=0.43..92.01 rows=4090 width=4) (actual time=0.021..0.341 rows=4000 loops=1) Index Cond: (col_a > 4996) Heap Fetches: 0 Planning Time: 0.283 ms Execution Time: 0.613 ms (6 rows) I tried fiddling with the table statistics and the random_page_cost but neither seemed to make a difference. Is there some nuance here that I'm missing? Why is the query planner using an index that drastically worsens the performance of the query? --00000000000092dd860631c08132 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Hello,

I'm running on the docker postgres:17.0 = image and trying to test out the behavior of adding a new index to a table.= Specifically, I wanted to verify that my new index is actually used by loo= king at the output of "EXPLAIN ANALYZE". However, I found that my= index is often not being used and wanted to see the rationale of the query= planner when choosing the index.

Reproduction steps
postgres=3D#= select version();
=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 version =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=A0PostgreSQL 17.0 (Debia= n 17.0-1.pgdg120+1) on aarch64-unknown-linux-gnu, compiled by gcc (Debian 1= 2.2.0-14) 12.2.0, 64-bit
(1 row)

1. Create database

CREATE= DATABASE test LOCALE_PROVIDER icu ICU_LOCALE "en-US-x-icu" LOCAL= E "en_US.utf8" TEMPLATE template0;

2. Create table and ind= ices
CREATE TABLE test_table (
=C2=A0 =C2=A0 col_a int,
=C2=A0 =C2= =A0 col_b INT NOT NULL =C2=A0 =C2=A0
);
CREATE INDEX IF NOT EXISTS id= x_col_a_btree ON test_table(col_b);
CREATE INDEX IF NOT EXISTS idx_col_a= _brin ON test_table USING brin (col_b);
CREATE INDEX IF NOT EXISTS idx_c= ol_b_a ON test_table(col_a, col_b);

3. Load 10 million rows into tab= le

DO $$
DECLARE
=C2=A0 =C2=A0 batch_count INT :=3D 0;
=C2= =A0 =C2=A0 b_var INT :=3D 0;
=C2=A0 =C2=A0 a_var INT :=3D 1;
=C2=A0 = =C2=A0 prev_a INT :=3D 1;
=C2=A0 =C2=A0 a_null BOOLEAN :=3D FALSE;
= =C2=A0 =C2=A0 batch_size INT :=3D 1000;
BEGIN
=C2=A0 =C2=A0 FOR i IN = 1..10000000 LOOP
=C2=A0 =C2=A0 =C2=A0 =C2=A0 IF batch_count =3D batch_si= ze THEN
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 b_var :=3D b_var + 1;<= br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 a_null :=3D NOT a_null;
=C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 IF NOT a_null THEN
=C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 a_var :=3D prev_a + 1;
=C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 ELSE
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 prev_a :=3D a_var;
=C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 a_var :=3D NULL;
=C2=A0 =C2=A0 =C2=A0 =C2=A0= =C2=A0 =C2=A0 END IF;
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 batch_c= ount :=3D 0;
=C2=A0 =C2=A0 =C2=A0 =C2=A0 END IF;
=C2=A0 =C2=A0 =C2=A0= =C2=A0 INSERT INTO test_table (col_a, col_b) VALUES (a_var, b_var);
=C2= =A0 =C2=A0 =C2=A0 =C2=A0 batch_count :=3D batch_count + 1;
=C2=A0 =C2=A0= END LOOP;
END $$;

4. When running the following query, I would e= xpect the index "idx_col_b_a" to be used: select min(col_b) from = test_table =C2=A0where col_a > 4996.
I have a range-based filter on = col_a, and am aggregating the result with min(col_b). Both columns are cove= red by "idx_col_b_a". However, explain analyze indicates otherwis= e:

postgres=3D# explain analyze select min(col_b) from test_table = =C2=A0where col_a > 4996;
=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 =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 =C2=A0 =C2= =A0 =C2=A0 =C2=A0
------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------
=C2=A0Result =C2=A0(cost=3D63.86..63.87 rows=3D1 w= idth=3D4) (actual time=3D587.550..587.550 rows=3D1 loops=3D1)
=C2=A0 =C2= =A0InitPlan 1
=C2=A0 =C2=A0 =C2=A0-> =C2=A0Limit =C2=A0(cost=3D0.43..= 63.86 rows=3D1 width=3D4) (actual time=3D587.542..587.543 rows=3D1 loops=3D= 1)
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0-> =C2=A0Index Scan using= idx_col_a_btree on test_table =C2=A0(cost=3D0.43..259400.27 rows=3D4090 wi= dth=3D4) (actual time=3D587.541..587.541 rows=3D1 loops=3D1)
=C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Filter: (col_a > 499= 6)
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Rows Re= moved by Filter: 9992000
=C2=A0Planning Time: 0.305 ms
=C2=A0Executio= n Time: 587.579 ms
(8 rows)

Instead of using idx_col_b_a, it does= an index scan on idx_col_a_btree. This is a problem because of the way how= data is structured in my table. The higher col_a values are associated wit= h higher col_b values. As a result, the index scan ends up having to scan t= hrough most of the index before finding the first record that matches the c= ritieria "col_a > 4996".

When I DROP the idx_col_a_btre= e index, the resulting query plan looks much better because it's using = the correct index on col_b:
postgres=3D# explain analyze select min(col_= b) from test_table =C2=A0where col_a > 4996;
=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 =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 =C2=A0
-----= ---------------------------------------------------------------------------= ----------------------------------------------------------
=C2=A0Aggrega= te =C2=A0(cost=3D102.23..102.24 rows=3D1 width=3D4) (actual time=3D0.591..0= .592 rows=3D1 loops=3D1)
=C2=A0 =C2=A0-> =C2=A0Index Only Scan using = idx_col_b_a on test_table =C2=A0(cost=3D0.43..92.01 rows=3D4090 width=3D4) = (actual time=3D0.021..0.341 rows=3D4000 loops=3D1)
=C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0Index Cond: (col_a > 4996)
=C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0Heap Fetches: 0
=C2=A0Planning Time: 0.283 ms
=C2=A0Execution T= ime: 0.613 ms
(6 rows)

I tried fiddling with the table statistics= and the random_page_cost but neither seemed to make a difference. Is there= some nuance here that I'm missing? Why is the query planner using an i= ndex that drastically worsens the performance of the query?


--00000000000092dd860631c08132--