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 1u9Q2j-001ZbH-JP for pgsql-performance@arkaria.postgresql.org; Mon, 28 Apr 2025 15:05:14 +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 1u9Q2h-001deq-Kv for pgsql-performance@arkaria.postgresql.org; Mon, 28 Apr 2025 15:05:12 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1u9PsQ-001Wlw-SR for pgsql-performance@lists.postgresql.org; Mon, 28 Apr 2025 14:54:36 +0000 Received: from mail-yb1-xb2f.google.com ([2607:f8b0:4864:20::b2f]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1u9PsP-0001Jh-35 for pgsql-performance@lists.postgresql.org; Mon, 28 Apr 2025 14:54:35 +0000 Received: by mail-yb1-xb2f.google.com with SMTP id 3f1490d57ef6-e6dea30465aso4052124276.1 for ; Mon, 28 Apr 2025 07:54:34 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1745852073; x=1746456873; darn=lists.postgresql.org; h=to:subject:message-id:date:from:in-reply-to:references:mime-version :from:to:cc:subject:date:message-id:reply-to; bh=Re30tEXQQTlBAKrSMiaK1C2y6KCIDchQVzQsBfT6IdI=; b=Mp67g6nLDCi8345w56Hy71IsfMrXuqDRmuXpBFJZxIxudgdGRz6bmhIAHbgEFiXsMr BMXFLlrow9wCj4vkQ1AIcOl2V0dhgC68ZAGTh6epRnvXsZlyTyBAltIrj3uXBd/FN6eD NL2lzxqehx9K4FUmfyro8SlMIO8gUL29xb0WsNDChQHCaQvIb2bpjjndgiW4B7u9DfNM EcF4AtFSBIoFGIL3DE4XcloH62LymB1lk0lNu4fkej8SfrLlFQF4FH2uRwxG1Bt0z1UC 1XXMDLWN1h92QL/uLK4hRq9Z5bOTllww3fZxgWrsMosWP6hTU/9wg+H3m4fy838BH2Jr bmCw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1745852073; x=1746456873; h=to:subject:message-id:date:from:in-reply-to:references:mime-version :x-gm-message-state:from:to:cc:subject:date:message-id:reply-to; bh=Re30tEXQQTlBAKrSMiaK1C2y6KCIDchQVzQsBfT6IdI=; b=iJnb/ZiklYm69WaDSU3F3IPJ06F2AV3bp7W0rNnEzi0QzNQ7vHYVkS50Jn3xoto0Fz GE16kjk+E/Rg0uhzAS6jK1v1blW/IyXcyFusXXvd+zlYnM7M/xyz2aHQg361RNLpKnCm gyDI18UcxlHcD1rx53S+8FA/L6SoE/z5ylP0XrQEP7a5nlT4tPEC9yBb9wEgy51UTugX qS+jYlwCzrYoNypkAHdLLXDJvH/Ssw/dyhcVojMHFA+0ZAMddhTzL7oLxnWQ1nSEcgvI B5kxvge39iZVVQh3kPPr8hxmJMVR1h/+/yVbwVhsfWmiGEWgkFrVmsBe4u+LGJGxWtN3 +VCQ== X-Forwarded-Encrypted: i=1; AJvYcCWIKHlrefPPlnkRfd8dmGUf7dofh8U3Ufwx3rR9ekbZf+5okAuv2aRZHTgWZUKKduuYRoVfEmQ+M21hPDUfwTGxdw==@lists.postgresql.org X-Gm-Message-State: AOJu0YyuQWss0wx5ujGxLFA5cgbtr14rKSoG4cZ7uouPqU+KIdslNK19 xe/NtLHjUUxyRCM8ejzpi0J83xOS/YljoD8Q2UEE/tBpuRaGSKgo+YwdQQyFdHOgVR9thbyNsBc 4cIdPkIJE4IX67yohUP0iWCE7MKw= X-Gm-Gg: ASbGncuDAh+FAJToxQ5jcumGP3vpB2o/x6K5S1OV+QXCRWToySG4wUF+ULcbLrnM2NY 0ELj9eeUCI3iJZMpvJEUzhUUs3X1dKRZLn52dAyFne1gzsiQkFSmrrVpg6HK3khndwSgAs9Sp19 ABEKcDH0ombaPLPQF7Dmyq X-Google-Smtp-Source: AGHT+IEVhqzG2gG4MSdTRnIQHjhgj+U/7AGgGexRFiwmBSiTyJY9JIdXofPGp1NHtNOUJyAZ3rVM6nsEgMuR/vpT7Kw= X-Received: by 2002:a05:6902:188f:b0:e73:2b04:589c with SMTP id 3f1490d57ef6-e732b04662cmr8512720276.7.1745852073287; Mon, 28 Apr 2025 07:54:33 -0700 (PDT) MIME-Version: 1.0 References: <79f3cb0ac1e221012df38d7baa72463662c37095.camel@cybertec.at> In-Reply-To: <79f3cb0ac1e221012df38d7baa72463662c37095.camel@cybertec.at> From: =?UTF-8?Q?Felipe_L=C3=B3pez_Montes?= Date: Mon, 28 Apr 2025 16:54:21 +0200 X-Gm-Features: ATxdqUHQtbROQvXxmwyIvhwv0RhiV2b9wH-kLG4wZoHMrg9ohYXncCuept4OULY Message-ID: Subject: Re: PostgreSQL Choosing Full Index Over Partial Index To: Laurenz Albe , pgsql-performance@lists.postgresql.org Content-Type: multipart/alternative; boundary="000000000000a8ab150633d7dc79" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000a8ab150633d7dc79 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Hi Mr. Laurenz, Thanks a lot for your response :). The full index is bigger as it has an entry for all the rows of the table, whilst the partial one only has entries for canceled flights. Output of pgstatindex() for the *partial index:* version,tree_level,index_size,root_block_no,internal_pages,leaf_pages,empty= _pages,deleted_pages,avg_leaf_density,leaf_fragmentation 4,0,16384,1,0,1,0,0,13.4,0 Output of pgstatindex() for the *full index:* version,tree_level,index_size,root_block_no,internal_pages,leaf_pages,empty= _pages,deleted_pages,avg_leaf_density,leaf_fragmentation 4,2,4825088,180,5,583,0,0,90.1,0 I already ran ANALYZE and also VACUUM ANALYZE, and it still goes for the full one. El lun, 28 abr 2025 a las 15:35, Laurenz Albe () escribi=C3=B3: > On Mon, 2025-04-28 at 15:22 +0200, Felipe L=C3=B3pez Montes wrote: > > 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 status > > FROM postgres_air.flight > > WHERE status =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' > > > > > > Following the book PostgreSQL Query Optimization (Second Edition), ther= e > 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 tha= n > 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=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 > > Which index is bigger (you can use \di+ in "psql")? > > Could you run the pgstatindex() function from the "pgstattuple" extension > on > both indexes and compare the output? > > Does ANALYZE on the table make a difference? > > Yours, > Laurenz Albe > --000000000000a8ab150633d7dc79 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Hi Mr. Laurenz,=C2=A0

Thanks a lot for your respons= e :).

The full index is bigger as it has an entry for all the rows o= f the table, whilst the partial one only has entries for canceled flights.<= br>
Output of pgstatindex() for the=C2=A0partial index:

ve= rsion,tree_level,index_size,root_block_no,internal_pages,leaf_pages,empty_p= ages,deleted_pages,avg_leaf_density,leaf_fragmentation
4,0,16384,1,0,1,0= ,0,13.4,0

Output of pgstatindex() for the=C2=A0full index:

version,tree_level,index_size,root_block_no,internal_pages,leaf_pages,= empty_pages,deleted_pages,avg_leaf_density,leaf_fragmentation
4,2,482508= 8,180,5,583,0,0,90.1,0


I already ran ANALYZE and also VACUUM ANA= LYZE, and it still goes for the full one.

El lun, 28 a= br 2025 a las 15:35, Laurenz Albe (<laurenz.albe@cybertec.at>) escribi=C3=B3:
On Mon, 2025-04-28 at 15:22 +0200,= Felipe L=C3=B3pez Montes wrote:
> I am using=C2=A0PostgreSQL=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 (please forget about the sense of the query= itself,
> I just want to focus on the planner):
>
> SELECT status
> FROM postgres_air.flight
> WHERE status =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'
>
>
> Following the book PostgreSQL Query Optimization (Second Edition), the= re 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 ta= ble,
> 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 scannin= g the
> partial one and would like to understand why.
>
> I assume but might be wrong that having this partial index, lighter th= an the
> full table index, with both satisfying a specific index-suitable filte= r
> 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=C2=A0 (cost=3D0.42= ..7.61 rows=3D182 width=3D11) (actual time=3D0.042..0.062 rows=3D171 loops= =3D1)
> =C2=A0=C2=A0Index Cond: (status =3D 'Canceled'::text)
> =C2=A0=C2=A0Heap 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=C2=A0 (cost=3D0.14..10= .82 rows=3D182 width=3D11) (actual time=3D0.039..0.050 rows=3D171 loops=3D1= )
> =C2=A0=C2=A0Heap Fetches: 0
> Planning Time: 0.135 ms
> Execution Time: 0.066 ms

Which index is bigger (you can use \di+ in "psql")?

Could you run the pgstatindex() function from the "pgstattuple" e= xtension on
both indexes and compare the output?

Does ANALYZE on the table make a difference?

Yours,
Laurenz Albe
--000000000000a8ab150633d7dc79--