MIME-Version: 1.0
References: <31a09bff-78df-468b-8783-4bb5ebc8c0c9@quadratum-braccas.com>
In-Reply-To: <31a09bff-78df-468b-8783-4bb5ebc8c0c9@quadratum-braccas.com>
From: Keith Fiske <keith.fiske@crunchydata.com>
Date: Thu, 24 Oct 2024 13:16:39 -0400
Message-ID: 
 <CAODZiv4jfNWNwQR8hwsVRrwD95i_cGDvAt9=BmzzaB2u2thygA@mail.gmail.com>
Subject: Re: Partitioned table scanning all pertitions when the where clause
 specifies the partition key
To: Sbob <sbob@quadratum-braccas.com>
Cc: "pgsql-performance@lists.postgresql.org"
 <pgsql-admin@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000f2c99d06253c2b5f"
Archived-At: 
 <https://www.postgresql.org/message-id/CAODZiv4jfNWNwQR8hwsVRrwD95i_cGDvAt9%3DBmzzaB2u2thygA%40mail.gmail.com>
Precedence: bulk

--000000000000f2c99d06253c2b5f
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Thu, Oct 24, 2024 at 12:53=E2=80=AFPM Sbob <sbob@quadratum-braccas.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 =3D '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 =3D upper('BORRIS');

or this should give the same result

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

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

--000000000000f2c99d06253c2b5f
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div dir=3D"ltr"><br></div><br><div class=3D"gmail_quote">=
<div dir=3D"ltr" class=3D"gmail_attr">On Thu, Oct 24, 2024 at 12:53=E2=80=
=AFPM Sbob &lt;<a href=3D"mailto:sbob@quadratum-braccas.com">sbob@quadratum=
-braccas.com</a>&gt; wrote:<br></div><blockquote class=3D"gmail_quote" styl=
e=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);paddin=
g-left:1ex">All;<br>
<br>
<br>
I created a table that includes a lastname column that is a varchar(50) :<b=
r>
<br>
<br>
CREATE TABLE customers (<br>
cust_id=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 bigint,<br>
custinfo=C2=A0=C2=A0 text,<br>
cust_dob=C2=A0=C2=A0 date,<br>
lastname=C2=A0=C2=A0 varchar(50),<br>
firstname varchar(50),<br>
custaddr=C2=A0=C2=A0 varchar(200),<br>
city=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 varc=
har(100),<br>
state=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 varchar(100),<br>
zipcode=C2=A0=C2=A0 varchar(5)<br>
)=C2=A0 PARTITION BY LIST (lastname) ;<br>
<br>
<br>
Then we created 4,000 partitions with each partition hosting a specific <br=
>
set of lastname&#39;s (each partition has 100 lastname&#39;s)<br>
<br>
The partitions are all based on UPPER(lastname) like this:<br>
<br>
<br>
CREATE TABLE cust_part1<br>
PARTITION OF customer<br>
FOR VALUES IN (&#39;SMITH&#39;, &#39;JONES&#39;,&#39;REX&#39;, &#39;ROBINSO=
N&#39;, &#39;ROBINSON JR&#39; ... )<br>
<br>
<br>
and all the partitions have been loaded up with all the data that lines <br=
>
up with the partition key (100 lastnames per partition)<br>
<br>
<br>
Each partition has an INDEX on upper(lastname)<br>
<br>
<br>
<br>
When I run something like :<br>
<br>
EXPLAIN SELECT * FROM customers where lastname =3D &#39;BORRIS&#39;;<br>
<br>
<br>
The explain plan does an index scan on each partition even though <br>
constraint_exclusion os set to &quot;partition&quot;<br>
<br>
<br>
Thanks in advance for any advice<br>
<br>
<br>
<br>
<br>
<br>
</blockquote></div><br clear=3D"all"><div>Since your index is functional, i=
n order to use the index, the queries must use that same function in the co=
ndition. Try doing=C2=A0<br><br>EXPLAIN SELECT * FROM customers where lastn=
ame =3D upper(&#39;BORRIS&#39;);<br></div><div><br></div><div>or this shoul=
d give the same result</div><div><br></div><div>EXPLAIN SELECT * FROM custo=
mers where lastname =3D upper(&#39;borris&#39;);<br></div><div><br></div><s=
pan class=3D"gmail_signature_prefix">-- </span><br><div dir=3D"ltr" class=
=3D"gmail_signature"><div dir=3D"ltr"><div><div dir=3D"ltr">Keith Fiske<br>=
Senior Database Engineer<br>Crunchy Data - <a href=3D"http://crunchydata.co=
m" target=3D"_blank">http://crunchydata.com</a></div></div></div></div></di=
v>

--000000000000f2c99d06253c2b5f--