Is there a Maximum number of partitions in which constraint_exclusion works?

From: Marcelo Vega <mavegaf(at)gmail(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: Is there a Maximum number of partitions in which constraint_exclusion works?
Date: 2013-10-08 15:31:03
Message-ID: CALi+mr4HWbsPzM5FZdXrSQR1SYmAFP6qp+ouF+jVVQXwmt0dsw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

I've been working with partitions (I have split my table in around 3000
partitions), and if i did some like this

SELECT *
FROM my_parent_table
WHERE some_value_in_which_partitioned_my_table in (34, 36, 48)

constraint_exclusion works, (looking EXPLAIN, I can see that in planner
only check the partition_table with CHECH id in 34, 36 and 48. Great!

But if I increase the amount of ids, constraint_exclusion don't work.

SELECT *
FROM my_parent_table
WHERE some_id in (34, 36, 48, 65, ... 234, 310) (in total, 101 different
ids)

When i do a query like that, EXPLAIN show me that ALL the partitioned
tables (3000) are checked (and, of course, the Query is too slow)

What is happening?

Browse pgsql-performance by date

  From Date Subject
Next Message Jeison Bedoya Delgado 2013-10-10 15:00:51 limit connections pgpool
Previous Message Luca Ferrari 2013-10-08 11:02:57 Re: [GENERAL] postgreSQL query via JDBC in different OS taking different running time?