Performance issues with inherited tables and indices

Question

I have a PostgreSQL database with a master table and 2 child tables. My master table:

CREATE TABLE test (
    id serial PRIMARY KEY, 
    date timestamp without time zone
);
CREATE INDEX ON test(date);

My child tables:

CREATE TABLE test_20150812 (
    CHECK ( date >= DATE '2015-08-12' AND date < DATE '2015-08-13' )
) INHERITS (test);

CREATE TABLE test_20150811 (
    CHECK ( date >= DATE '2015-08-11' AND date < DATE '2015-08-12' )
) INHERITS (test);

CREATE INDEX ON test_20150812(date);
CREATE INDEX ON test_20150811(date);

When I execute query like:

select * from test_20150812 where date > '2015-08-12' order by date desc;

It returns very quickly (20-30 miliseconds). EXPLAIN output:

 Limit  (cost=0.00..2.69 rows=50 width=212)
   ->  Index Scan Backward using test_20150812_date_idx on test_20150812  (cost=0.00..149538.92 rows=2782286 width=212)
         Index Cond: (date > '2015-08-12 00:00:00'::timestamp without time zone)

However if I execute query like:

select * from test where date > '2015-08-12' order by date desc;

It takes a long time (10-15 seconds). EXPLAIN output:

 Limit  (cost=196687.06..196687.19 rows=50 width=212)
   ->  Sort  (cost=196687.06..203617.51 rows=2772180 width=212)
         Sort Key: public.test.date
         ->  Result  (cost=0.00..104597.24 rows=2772180 width=212)
               ->  Append  (cost=0.00..104597.24 rows=2772180 width=212)
                     ->  Seq Scan on test  (cost=0.00..0.00 rows=1 width=1857)
                           Filter: (date > '2015-08-12 00:00:00'::timestamp without time zone)
                     ->  Seq Scan on test_20150812 test  (cost=0.00..104597.24 rows=2772179 width=212)
                           Filter: (date > '2015-08-12 00:00:00'::timestamp without time zone)

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

I see that, if a query is executed on master table, indices are never used. How can I improve it? I want to make all my queries on my master table. When querying for a specific date I expect no performance difference between querying on the master table or the child table.

Answer 1

"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 >= DATE '2015-08-12' AND date < DATE '2015-08-13' )

But your query has the condition:

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

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

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

WHERE ts >= '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 >= timestamp '2015-08-11' AND ts < 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.