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
iteither:
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
matchingvalues.
In that case, there's currently 55,000,000 rows spread over 87 partitions (the row count will grow steadily, the
partitioncount will remain mostly the same). It's like this one, in that the constraint column is an integer and the
checkconstraint is simple equality, not a range or list. And there is no index on the constraint column, since for
everypartition there is only a single value in that column--which means the planner winds up using a different index to
scanthe partitions (and it is a highly-selective index, so if it's going to scan non-matching partitions, it's not a
badindex 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
todirectly 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
sortof feature request, where potential exclusive constraints are passed through, so that before performing the index
scanthe 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
havea view, and queries against that could hit any partition (usually only one, but sometimes multiples).
--
Scott Ribe
scott_ribe@elevated-dev.com
http://www.elevated-dev.com/
(303) 722-0567 voice