Curious, what is seq_page_cost and random_page_cost?
show seq_page_cost
->1
show random_page_cost
->4
Any idea of your cache hits for indexes?
No, I am afraid not. It’s been a long time since I went that deep in the RDBMS behaviour (must have been Oracle 7.something :) )
If they are very high and/or you have SSD or similar fast storage, then maybe random_page_cost should be 1.1-2 and not default 4 (assuming seq_page_cost is still 1). The planner will be more likely to use an index scan if the expected cost for scanning an index (random) is closer to a sequential read. Sorry if this explanation is completely superfluous and you have already configured this.
I played around with some of the settings (also with setting enable_seqscan to false). While some of the plans did no longer use a full table scan, the performance did not really work out when I tried. Tbh, I never got too deep in those plans to find out why that would be, as I am not too keen on changing these settings in the first place.
It would be interesting to see explain (analyze buffers) output so we can see the actual counts for these nodes. I'm rather surprised that the query I provided didn't use the timestamp index unless the inclusion of 3 days worth of range meant that it was estimated to be too high a fraction of the table. If you just execute only the subquery, is the index used by chance?
Index is not used for the subquery
explain
select mv_inner.*
from measurement_value AS mv_inner
where mv_inner.timestamp > '2020-11-06'::timestamp - interval '1 day' and mv_inner.timestamp < '2020-11-07'::timestamp + interval '1 day'
offset 0
==>
Seq Scan on measurement_value mv_inner (cost=0.00..7175777.00 rows=1219616 width=1006) Filter: (("timestamp" > '2020-11-05 00:00:00'::timestamp without time zone) AND ("timestamp" < '2020-11-08 00:00:00'::timestamp without time zone))
Top posting (reply with all previous copied below) is discouraged on these lists. I think because it makes the archives rather messy.