1

I have a big table partition in PostgreSQL by disjoint conditions.

Something like:

CREATE TABLE child_table_1(check(my_condition = '01')) INHERITS (parent_table);
// ...
CREATE TABLE child_table_20(check(my_condition = '20')) INHERITS (parent_table);

When I do a query like:

EXPLAIN SELECT * FROM parent_table WHERE my_condition = '12' or my_condition = '14';

then the query planner works as expected and shows:

Append  (cost=0.00..21424.65 rows=100 width=60)
  ->  Seq Scan on parent_table  (cost=0.00..0.00 rows=1 width=30)
        Filter: ((my_condition = '12'::bpchar) OR (my_condition= '14'::bpchar))
  ->  Seq Scan on child_table_12  (cost=0.00..14790.10 rows=50 width=20)
        Filter: ((my_condition = '12'::bpchar) OR (my_condition = '14'::bpchar))
  ->  Seq Scan on child_table_14 (cost=0.00..6634.55 rows=50 width=10)
        Filter: ((my_condition = '12'::bpchar) OR (my_condition = '14'::bpchar))

However if I try a more dynamic approach,

EXPLAIN SELECT * FROM parent_table WHERE my_condition = ANY(array['12','04'])

it goes crazy and it tries to scan all the tables in the partition.

How should I modify the conditions for the child tables so it can be recognized?

Otherwise, how could I dynamically query against several child tables without writing a condition for each key.

Gabriel Furstenheim
  • 2,969
  • 30
  • 27
  • I have a similar issue with check constraints propagating in joins when using anything other than an equality condition: http://stackoverflow.com/questions/23565435/postgresql-check-contraints-not-passed-in-join. I am curious to see any responses. On a more constructive note, if you index the `my_condition` field, the index, at the very least should recognize that no records exist in the table for that condition. It's a poor substitute for skipping the table entirely. – Hambone Jan 13 '16 at 16:16
  • The static version of `IN` works, that is `select * from parent_table WHERE my_condition IN ('08','22')`. However I need it to use it for a plpgsql function with variable input. The only way I find is to build the query dynamically which in general is open to SQL injection (although not in my case). If someone provides a way to use a list of values inside a function without building a query I will accept the answer. – Gabriel Furstenheim Jan 14 '16 at 08:47
  • I have to ask... what version are you on? I tried this on 9.3, and it's working exactly as you would expect. `explain select * from parent_table where my_condition = any(array['1', '3'])` scans only interited tables 1 and 3 – Hambone Jan 15 '16 at 03:58
  • I'm using postgres 9.4. – Gabriel Furstenheim Jan 15 '16 at 08:30

1 Answers1

0

This is an issue with char(2) if the parent table is created using char varying(2) then the partition is used:

https://www.postgresql.org/message-id/CA%2BbJJby9Bo6wkHMvLYhRRBt%2BiLApTuHCO_-Ljmx8BnZjyu8ncg%40mail.gmail.com

Gabriel Furstenheim
  • 2,969
  • 30
  • 27