Return to Answer

###After question update As long as you read the whole table, an index is of limited use. If you query a single table with a matching index, so that readily sorted rows can be read from the index and Postgres can skip the sort step altogether, you'll see an index scan.

After question update

As long as you read the whole table, an index is of limited use. If you query a single table with a matching index, so that readily sorted rows can be read from the index and Postgres can skip the sort step altogether, you'll see an index scan.

• PostgreSQL partial index unused when created on a table with existing data

• PostgreSQL partial index unused when created on a table with existing data

This SQL fiddle with 10k rows per child and valid statistics shows bitmap index scans as expected.

###After question update As long as you read the whole table, an index is of limited use. If you query a single table with a matching index, so that readily sorted rows can be read from the index and Postgres can skip the sort step altogether, you'll see an index scan.

That's not possible when multiple tables have to be combined. This SQL fiddle with 10k rows per child and valid statistics shows bitmap index scans as expected. After repeating the query a couple of times (as soon as the whole table is cached), Postgres may skip the index and switch to sequential scans, which have become cheaper now.

Postgres is obviously not smart enough to understand the mutually excluding check constraints, which would allow to append readily sorted results from each table as is. You could force that by manually instructing it:

(SELECT * FROM test2_20150812 ORDER BY ts DESC)
UNION ALL
(SELECT * FROM test2_20150811 ORDER BY ts DESC);

However, Postgres should be smart enough to use Merge Append (cheap method to combine pre-sorted sets). In my local tests on PostgreSQL 9.4 I actually see index scans on each partition, combined with Merge Append. That plan is better, but it's not that much faster than sequential scans because, remember!, as long as you read the whole table, an index is of limited use.

'QUERY PLAN'
'Merge Append  (cost=0.73..16866.41 rows=200001 width=45)'
'  Sort Key: test.ts'
'  ->  Index Scan Backward using test_ts_idx on test  (cost=0.13..8.14 rows=1 width=528)'
'        Index Cond: (ts >= '2015-08-11 00:00:00'::timestamp without time zone)'
'  ->  Index Scan Backward using test_20150811_ts_idx on test_20150811  (cost=0.29..6594.01 rows=100000 width=45)'
'        Index Cond: (ts >= '2015-08-11 00:00:00'::timestamp without time zone)'
'  ->  Index Scan Backward using test_20150812_ts_idx on test_20150812  (cost=0.29..6594.29 rows=100000 width=45)'
'        Index Cond: (ts >= '2015-08-11 00:00:00'::timestamp without time zone)'

I don't get the same plan with Postgres 9.3 (testing on sqlfiddle). Must be a limitation of pg 9.3. (?)

But since you are using the outdated version 9.0, none of that is available to you.
Merge Append was introduced with 9.1.

You get more interesting results when limiting the result to few rows. varchar(15) or varchar(255) has very little impact on the query plan. The wider type favors indexes some more.

Your added fiddle with some more test queries.