0

Our production system is on postgres 9, with a partition table. In one of the select queries, we cannot pass the partition key for the select, but we want the data from the latest partition, is it possible to make the postgres search from the last partition backwards?

EXPLAIN
SELECT *
FROM user.user
WHERE username = 123456
ORDER BY start_time DESC
LIMIT 1;

This searches from the earliest partition to the most recently created partition even though I've ordered my partition key in descending order. Is there a way to make postgres look from the recent most partition and look backward?

DDL of table:

create table IF NOT EXISTS user.user
(
    id                      uuid      default journey.uuid_generate_v4() not null,
    username                varchar(255),
    email_id                varchar(255),
    start_time              timestamp not null,
    end_time                timestamp not null
);


create table if not exists user.user_201301  
(CHECK ( start_time >= DATE '2013-01-01' AND start_time < DATE '2013-02-01')) INHERITS (user.user);
ALTER TABLE if exists user.user_201301 ADD PRIMARY KEY (id);
....

We've partition tables until 202101 and will continue.

On the above select query, the explain looks at all the partitions from 201301 till 202101, how do I make it look from 202101 backwards to 201301, this will make my query easier.

  • 1
    Please provide information — preferably in the form of DDL — about how you have setup the partitions. Is there any reason you haven’t upgraded to Postgres 12? You’re 3 major versions behind now. – Colin 't Hart Feb 10 at 4:52
  • @Colin'tHart updated the DDL. With respect to update, we are on AWS RDS Aurora with postgres as the engine with cloudformation as a way to build our DB, the cloudformation template has a problem, if we change the engine version, AWS does a replacement of the RDS which replaces the DB and essentially wiping out data and snapshots, we're working with AWS on a workaround , currently there's no workaround and we've to stick with postgres 9.6 docs.aws.amazon.com/AWSCloudFormation/latest/UserGuide/… – Pavanraotk Feb 10 at 8:08
  • Please edit your question and add the execution plan generated using explain (analyze, buffers, format text) (not just a "simple" explain) as formatted text and make sure you prevent the indention of the plan. Paste the text, then put ``` on the line before the plan and on a line after the plan. – a_horse_with_no_name Feb 10 at 9:23
  • partition by RANGE is not valid in 9.4 ? – Luuk Feb 10 at 10:41
  • @Luuk sorry, I posted my DDL with another DB from postgres 10, PARTITION BY RANGE is in postgres 10 and not in 9.6, I've edited my question – Pavanraotk Feb 11 at 1:04

Your Answer

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

Browse other questions tagged or ask your own question.