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 1u9OYY-001JiV-0A for pgsql-performance@arkaria.postgresql.org; Mon, 28 Apr 2025 13:29:58 +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 1u9OYV-000yPc-Vg for pgsql-performance@arkaria.postgresql.org; Mon, 28 Apr 2025 13:29:57 +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 1u9ORa-000tyD-PG for pgsql-performance@lists.postgresql.org; Mon, 28 Apr 2025 13:22:47 +0000 Received: from mail-yb1-xb2d.google.com ([2607:f8b0:4864:20::b2d]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1u9ORZ-0000WA-1j for pgsql-performance@lists.postgresql.org; Mon, 28 Apr 2025 13:22:47 +0000 Received: by mail-yb1-xb2d.google.com with SMTP id 3f1490d57ef6-e461015fbd4so3939045276.2 for ; Mon, 28 Apr 2025 06:22:44 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1745846563; x=1746451363; darn=lists.postgresql.org; h=to:subject:message-id:date:from:mime-version:from:to:cc:subject :date:message-id:reply-to; bh=x+1legGx+s0Ljpri0CUpafK1fkUONO41hqN/FjtENBE=; b=dx3xiLgJdqStjxABWmrq1CynmN0tjEUpEYvPB3RAE//QyPZS26n9v+eOwEVUpBjtmI DV7NaB+Jxp6X6sT3hqOQ0kMl627UfN0AECQ206UrfkTT+eJhkmNoKOCZ//kQU3cXt81C WxnwcTdsFVm48yCsPHTG9KSg+dXr7Cklz7hLcODLsMbcZBjfvvG+c9imsWx9yjdi/HFl SY+sv7PQExy66cgJKws2LzXEpwhk1NdTnjVuP28M+CQwmIr7K8BBX3elidvwrDRDMxpA R40M+Ohlwt+yJASdpiAn6n7TiKdUuoN7GvVZIAuq4i1jSDGkreTjccN23Al9inGQ3d1h 2wuQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1745846563; x=1746451363; h=to:subject:message-id:date:from:mime-version:x-gm-message-state :from:to:cc:subject:date:message-id:reply-to; bh=x+1legGx+s0Ljpri0CUpafK1fkUONO41hqN/FjtENBE=; b=m3PDCUKobJJ79d/+B4f3KGYhMUOJI6la/QmaoVsF7ahlmJCV01gx/bQvAKxRIk9Pw4 mTeQbu9QGKkx/APWTh2IUIL/Mz8PhSQz2E1jQLVy6pCbVorZ9o7nmKclqgiQ3tSq0/h1 vunlcvh1yWk4jZM7y8IzxKDdjOQ9pRmJkZk/X51PSOkDe/MbpbfQdtPU6zhum6yrE7LZ 7gLT85pCaDM/hgeX9lXCP9/0pWcFAKrAd0iMQJLRvZ5bJ2+uO2ZbmRxVAIW58lJdia79 8+DjO5H6RQnjio3WQvMxzaNTikunVeZJV4zRfACCUH16RykQpMbJlFs6pyMRtPeSSXHV fymw== X-Gm-Message-State: AOJu0YyaUYdJjVdVW5yFVH9decfNMh0IsBqHx22/QSWjP68M+N7RH71V UxpuxfTZPrPCx72ALtQDdSNz543iEqtrxdIRUx85YeBJpqEgx6wC5/MXFSZOlnNbWhhRqod53D3 Z6R8XHFjy6D2NUCM21v6Dns+cDE8/a5l9Wlk= X-Gm-Gg: ASbGncuSLZUpX12sIKpVg5n0KosFv4/Vp913UT7BAxmwNItw+Jgdxi9BJbT1r9nrmzP ZPHLpfFfyvmgVv3XJgVcabzCIm65fGslzU8zradITY1G9Bbt7X0VEVTZIMFD9rYDwIRi4h9h/TV siC4/1Pix1tAb42lWbp6aP X-Google-Smtp-Source: AGHT+IGECP9vqp3Nt2DNuUVxQQTuH7cVi54VL9qXuK9D+0eNzG2xthuLi4ml7uP3uT/VaGHPYCghseNdHE3UGh2pjcQ= X-Received: by 2002:a05:6902:2686:b0:e60:9d12:c1e5 with SMTP id 3f1490d57ef6-e732343bf7cmr10829894276.36.1745846563293; Mon, 28 Apr 2025 06:22:43 -0700 (PDT) MIME-Version: 1.0 From: =?UTF-8?Q?Felipe_L=C3=B3pez_Montes?= Date: Mon, 28 Apr 2025 15:22:32 +0200 X-Gm-Features: ATxdqUFpUu6DvWKO3u_9DzJnOnEPsZ4g57_WlVIhcvA7UozEhpGL--oJYhm1BUc Message-ID: Subject: PostgreSQL Choosing Full Index Over Partial Index To: pgsql-performance@lists.postgresql.org Content-Type: multipart/alternative; boundary="0000000000003cd15e0633d6943b" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000003cd15e0633d6943b Content-Type: text/plain; charset="UTF-8" Hi all, I am using PostgreSQL 17.4 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 12.4.0, 64-bit, and working with the postgres_air Database . I have a very simple query (please forget about the sense of the query itself, I just want to focus on the planner): *SELECT statusFROM postgres_air.flightWHERE status = 'Canceled';* And the following indexes: *CREATE INDEX flight_status_index ON flight(status)* *CREATE INDEX flight_canceled ON flight(status)WHERE status = 'Canceled'* Following the book PostgreSQL Query Optimization (Second Edition), there is a statement on page 90 talking about Partial Indexes that says that the planner will use the partial index rather than the full index on the flight table, however after doing my own tests I have checked that this is not true and the planner estimates that scanning the full index is cheaper than scanning the partial one and would like to understand why. I assume but might be wrong that having this partial index, lighter than the full table index, with both satisfying a specific index-suitable filter condition (in this case canceled flights represent 171 rows vs 683178 rows from the whole table), should be a reason for the planner to know that searching in the partial index should be faster than searching in the full index, besides the true fact that this partial index weights less than the full one. I also tried downgrading the version to the one used by the authors of the book but same behavior happens. Please see attached the different plan executions: *Plan for the full index:* QUERY PLAN Index Only Scan using flight_status_index on flight (cost=0.42..7.61 rows=182 width=11) (actual time=0.042..0.062 rows=171 loops=1) Index Cond: (status = 'Canceled'::text) Heap Fetches: 0 Planning Time: 0.173 ms Execution Time: 0.080 ms *Plan for the partial index:* QUERY PLAN Index Only Scan using flight_canceled on flight (cost=0.14..10.82 rows=182 width=11) (actual time=0.039..0.050 rows=171 loops=1) Heap Fetches: 0 Planning Time: 0.135 ms Execution Time: 0.066 ms Thanks in advance. --0000000000003cd15e0633d6943b Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Hi all,

I am using=C2=A0Po= stgreSQL=C2=A017.4 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 12.= 4.0, 64-bit, and working with the postgres_air Database.

I have a very simple query (p= lease forget about the sense of the query itself, I just want to focus on t= he planner):

SELECT status
FROM postgres_air.flight
WHERE s= tatus =3D 'Canceled';

And the following indexes:

= CREATE INDEX flight_status_index ON flight(status)

CREATE = INDEX flight_canceled ON flight(status)
WHERE status =3D 'Canceled&#= 39;


Following the book PostgreSQL Query Optimization (Second= Edition), there is a statement on page 90 talking about Partial Indexes th= at says that the planner will use the partial index rather than the full in= dex on the flight table, however after doing my own tests I have checked th= at this is not true and the planner estimates that scanning the full index = is cheaper than scanning the partial one and would like to understand why.<= br>
I assume but might be wrong that having this partial index, lighter = than the full table index, with both satisfying a specific index-suitable f= ilter condition (in this case canceled flights represent 171 rows vs 683178= rows from the whole table), should be a reason for the planner to know tha= t searching in the partial index should be faster than searching in the ful= l index, besides the true fact that this partial index weights less than th= e full one.

I also tried downgrading the version to the one used by = the authors of the book but same behavior happens.

Please see attach= ed the different plan executions:

Plan for the full ind= ex:

QUERY PLAN
Index Only Scan using flight_status_index on flight  (cost=3D0.42..7.61 rows=3D182 width=3D11) (actual time=3D0.042..0.062 rows=3D171 loops=3D1)
  Index Cond: (status =3D 'Canceled'::text)
  Heap Fetches: 0
Planning Time: 0.173 ms
Execution Time: 0.080 ms

Plan for the partial index:

QUERY PLAN
Index Only Scan using flight_canceled on flight  (cost=3D0.14.=
.10.82 rows=3D182 width=3D11) (actual time=3D0.039..0.050 rows=3D171 loops=3D1)
  Heap Fetches: 0
Planning Time: 0.135 ms
Execution Time: 0.066 ms



Thanks i= n advance.

--0000000000003cd15e0633d6943b--