From: | Niels Jespersen <NJN(at)dst(dot)dk> |
---|---|
To: | "pgsql-general(at)lists(dot)postgresql(dot)org" <pgsql-general(at)lists(dot)postgresql(dot)org> |
Subject: | design partioning scheme for selecting from latest partition |
Date: | 2021-03-22 14:40:32 |
Message-ID: | 5de5c8b614544d7ba782b15ffb12e5da@dst.dk |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hello
I have a table partitioned like this
drop table if exists s cascade;
create table s
(
version int not null,
a int,
b int
) partition by list (version);
-- Add tens of partitions
-- Load millions of rows in each partition
-- Then I want to be able to do this wothout scanning all partitions for the highest version number.
select s.* from s where s.version = (select max(version) from s);
I could add an index on the version column. But the only use would be to the newest partition, so that seems a bit like overkill, indexing 100 of milliomns of rows.
Is there another way to do this in a cheaper way.
For now I have created a materialized view based on the select above, thus only scanning for max partition only once.
Niels Jespersen
From | Date | Subject | |
---|---|---|---|
Next Message | Edward Donahue III | 2021-03-22 15:02:55 | gdal32-libs-3-2-2-13.rhel bad dependency |
Previous Message | Ron Clarke | 2021-03-22 13:22:43 | Re: More than one UNIQUE key when matching items.. |