Return to Question

edit:

My postgres version: postgres (PostgreSQL) 9.0.12

After Erwins' comment I tried and it works fine. However I see another problem;

I use it with collumn which named coll;

CREATE TABLE test2 (
    id serial PRIMARY KEY,
    coll character varying(32),
    ts timestamp without time zone
);
CREATE INDEX ON test2(ts);

CREATE TABLE test2_20150812 (
    CHECK ( ts >= timestamp '2015-08-12' AND ts < timestamp '2015-08-13' )
) INHERITS (test2);

CREATE TABLE test2_20150811 (
    CHECK ( ts >= timestamp '2015-08-11' AND ts < timestamp '2015-08-12' )
) INHERITS (test2);

CREATE INDEX ON test2_20150812(ts);
CREATE INDEX ON test2_20150811(ts);

EXPLAIN select * from test2 WHERE ts >= '2015-08-11' ORDER BY ts DESC;

output is;

Sort  (cost=76.86..78.64 rows=715 width=46)
   Sort Key: public.test2.ts
   ->  Result  (cost=0.00..42.96 rows=715 width=46)
         ->  Append  (cost=0.00..42.96 rows=715 width=46)
               ->  Seq Scan on test2  (cost=0.00..0.00 rows=1 width=46)
                     Filter: (ts >= '2015-08-11 00:00:00'::timestamp without time zone)
               ->  Bitmap Heap Scan on test2_20150812 test2  (cost=7.02..21.48 rows=357 width=46)
                     Recheck Cond: (ts >= '2015-08-11 00:00:00'::timestamp without time zone)
                     ->  Bitmap Index Scan on test2_20150812_ts_idx  (cost=0.00..6.93 rows=357 width=0)
                           Index Cond: (ts >= '2015-08-11 00:00:00'::timestamp without time zone)
               ->  Bitmap Heap Scan on test2_20150811 test2  (cost=7.02..21.48 rows=357 width=46)
                     Recheck Cond: (ts >= '2015-08-11 00:00:00'::timestamp without time zone)
                     ->  Bitmap Index Scan on test2_20150811_ts_idx  (cost=0.00..6.93 rows=357 width=0)
                           Index Cond: (ts >= '2015-08-11 00:00:00'::timestamp without time zone)

However if I change column coll and do these steps again;

CREATE TABLE test2 (
    id serial PRIMARY KEY,
    coll character varying(255),
    ts timestamp without time zone
);

Explain output is;

 Sort  (cost=42.47..43.18 rows=287 width=157)
   Sort Key: public.test2.ts
   ->  Result  (cost=0.00..30.75 rows=287 width=157)
         ->  Append  (cost=0.00..30.75 rows=287 width=157)
               ->  Seq Scan on test2  (cost=0.00..0.00 rows=1 width=157)
                     Filter: (ts >= '2015-08-11 00:00:00'::timestamp without time zone)
               ->  Seq Scan on test2_20150812 test2  (cost=0.00..15.38 rows=143 width=157)
                     Filter: (ts >= '2015-08-11 00:00:00'::timestamp without time zone)
               ->  Seq Scan on test2_20150811 test2  (cost=0.00..15.38 rows=143 width=157)
                     Filter: (ts >= '2015-08-11 00:00:00'::timestamp without time zone)

I dont understand why postgresql do that. Is there any way for using childs' index with column?

CREATE TABLE test2 (
    id serial PRIMARY KEY,
    coll character varying(32),
    ts timestamp without time zone
);
CREATE INDEX ON test2(date);

CREATE TABLE test2_20150812 (
    CHECK ( ts >= timestamp '2015-08-12' AND ts < timestamp '2015-08-13' )
) INHERITS (test2);

CREATE TABLE test2_20150811 (
    CHECK ( ts >= timestamp '2015-08-11' AND ts < timestamp '2015-08-12' )
) INHERITS (test2);

CREATE INDEX ON test2_20150812(ts);
CREATE INDEX ON test2_20150811(ts);

EXPLAIN select * from test2 WHERE ts >= '2015-08-11' ORDER BY ts DESC;

CREATE TABLE test2 (
    id serial PRIMARY KEY,
    coll character varying(32),
    ts timestamp without time zone
);
CREATE INDEX ON test2(ts);

CREATE TABLE test2_20150812 (
    CHECK ( ts >= timestamp '2015-08-12' AND ts < timestamp '2015-08-13' )
) INHERITS (test2);

CREATE TABLE test2_20150811 (
    CHECK ( ts >= timestamp '2015-08-11' AND ts < timestamp '2015-08-12' )
) INHERITS (test2);

CREATE INDEX ON test2_20150812(ts);
CREATE INDEX ON test2_20150811(ts);

EXPLAIN select * from test2 WHERE ts >= '2015-08-11' ORDER BY ts DESC;