Re: partition queries hitting all partitions even though check key is specified

Kevin Kempter <kevink(at)consistentstate(dot)com> writes:
> I cant figure out why we're scanning all of our partitions.

The example works as expected for me:

regression=# CREATE TABLE url_hits (
id integer NOT NULL,
content_type_id integer,
file_extension_id integer,
"time" integer,
bytes integer NOT NULL,
path_id integer);
CREATE TABLE
regression=# create table url_hits_2011_12 (
check (
"time" >= extract ('epoch' from timestamp '2011-12-01
00:00:00')::int4
and "time" <= extract ('epoch' from timestamp '2011-12-31
23:59:59')::int4
)
) INHERITS (url_hits);
CREATE TABLE
regression=# create table url_hits_2009_08 (
check (
"time" >= extract ('epoch' from timestamp '2009-08-01
00:00:00')::int4
and "time" <= extract ('epoch' from timestamp '2009-08-31
23:59:59')::int4
)
) INHERITS (url_hits);
CREATE TABLE
regression=# explain select * from url_hits where "time" <
date_part('epoch'::text, '2009-08-12'::timestamp without time zone)::integer;
QUERY PLAN
-----------------------------------------------------------------------------------------
Result (cost=0.00..82.50 rows=1401 width=24)
-> Append (cost=0.00..82.50 rows=1401 width=24)
-> Seq Scan on url_hits (cost=0.00..27.50 rows=467 width=24)
Filter: ("time" < 1250049600)
-> Seq Scan on url_hits_2011_12 url_hits (cost=0.00..27.50 rows=467 width=24)
Filter: ("time" < 1250049600)
-> Seq Scan on url_hits_2009_08 url_hits (cost=0.00..27.50 rows=467 width=24)
Filter: ("time" < 1250049600)
(8 rows)

regression=# set constraint_exclusion TO 1;
SET
regression=# explain select * from url_hits where "time" <
date_part('epoch'::text, '2009-08-12'::timestamp without time zone)::integer;
QUERY PLAN
-----------------------------------------------------------------------------------------
Result (cost=0.00..55.00 rows=934 width=24)
-> Append (cost=0.00..55.00 rows=934 width=24)
-> Seq Scan on url_hits (cost=0.00..27.50 rows=467 width=24)
Filter: ("time" < 1250049600)
-> Seq Scan on url_hits_2009_08 url_hits (cost=0.00..27.50 rows=467 width=24)
Filter: ("time" < 1250049600)
(6 rows)

You sure you remembered those fiddly little casts everywhere?
(Frankly, declaring "time" as integer and not timestamp here strikes
me as utter lunacy.) What PG version are you using?

regards, tom lane