Message-ID: <09715bfd-53aa-4504-8400-355d3682158a@pscs.co.uk>
Date: Thu, 24 Oct 2024 18:28:55 +0100
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: Partitioned table scanning all pertitions when the where
 clause specifies the partition key
Content-Language: en-GB
To: pgsql-admin@lists.postgresql.org
References: <31a09bff-78df-468b-8783-4bb5ebc8c0c9@quadratum-braccas.com>
 <CAODZiv4jfNWNwQR8hwsVRrwD95i_cGDvAt9=BmzzaB2u2thygA@mail.gmail.com>
From: Paul Smith* <paul@pscs.co.uk>
In-Reply-To: 
 <CAODZiv4jfNWNwQR8hwsVRrwD95i_cGDvAt9=BmzzaB2u2thygA@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
Archived-At: 
 <https://www.postgresql.org/message-id/09715bfd-53aa-4504-8400-355d3682158a%40pscs.co.uk>
Precedence: bulk

On 24/10/2024 18:16, Keith Fiske wrote:
>
> Since your index is functional, in order to use the index, the queries 
> must use that same function in the condition. Try doing
>
> EXPLAIN SELECT * FROM customers where lastname = upper('BORRIS');

I thought you'd need to use the same function exactly.


EXPLAIN SELECT * FROM customers where upper(lastname) = upper('BORRIS');


As the index is on 'upper(lastname)', 'upper(lastname)' has to be in the 
query


Paul