PostgreSQL – Partitioning Constraint Exclusion Not Working

constraintpartitioningpostgresql-9.6

I have a table with about 9 million rows where i'm keeping customer's id, a timestamp, and a cost value for the particular date-time.

It looks like this:

customer|t|consumption

I need to split my dataset into smaller tables which will inherit from the table master.

Master table:

CREATE TABLE my_table_master (
 customer TEXT NOT NULL,
 t TIMESTAMP NOT NULL,
 consumption INTEGER NOT NULL 
);

Child tables:

CREATE TABLE my_table_2 (
CHECK ( t='2013-07-01 01:00:00')
) INHERITS (my_table_master);

CREATE TABLE my_table_3 (
CHECK ( my_extract(t)=0 )
) INHERITS (my_table_master);

CREATE TABLE my_table_4 (
CHECK ( t <> '2013-07-01 01:00:00' AND my_extract(t) <> 0 )
) INHERITS (my_table_master);

The problem

Constraint exclusion is not working here. Note that i have already execute the command:

SET constraint_exclusion = on;

If for example i run the following query, the planner includes my_table_2 and my_table_3 instead of just my_table_2.

SELECT * FROM my_table_master WHERE t='2013-07-01 01:00:00';

Query Plan:

Append  (cost=0.00..24601.55 rows=967 width=24) (actual time=164.514..164.693 rows=993 loops=1)
->  Seq Scan on public.my_table_master  (cost=0.00..0.00 rows=1 width=44) (actual time=0.010..0.010 rows=0 loops=1)
 Output: my_table_master.customer, my_table_master.t, my_table_master.consumption
 Filter: (my_table_master.t = '2013-07-01 01:00:00'::timestamp without time zone)
 ->  Seq Scan on public.my_table_3  (cost=0.00..24577.43 rows=960 width=24) (actual time=164.472..164.472 rows=0 loops=1)
 Output: my_table_3.customer, my_table_3.t, my_table_3.consumption
 Filter: (my_table_3.t = '2013-07-01 01:00:00'::timestamp without time zone)
 Rows Removed by Filter: 1237954
 ->  Seq Scan on public.my_table_2  (cost=0.00..24.13 rows=6 width=44) (actual time=0.029..0.165 rows=993 loops=1)
 Output: my_table_2.customer, my_table_2.t, my_table_2.consumption
 Filter: (my_table_2.t = '2013-07-01 01:00:00'::timestamp without time zone)
 Planning time: 0.298 ms
 Execution time: 164.731 ms

EDIT:

If i'm only asking two specific queries on the DB that include the following WHERE clauses respectively:

WHERE t='2013-07-01 01:00:00' 
WHERE my_extract(t)=0

do you think i should possibly split my dataset in a different way?
If yes, please let me know.

Best Answer

On closer inspection... PostgreSQL is actually functioning as expected:

my_table_master is scanned because there is no constraint on it. The master table can actually have rows, there's nothing preventing it in PostgreSQL inheritance.
my_table_3 is scanned because its check constraint (t='2013-07-01 01:00:00') doesn't have anything to do (in principle) with the condition in the WHERE clause. Even if the definition of the function my_extract makes my_extract(t) = 0 incompatible with t='2013-07-01 01:00:00', the database is clearly not "intelligent enough" to infer it. You could add, if appropriate, even if redundant, NOT (my_extract(t) = 0) in your where clause. That would prevent my_table_3 from being scanned.
my_table_2 is scanned as expected.
my_table_4 is not actually scanned.

So, the constraints prevent one table from being scanned out of the 4.

Alternate query, adding the (my_extract(t) = 0) clause:

EXPLAIN 
    SELECT * 
    FROM my_table_master 
    WHERE t='2013-07-01 01:00:00' 
          AND NOT (my_extract(t) = 0)  /* This is the new addition */ ;

returns

Append  (cost=0.00..309.45 rows=7 width=44)
  ->  Seq Scan on my_table_master  (cost=0.00..0.00 rows=1 width=44)
        Filter: ((t = '2013-07-01 01:00:00'::timestamp without time zone) AND (my_extract(t) <> 0))
  ->  Seq Scan on my_table_2  (cost=0.00..309.45 rows=6 width=44)
        Filter: ((t = '2013-07-01 01:00:00'::timestamp without time zone) AND (my_extract(t) <> 0))

By adding the extra condition, the scan of `my_table_3` is prevented.

NOTE: To make the trials, I've made up my_extract like this:

CREATE FUNCTION my_extract(_t timestamp without time zone) 
RETURNS integer AS
$$
begin
    return extract(day from _t)::integer ;
end
$$
LANGUAGE plpgsql IMMUTABLE /* attention! */ ;

I've used plpgsql to ensure it is not inlined (even if it could be trivially done). Notice that it is necessary that the function be labeled as IMMUTABLE (that is, its result only depends on its parameters), or it won't work as expected.

ADDENDUM

EXPLAIN 
    SELECT * FROM my_table_master 
    WHERE my_extract(t) = 0 ;

  Append  (cost=0.00..613.25 rows=13 width=44)
    ->  Seq Scan on my_table_master  (cost=0.00..0.00 rows=1 width=44)
          Filter: (my_extract(t) = 0)
    ->  Seq Scan on my_table_2  (cost=0.00..306.62 rows=6 width=44)
          Filter: (my_extract(t) = 0)
    ->  Seq Scan on my_table_3  (cost=0.00..306.62 rows=6 width=44)
          Filter: (my_extract(t) = 0)

Works as well as expected:

my_table_master -> as before
my_table_2 -> is scanned because its constraint doesn't have any immediate relation to
my_table_3 -> is scanned because its constraint actually match the WHERE clause.

What PostgreSQL could do (and it doesn't) is eliminate the FILTER my_extract(t)=0 when scanning table 3, because the condition is already known to be true. Let's hope this optimization is implemented at some point in the future.

Misconception 1: "Natural order"

<query without ORDER BY>
... would naturally order by id, therefore yielding the latest records.

There is no natural order in a SELECT statement. Without ORDER BY you get rows in arbitrary order. Generally that will be the cheapest order in which Postgres can satisfy your query, i.e. the order in which tuples are stored physically or in which they are retrieved after an index look-up. But there is no guarantee whatsoever. If your statement seemed to work, this was pure luck / coincidence and it can break at any time.

Use your first query instead. If logdate is, in fact, of type date, or if you need to be sure, you should add more ORDER BY items to break ties and get a stable sort order. If you don't care which, append your (new) primary key (see below):

SELECT id, logdate, content
FROM   measurement
ORDER  BY logdate DESC, measurement_id DESC
LIMIT 500;

If the latest row (biggest measurement-id) is guaranteed to have the latest logdate, you can just ORDER BY measurement_id DESC, but don't take this for granted. In a multi-user environment a row with a later logdate can be written sooner than another row with a sooner logdate.

This is one reason why your idea for the new primary key is not very useful:

10000000000 - extract(epoch from logdate), and use it as PRIMARY KEY

The other reason: it is bound to fail sooner or later if logdate is not guaranteed to be unique - which it most probably isn't.

Use a serial column measurement_id as primary key instead. Or bigserial if you expect more than 2147483647 rows over time.

Index

You claim you did it just as the documentation says, and the documentation says:

For each partition, create an index on the key column(s), as well as ...

And further down:

We probably need indexes on the key columns too:

CREATE INDEX measurement_y2006m02_logdate ON measurement_y2006m02 (logdate);
CREATE INDEX measurement_y2006m03_logdate ON measurement_y2006m03 (logdate);
...

The only tiny difference: the example in the manual uses the more sensible singular form for the table name: measurement instead of ~~measurements~~.

If you go with my advice:

ORDER  BY logdate DESC, measurement_id DESC

make that:

CREATE INDEX measurement_y2006m02_logdate
ON measurement_y2006m02 (logdate DESC, measurement_id DESC);
...

Misconception 2: "scan all partitions"

Therefore, the query planner always scans all the partitions.

The query planner will plan to check all partitions in sequence. But as soon as the query is satisfied (500 rows are retrieved), it will stop executing. Test with EXPLAIN ANALYZE, you will see the annotation (never executed) behind remaining partitions.

If the planner shouldn't be smart enough to derive the best sequence in which to scan from your setup (can't test right now), you can give a hand with a UNION ALL query on the partitions:

(
SELECT measurement_id, logdate, content FROM measurement_y2006m03_logdate
ORDER  BY logdate DESC, measurement_id DESC
)            -- parens needed to include ORDER BY in individual legs of UNION query
UNION ALL
(
SELECT measurement_id, logdate, content FROM measurement_y2006m02_logdate
ORDER  BY logdate DESC, measurement_id DESC
)
...          -- latest partition first

LIMIT 500;

But that may not be necessary.

Best Answer

By adding the extra condition, the scan of my_table_3 is prevented.

ADDENDUM

Related Solutions

Mysql – Partitioning not working need primary key

PostgreSQL – Constraint Exclusion to Fetch Latest N Rows

Misconception 1: "Natural order"

Index

Misconception 2: "scan all partitions"

Related Question

By adding the extra condition, the scan of `my_table_3` is prevented.