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.