>Fra: Francisco Olarte <fola...@peoplecall.com>
>Sendt: 22. marts 2021 20:04
>Til: Niels Jespersen <n...@dst.dk>
>Cc: pgsql-general@lists.postgresql.org
>Emne: Re: design partioning scheme for selecting from latest partition
>
>Niels:
>
>On Mon, Mar 22, 2021 at 3:40 PM Niels Jespersen <n...@dst.dk> wrote:
>...
>> -- 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.
>
>Without an index, or some caching, you would need to scan partitions.
>...
>Even if you can do something like that, without an index you will need a full
>scan, or do some trigger magic and keep a cache ( just keep versio, count(*)
>on a table and maintain it ). If your partitions are ordered, you can always
>keep the last one indexed, or if you know versions do not decrease, you may
>keep things cached. This seems to be the kind of problem where the generic
>solution is hard but a little insider knowledge can accelerate it a lot.
>
>Regards.
> Francisco Olarte.
Thank you Francisco
I think I will revisit the whole design. Better do it right.
Niels
