Feedback-ID: i374947ac:Fastmail
Content-Type: multipart/alternative;
 boundary="------------7hW9JqQm38oIuDUsUzOZEE8R"
Message-ID: <f1b40028-4bcb-4059-9f7b-975da3c3c4e8@quadratum-braccas.com>
Date: Thu, 24 Oct 2024 14:48:36 -0600
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: Partitioned table scanning all pertitions when the where clause
 specifies the partition key
To: Keith Fiske <keith.fiske@crunchydata.com>
Cc: "pgsql-performance@lists.postgresql.org"
 <pgsql-admin@lists.postgresql.org>
References: <31a09bff-78df-468b-8783-4bb5ebc8c0c9@quadratum-braccas.com>
 <CAODZiv4jfNWNwQR8hwsVRrwD95i_cGDvAt9=BmzzaB2u2thygA@mail.gmail.com>
Content-Language: en-US
From: Sbob <sbob@quadratum-braccas.com>
In-Reply-To: 
 <CAODZiv4jfNWNwQR8hwsVRrwD95i_cGDvAt9=BmzzaB2u2thygA@mail.gmail.com>
Archived-At: 
 <https://www.postgresql.org/message-id/f1b40028-4bcb-4059-9f7b-975da3c3c4e8%40quadratum-braccas.com>
Precedence: bulk

This is a multi-part message in MIME format.
--------------7hW9JqQm38oIuDUsUzOZEE8R
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit


On 10/24/24 11:16 AM, Keith Fiske wrote:
>
>
> On Thu, Oct 24, 2024 at 12:53 PM 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 = '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
--------------7hW9JqQm38oIuDUsUzOZEE8R
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 8bit

<!DOCTYPE html>
<html>
  <head>
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
  </head>
  <body>
    <p><br>
    </p>
    <div class="moz-cite-prefix">On 10/24/24 11:16 AM, Keith Fiske
      wrote:<br>
    </div>
    <blockquote type="cite"
cite="mid:CAODZiv4jfNWNwQR8hwsVRrwD95i_cGDvAt9=BmzzaB2u2thygA@mail.gmail.com">
      <meta http-equiv="content-type" content="text/html; charset=UTF-8">
      <div dir="ltr">
        <div dir="ltr"><br>
        </div>
        <br>
        <div class="gmail_quote">
          <div dir="ltr" class="gmail_attr">On Thu, Oct 24, 2024 at
            12:53 PM Sbob &lt;<a
              href="mailto:sbob@quadratum-braccas.com"
              moz-do-not-send="true" class="moz-txt-link-freetext">sbob@quadratum-braccas.com</a>&gt;
            wrote:<br>
          </div>
          <blockquote class="gmail_quote"
style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">All;<br>
            <br>
            <br>
            I created a table that includes a lastname column that is a
            varchar(50) :<br>
            <br>
            <br>
            CREATE TABLE customers (<br>
            cust_id      bigint,<br>
            custinfo   text,<br>
            cust_dob   date,<br>
            lastname   varchar(50),<br>
            firstname varchar(50),<br>
            custaddr   varchar(200),<br>
            city            varchar(100),<br>
            state        varchar(100),<br>
            zipcode   varchar(5)<br>
            )  PARTITION BY LIST (lastname) ;<br>
            <br>
            <br>
            Then we created 4,000 partitions with each partition hosting
            a specific <br>
            set of lastname's (each partition has 100 lastname'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 ('SMITH', 'JONES','REX', 'ROBINSON', 'ROBINSON
            JR' ... )<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 = 'BORRIS';<br>
            <br>
            <br>
            The explain plan does an index scan on each partition even
            though <br>
            constraint_exclusion os set to "partition"<br>
            <br>
            <br>
            Thanks in advance for any advice<br>
            <br>
            <br>
            <br>
            <br>
            <br>
          </blockquote>
        </div>
        <br clear="all">
        <div>Since your index is functional, in order to use the index,
          the queries must use that same function in the condition. Try
          doing <br>
          <br>
          EXPLAIN SELECT * FROM customers where lastname =
          upper('BORRIS');<br>
        </div>
        <div><br>
        </div>
        <div>or this should give the same result</div>
        <div><br>
        </div>
        <div>EXPLAIN SELECT * FROM customers where lastname =
          upper('borris');<br>
        </div>
        <div><br>
        </div>
      </div>
    </blockquote>
    <p><br>
    </p>
    <p>same results</p>
    <p><br>
    </p>
    <p><br>
    </p>
    <blockquote type="cite"
cite="mid:CAODZiv4jfNWNwQR8hwsVRrwD95i_cGDvAt9=BmzzaB2u2thygA@mail.gmail.com">
      <div dir="ltr"><span class="gmail_signature_prefix">-- </span><br>
        <div dir="ltr" class="gmail_signature">
          <div dir="ltr">
            <div>
              <div dir="ltr">Keith Fiske<br>
                Senior Database Engineer<br>
                Crunchy Data - <a href="http://crunchydata.com"
                  target="_blank" moz-do-not-send="true"
                  class="moz-txt-link-freetext">http://crunchydata.com</a></div>
            </div>
          </div>
        </div>
      </div>
    </blockquote>
  </body>
</html>

--------------7hW9JqQm38oIuDUsUzOZEE8R--