public inbox for [email protected]
help / color / mirror / Atom feedPartitioned table scanning all pertitions when the where clause specifies the partition key
4+ messages / 3 participants
[nested] [flat]
* Partitioned table scanning all pertitions when the where clause specifies the partition key
@ 2024-10-24 16:53 Sbob <[email protected]>
0 siblings, 1 reply; 4+ messages in thread
From: Sbob @ 2024-10-24 16:53 UTC (permalink / raw)
To: [email protected] <[email protected]>
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Partitioned table scanning all pertitions when the where clause specifies the partition key
@ 2024-10-24 17:16 Keith Fiske <[email protected]>
parent: Sbob <[email protected]>
0 siblings, 2 replies; 4+ messages in thread
From: Keith Fiske @ 2024-10-24 17:16 UTC (permalink / raw)
To: Sbob <[email protected]>; +Cc: [email protected] <[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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Partitioned table scanning all pertitions when the where clause specifies the partition key
@ 2024-10-24 17:28 Paul Smith* <[email protected]>
parent: Keith Fiske <[email protected]>
1 sibling, 0 replies; 4+ messages in thread
From: Paul Smith* @ 2024-10-24 17:28 UTC (permalink / raw)
To: [email protected]
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Partitioned table scanning all pertitions when the where clause specifies the partition key
@ 2024-10-24 20:48 Sbob <[email protected]>
parent: Keith Fiske <[email protected]>
1 sibling, 0 replies; 4+ messages in thread
From: Sbob @ 2024-10-24 20:48 UTC (permalink / raw)
To: Keith Fiske <[email protected]>; +Cc: [email protected] <[email protected]>
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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2024-10-24 20:48 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-10-24 16:53 Partitioned table scanning all pertitions when the where clause specifies the partition key Sbob <[email protected]>
2024-10-24 17:16 ` Keith Fiske <[email protected]>
2024-10-24 17:28 ` Paul Smith* <[email protected]>
2024-10-24 20:48 ` Sbob <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox