7

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.

  • @ypercube yes. I tried it. The result was same. – umut Aug 13 '15 at 13:54
  • @ypercube unfortunately result was same. It still take 10-15 seconds. – umut Aug 13 '15 at 14:01
  • 1
    I took the liberty to resolve inconsistent table names (test_20150812 vs. test_table_20150812) in favor of short names. You forgot to provide your version of Postgres and please provide the output of EXPLAIN (ANALYZE, BUFFERS), not just EXPLAIN. Also odd: Seq Scan on test_table_20150812 test_table (now simplified to test_20150812 test) isn't valid explain output ... – Erwin Brandstetter Aug 13 '15 at 16:29
  • pg 9.0 is very old by now, it reaches EOL next month (Sept. 2015). Seriously consider upgrading to a current version. Current versions have many performance improvements over this old version. While stuck with your outdated version, you should at least upgrade to the latest point release (currently 9.0.22) for bug fixes. – Erwin Brandstetter Aug 14 '15 at 8:32
  • 1
    I rolled back later changes that ultimately went into a new question: dba.stackexchange.com/q/111022/3684 – Erwin Brandstetter Aug 14 '15 at 9:01
11

"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.

  • Thank you for helping @Erwin. I apply these changes and edit my question. Can you help me about my new problem? – umut Aug 14 '15 at 8:05
  • @umut: Please put the new issue into a new question. This one is overloaded already. You can always link to this one for context. (Might be you just need to VACUUM ANALYZE your tables.) – Erwin Brandstetter Aug 14 '15 at 8:25
  • I write new question: dba.stackexchange.com/questions/111022/… – umut Aug 14 '15 at 8:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.