Consider the following test setup: create table t1 ( id int8 primary key, name varchar not null unique );
create table t2 ( id int8 primary key, t1_id int8 not null references t1 ); create table t2a ( primary key(id), check(t1_id = 1) ) inherits (t2); create table t2b ( primary key(id), check(t1_id = 2) ) inherits (t2); insert into t1 values(1, 'foo'); insert into t1 values(2, 'bar'); Now a simple query shows constraint exclusion; the following shows only t2 and t2a being checked: explain select * from t1, t2 where t1.id = t2.t1_id and t1.id = 1; But the following shows t2, t2a, and t2b being checked: explain select * from t1, t2 where t1.id = t2.t1_id and t1.name = 'foo'; And I tried to make the "it only involves a single t1 and matches a single partition" more explicit, but this didn't do it either: explain with tbl as (select id from t1 where name = 'foo') select * from t1, t2 where t1.id = t2.t1_id and t1.id = (select id from tbl); Granted these are near-empty tables, but I'm seeing the same behavior with real data and a real (complicated, 6-way join) query, where the vast majority of time is spent scanning the indexes of tables that cannot possibly contain any matching values. In that case, there's currently 55,000,000 rows spread over 87 partitions (the row count will grow steadily, the partition count will remain mostly the same). It's like this one, in that the constraint column is an integer and the check constraint is simple equality, not a range or list. And there is no index on the constraint column, since for every partition there is only a single value in that column--which means the planner winds up using a different index to scan the partitions (and it is a highly-selective index, so if it's going to scan non-matching partitions, it's not a bad index to use). I do have a workaround, in that there's only 1 special case where the performance matters, and in that case it's easy to directly join with the single appropriate partition. But I do wonder if I'm missing some way to encourage the planner to exclude partitions, or if this is forming into some sort of feature request, where potential exclusive constraints are passed through, so that before performing the index scan the executor can decide to skip the scan and return no matches? One additional wrinkle is that though I'm mostly concerned about a single query that hits a single partition, I also have a view, and queries against that could hit any partition (usually only one, but sometimes multiples). -- Scott Ribe scott_r...@elevated-dev.com http://www.elevated-dev.com/ (303) 722-0567 voice -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
