I have a question to ask to help me understand better the indexes.Do we see any difference on this:
1
create index insertion_test_timestamp1_idx
on insertion_test (timestamp1);
create index insertion_test2_timestamp1_idx
on insertion_test (id13);
and this:
2
create index insertion_test_timestamp1_idx
on insertion_test (timestamp1,id13);
Some of my queries that i use look like this:
select * from timestampdb where timestamp1 >='2020-01-01 00:05:00' and timestamp1<='2020-01-02 00:05:00' and id13>'5',
select date_trunc('hour',timestamp1) as hour,avg(id13) from timestampdb where timestamp1 >='2020-01-01 00:05:00' and timestamp1<='2020-01-02 00:05:00' group by hour order by hour ,
select date_trunc('hour',timestamp1) as hour,max(id13) from timestampdb where timestamp1<='2020-01-01 00:05:00' group by hour order by hour desc limit 5,
select date_trunc('hour',timestamp1) as hour,max(id13) from timestamppsql where timestamp1 >='2020-01-01 00:05:00' and timestamp1<='2020-01-01 01:05:00' group by hour order by hour asc
My version is this:psql (PostgreSQL) 12.6 (Ubuntu 12.6-1.pgdg20.04+1)
In your case:
a query with where timestamp1 = 'sometimestamp' uses op 1 a query with where id13 = someid13 uses op 1
a query with where timestamp1 = 'sometimestamp' and id13 = someid13, may use op 2
a query with where id13 = someid13 and timestamp1 = 'sometimestamp' , may use op 1 or op 2
Why? because an index selection involves a lot of things, cardinality, size, stats, post select operations, etc.
And remember, op 2 is covering the first of op 1.