public inbox for [email protected]  

help / color / mirror / Atom feed

From: Keith Fiske <[email protected]>
To: Sbob <[email protected]>
Cc: [email protected] <[email protected]>
Subject: Re: Partitioned table scanning all pertitions when the where clause specifies the partition key
Date: Thu, 24 Oct 2024 13:16:39 -0400
Message-ID: <CAODZiv4jfNWNwQR8hwsVRrwD95i_cGDvAt9=BmzzaB2u2thygA@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>

On Thu, Oct 24, 2024 at 12:53 PM Sbob <[email protected]> wrote:

> All;
>
>
> I created a table that includes a lastname column that is a varchar(50) :
>
>
> CREATE TABLE customers (
> cust_id      bigint,
> custinfo   text,
> cust_dob   date,
> lastname   varchar(50),
> firstname varchar(50),
> custaddr   varchar(200),
> city            varchar(100),
> state        varchar(100),
> zipcode   varchar(5)
> )  PARTITION BY LIST (lastname) ;
>
>
> Then we created 4,000 partitions with each partition hosting a specific
> set of lastname's (each partition has 100 lastname's)
>
> The partitions are all based on UPPER(lastname) like this:
>
>
> CREATE TABLE cust_part1
> PARTITION OF customer
> FOR VALUES IN ('SMITH', 'JONES','REX', 'ROBINSON', 'ROBINSON JR' ... )
>
>
> and all the partitions have been loaded up with all the data that lines
> up with the partition key (100 lastnames per partition)
>
>
> Each partition has an INDEX on upper(lastname)
>
>
>
> When I run something like :
>
> EXPLAIN SELECT * FROM customers where lastname = 'BORRIS';
>
>
> The explain plan does an index scan on each partition even though
> constraint_exclusion os set to "partition"
>
>
> Thanks in advance for any advice
>
>
>
>
>
>
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');

or this should give the same result

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

-- 
Keith Fiske
Senior Database Engineer
Crunchy Data - http://crunchydata.com

view thread (4+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected]
  Subject: Re: Partitioned table scanning all pertitions when the where clause specifies the partition key
  In-Reply-To: <CAODZiv4jfNWNwQR8hwsVRrwD95i_cGDvAt9=BmzzaB2u2thygA@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox