1 / 8

Partitions not Working as Expected

Partitions not Working as Expected. By: Lloyd Albin 8/6/2013. The Problem. I found this issue posted on the pgsql -performance mailing list and decided to verify the problem since we use partitions in our database. http:// www.postgresql.org/message-id/51CC652F.3010304@optionshouse.com

melita
Download Presentation

Partitions not Working as Expected

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Partitions not Workingas Expected By: Lloyd Albin 8/6/2013

  2. The Problem I found this issue posted on the pgsql-performance mailing list and decided to verify the problem since we use partitions in our database. http://www.postgresql.org/message-id/51CC652F.3010304@optionshouse.com My tanks to Shaun for exposing this issue. Shaun ThomasOptionsHouse| 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604312-676-8870sthomas(at)optionshouse(dot)com

  3. Creating the tables We first create the parent table and then two child tables. The child tables have check constraints to limit what data may be added to the child table and to help the query optimizer to know if this table should be part of the query or not when querying the parent table. CREATE TABLE part_test ( fake INT, part_col TIMESTAMP WITHOUT TIME ZONE ); CREATE TABLE part_test_1 ( CHECK (part_col >= '2013-05-01' AND part_col < '2013-06-01') ) INHERITS (part_test); CREATE TABLE part_test_2 ( CHECK (part_col >= '2013-04-01' AND part_col < '2013-05-01') ) INHERITS (part_test);

  4. Usage of the check constraint in your query If you hard code your date into your query, then it uses the correct child partition for your query. EXPLAIN SELECT * FROM part_test WHERE part_col > '2013-06-27';

  5. The problem We find that where we think it should have used the correct child table, it instead use all the child tables, ignoring the check constraints. As a dba we tell the developers to use Postgres as much as possible instead of their programming language. EXPLAIN SELECT * FROM part_test WHERE part_col > CURRENT_DATE;

  6. Additional issues While these were not tested/reported, they will also fail as I found out during my testing. SELECT * FROM part_test WHERE part_col > NOW(); SELECT * FROM part_test WHERE part_col > date_trunc(‘month’, NOW());

  7. Talking about the issue. This is one of the many responses. This one by Tom Lane. The long and the short of the issue is that the DATE/TIME functions can change over time or even be different between when you prepare a statement and execute the statement so they will always scan all the child tables. I don't see any very good solution to your problem within the current approach to partitioning, which is basically theorem-proving. That proof engine has no concept of time passing, let alone the sort of detailed knowledge of the semantics of this particular function that would allow it to conclude "if CURRENT_DATE > '2013-06-20' is true now, it will always be so in the future as well".

  8. The solution We all hate this solution for a fix for this will be a long ways off as they say the whole engine will have to be re-written to support a fix. The only current solution is to hard code your date/timestamp into your query where your programming language submits the current date/timestamp as a text string instead of using the system variables.

More Related