public inbox for [email protected]  

help / color / mirror / Atom feed

From: Sbob <[email protected]>
To: Keith Fiske <[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 14:48:36 -0600
Message-ID: <[email protected]> (raw)
In-Reply-To: <CAODZiv4jfNWNwQR8hwsVRrwD95i_cGDvAt9=BmzzaB2u2thygA@mail.gmail.com>
References: <[email protected]>
	<CAODZiv4jfNWNwQR8hwsVRrwD95i_cGDvAt9=BmzzaB2u2thygA@mail.gmail.com>


On 10/24/24 11:16 AM, Keith Fiske wrote:
>
>
> 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');
>

same results



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

view thread (4+ messages)

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: <[email protected]>

* 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