in postgresql 9.2, can hint optimization be used for partitions?
in a simple partitioned table:
-- note: no records stored in base, only inheritors of base create table base( base_id bigint, base_tp char(3) not null, ... ); create table base_abc( base_id bigserial primary key, base_tp default 'abc' check( base_tp = 'abc' ), ... ) inherits( base ); create table base_efg( base_id bigserial primary key, base_tp default 'efg' check( base_tp = 'efg' ), ... ) inherits( base );
if the the where clause in a query were to use base_tp, eg,
select * from base where ... and base_tp='abc'
would under 9.2 the query be optimized to select only table base_abc or would it, as it does currently, query a base, base_abc, and base_efg?
Well, this behaviour can be set via the constraint_exclusion config option. If it's on, then only the partitions with matching constraint(s) will be accessed. But you should be careful, and choose partition instead in order to avoid a heavy performance hit. It's just the same considering partitioned tables, but leaves others unaffected.
Have a look at its documentation.