Re: Search in historical table

On Mon, Jun 5, 2023 at 4:59 PM Lorusso Domenico <domenico(dot)l76(at)gmail(dot)com> wrote:
> Ora queste soluzioni usano i timestamp_range, quindi sarebbe ragionevole pensare che sia la scelta migliore, ma appunto ho qualche dubbio su altri aspetti come il partizionamento, e in più non mi è chiaro come usare gli indici Gist con questi oggetti

"Si può fare!" (cit., Frankenstein Junior).
Non avendolo però mai usato in produzione, non so cosa succeda a
livello di performance. E comunque per partizionare devi avere dei
periodi non sovrapposti.

testdb=> create table test ( a text, validity tsrange ) partition by
range( validity);
CREATE TABLE
testdb=> create table m05 partition of test for values from (
'[2023-05-01, 2023-05-01]' ) to ( '[2023-
05-31, 2025-05-31]' );
CREATE TABLE
testdb=> create table m06 partition of test for values from (
'[2023-06-01, 2023-06-01]' ) to ( '[2023-
06-30, 2025-06-30]' );
CREATE TABLE

testdb=> insert into test values( 'a', '[2023-05-02, 2023-05-10]' );
INSERT 0 1
testdb=> insert into test values( 'b', '[2023-06-02, 2023-06-10]' );
INSERT 0 1
testdb=> select * from test;
a | validity
---+-----------------------------------------------
a | ["2023-05-02 00:00:00","2023-05-10 00:00:00"]
b | ["2023-06-02 00:00:00","2023-06-10 00:00:00"]
(2 rows)

testdb=> select * from only m05;
a | validity
---+-----------------------------------------------
a | ["2023-05-02 00:00:00","2023-05-10 00:00:00"]
(1 row)

testdb=> select * from only m06;
a | validity
---+-----------------------------------------------
b | ["2023-06-02 00:00:00","2023-06-10 00:00:00"]
(1 row)
testdb=> explain select * from test where validity @> '[2023-05-01,
2023-06-19]';
QUERY PLAN
----------------------------------------------------------------------------------------
Append (cost=0.00..42.04 rows=8 width=64)
-> Seq Scan on m05 test_1 (cost=0.00..21.00 rows=4 width=64)
Filter: (validity @> '["2023-05-01 00:00:00","2023-06-19
00:00:00"]'::tsrange)
-> Seq Scan on m06 test_2 (cost=0.00..21.00 rows=4 width=64)
Filter: (validity @> '["2023-05-01 00:00:00","2023-06-19
00:00:00"]'::tsrange)
(5 rows)