design partioning scheme for selecting from latest partition

Поиск
Список
Период
Сортировка
От Niels Jespersen
Тема design partioning scheme for selecting from latest partition
Дата
Msg-id 5de5c8b614544d7ba782b15ffb12e5da@dst.dk
обсуждение исходный текст
Ответы Re: design partioning scheme for selecting from latest partition
Список 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



В списке pgsql-general по дате отправления:

Предыдущее
От: Ron Clarke
Дата:
Сообщение: Re: More than one UNIQUE key when matching items..
Следующее
От: Edward Donahue III
Дата:
Сообщение: gdal32-libs-3-2-2-13.rhel bad dependency