Re: Partitioned table scanning all pertitions when the where clause specifies the partition key

From: Paul Smith* <paul(at)pscs(dot)co(dot)uk>
To: pgsql-admin(at)lists(dot)postgresql(dot)org
Subject: Re: Partitioned table scanning all pertitions when the where clause specifies the partition key
Date: 2024-10-24 17:28:55
Message-ID: 09715bfd-53aa-4504-8400-355d3682158a@pscs.co.uk
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-admin

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

In response to

Browse pgsql-admin by date

  From Date Subject
Next Message Sbob 2024-10-24 20:48:36 Re: Partitioned table scanning all pertitions when the where clause specifies the partition key
Previous Message Keith Fiske 2024-10-24 17:16:39 Re: Partitioned table scanning all pertitions when the where clause specifies the partition key