0

I created my table as below:

CREATE TABLE IF NOT EXISTS public.customers (
    user_id varchar(255) NOT NULL,
    item_name varchar(255) NOT NULL,
    score int4 NULL,
    is_marked bool NULL
) PARTITION BY list(lower(right(user_id, 2)));

I then created partitions on user_id as combinations of 00, 01,....a0....zz.

I also have index on user_id column on parent as well as on the child partitions.

When I use a simple query as below, It is scanning all the partitions.

EXPLAIN ANALYZE SELECT * FROM customers where user_id = '1304257761';

Output:

"QUERY PLAN"
"Append  (cost=0.42..11205.43 rows=11539 width=876) (actual time=268.869..1013.606 rows=9365 loops=1)"
"  ->  Index Scan using customers_00_user_id_idx on customers_00  (cost=0.42..8.22 rows=6 width=701) (actual time=4.676..4.677 rows=0 loops=1)"
"        Index Cond: ((user_id)::text = '1304257761'::text)"
"  ->  Index Scan using customers_01_user_id_idx on customers_01  (cost=0.42..21.11 rows=18 width=684) (actual time=6.882..6.882 rows=0 loops=1)"
"        Index Cond: ((user_id)::text = '1304257761'::text)"
"  ->  Index Scan using customers_02_user_id_idx on customers_02  (cost=0.42..66.46 rows=64 width=513) (actual time=4.213..4.213 rows=0 loops=1)"
"        Index Cond: ((user_id)::text = '1304257761'::text)"
"  ->  Index Scan using customers_03_user_id_idx on customers_03  (cost=0.42..17.24 rows=18 width=631) (actual time=3.226..3.226 rows=0 loops=1)"
"        Index Cond: ((user_id)::text = '1304257761'::text)"
"  ->  Index Scan using customers_04_user_id_idx on customers_04  (cost=0.42..10.25 rows=8 width=697) (actual time=3.237..3.237 rows=0 loops=1)"
"        Index Cond: ((user_id)::text = '1304257761'::text)"
"  ->  Index Scan using customers_05_user_id_idx on customers_05  (cost=0.42..15.79 rows=13 width=696) (actual time=5.942..5.942 rows=0 loops=1)"
"        Index Cond: ((user_id)::text = '1304257761'::text)"
"  ->  Index Scan using customers_06_user_id_idx on customers_06  (cost=0.42..11.42 rows=9 width=700) (actual time=2.355..2.356 rows=0 loops=1)"
"        Index Cond: ((user_id)::text = '1304257761'::text)"
"  ->  Index Scan using customers_07_user_id_idx on customers_07  (cost=0.42..16.04 rows=13 width=708) (actual time=2.542..2.542 rows=0 loops=1)"
"        Index Cond: ((user_id)::text = '1304257761'::text)"
"  ->  Index Scan using customers_08_user_id_idx on customers_08  (cost=0.42..12.25 rows=10 width=698) (actual time=2.684..2.684 rows=0 loops=1)"
"        Index Cond: ((user_id)::text = '1304257761'::text)"
"  ->  Index Scan using customers_09_user_id_idx on customers_09  (cost=0.42..14.90 rows=12 width=705) (actual time=2.755..2.755 rows=0 loops=1)"
"        Index Cond: ((user_id)::text = '1304257761'::text)"
"  ->  Index Scan using customers_0a_user_id_idx on customers_0a  (cost=0.41..1.72 rows=1 width=505) (actual time=1.778..1.778 rows=0 loops=1)"
"        Index Cond: ((user_id)::text = '1304257761'::text)"
"  ->  Index Scan using customers_0b_user_id_idx on customers_0b  (cost=0.28..2.29 rows=1 width=486) (actual time=0.886..0.886 rows=0 loops=1)"
"        Index Cond: ((user_id)::text = '1304257761'::text)"
"  ->  Index Scan using customers_0c_user_id_idx on customers_0c  (cost=0.28..2.34 rows=1 width=631) (actual time=0.805..0.806 rows=0 loops=1)"
"        Index Cond: ((user_id)::text = '1304257761'::text)"
.....

My understanding is that it should only scan the customers_61 as passed user_id = '1304257761'

Improve this question

1 Answer 1

Reset to default
3

Since you partitioned the table on a function of user_id, the optimizer is not able to restrict the list of partitions before the actual execution of the plan. You need to explicitly add a WHERE condition matching the one you used in the definition of the partitions. Try this:

EXPLAIN ANALYZE SELECT * FROM customers 
where user_id = '1304257761' 
AND lower(right(user_id, 2))='61';
Improve this answer
6
  • Also works with just lower(user_id)
    – Andy DB Analyst
    Jun 30 at 10:43
  • @AndyDBAnalyst No, it won't. This is list partitioning, not range partitioning.
    – Laurenz Albe
    Jun 30 at 19:44
  • Can you provide more info or could please share a link on why it doesn't work? I tried the query you gave, it's working; scanning only one partition.
    – sujeet
    Jul 1 at 6:09
  • @sujeet Laurenz was replying to Andy that using just lower(user_id) won't work. Usino the exact expression you used in the definition of the partition lower(right(user_id, 2)) surely does work.
    – Andrea B.
    Jul 1 at 10:33
  • 1
    @sujeet Check here: postgresql.org/docs/current/… The key phrase is the planner will examine the definition of each partition and prove that the partition need not be scanned because it could not contain any rows meeting the query's WHERE clause. When the planner can prove this, it excludes (prunes) the partition from the query plan. "Proving" that a specific value of user_id maps to a specific value of a generic function chain like foo(bar(user_id)) is not easy for the query planner.
    – Andrea B.
    Jul 3 at 8:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.