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

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');

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