>
> 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.
Will try to remember
