Performance issues with inherited tables and indices
"date"

Don't call your timestamp column "date", that's very misleading. Better yet, don't use the basic type name "date" as identifier at all, that's error-prone, leads to confusing error messages and it's a reserved word in standard SQL. Should be something like:

CREATE TABLE test (
  id serial PRIMARY KEY
, ts timestamp NOT NULL  -- also adding NOT NULL constraint
);
CREATE INDEX ON test(ts);


Caveats

Be aware of this caveat with constraint exclusion:


  Constraint exclusion only works when the query's WHERE clause contains
  constants (or externally supplied parameters). For example, a
  comparison against a non-immutable function such as CURRENT_TIMESTAMP
  cannot be optimized, since the planner cannot know which partition the
  function value might fall into at run time.


Bold epmhasis mine. You dodged this one, but with your confusing setup you might trip over it soon enough.

Also, since you have daily partitions:


  All constraints on all partitions of the master table are examined
  during constraint exclusion, so large numbers of partitions are likely
  to increase query planning time considerably. Partitioning using these
  techniques will work well with up to perhaps a hundred partitions;
  don't try to use many thousands of partitions.


Bold emphasis mine. If you are spanning more than a couple of months, try weekly or monthly partitions instead.

Mismatch in predicates

Your check condition:

CHECK ( date &gt;= DATE '2015-08-12' AND date &lt; DATE '2015-08-13' )


But your query has the condition:

where date &gt; '2015-08-12' order by date desc;  -- should be: &gt;=


This leaves a slight mismatch (probably incorrect!) and forces Postgres to recheck the condition. Not good, but also can't answer your question.

Use &gt;=, and either make the column NOT NULL or append NULLS LAST to the statement:

WHERE ts &gt;= '2015-08-12' ORDER BY ts DESC;


... and make the index match.

Unclean CHECK constraint

The CHECK constraints are saved with date constants instead of timestamp constants. Should be something like:

CHECK (ts &gt;= timestamp '2015-08-11' AND ts &lt; timestamp '2015-08-12');


Constraint exclusion

You write:


  constraint_exclusion is set to ON in my postgresql.conf. Therefore it should only be executed on test_20150812.


As you can see in the query plan, only test and test_20150812 are scanned, but not test_20150811. Ergo: constraint exclusion is working fine, despite all your deviations. That's just another wrong track.

Won many battles, but not the war

After cleaning all of this up, I see a bitmap index scan for the child table instead of your seq scan. Still slower than a query on the child table only. That's obviously due to the fact that the parent table itself can have matching rows, too, which must be sorted with the rest, so the result cannot just be read from the index.