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

On 10/24/24 11:16 AM, Keith Fiske wrote:
>
>
> On Thu, Oct 24, 2024 at 12:53 PM Sbob <sbob(at)quadratum-braccas(dot)com> 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