> Good luck!
Tx! And tx for your support.
On Wed, Nov 11, 2020, 7:30 AM Peter Coppens
wrote:
>
> > It seems odd to me to not do any basic adjustment of random_page_cost
> though. It isn't a magic number that the core team know to be perfect. It
> is a baseline that is likely to be quite different for each use case and
> server config. Wh
>
> Ahhh. You don't have a single column index on the timestamp value or a multi
> column one with timestamp first. No wonder the subquery didn't help. My
> apologies for not realizing that before. Thanks for satisfying my curiosity
> why it didn't perform like it should. Certainly, that index
On Tue, Nov 10, 2020, 10:51 PM Peter Coppens
wrote:
> If you disable sequential scan, does it choose the index and what cost
> does it show?
>
>
> It chooses the index, but apparently to create some intermediate structure
> that then later still needs to be joined on the device_id. Probably
> req
> Curious, how accurate is that row count of 1.2 million records for 3 days?
Not to bad actually
select count(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'
On Tue, Nov 10, 2020, 3:24 PM Peter Coppens
wrote:
> 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
>
> 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 a
On Tue, Nov 10, 2020 at 1:25 AM Peter Coppens
wrote:
> Triggered by Michael mentioning subqueries I ended up trying
>
> explain
> select d.short_id,mv.timestamp ,mv.I64_01
> from device d, device_configuration dc, measurement_value mv
> where mv.device_id=d.short_id and dc.device_id = d.id
Triggered by Michael mentioning subqueries I ended up trying
explain
select d.short_id,mv.timestamp ,mv.I64_01
from device d, device_configuration dc, measurement_value mv
where mv.device_id=d.short_id and dc.device_id = d.id and dc.latest=true and
mv.timestamp > '2020-11-06'::times
Pavel
Tx for the tip. But given that if the I64_01 column is removed from the select
list, the index is used I guess the cast is not likely to be the cause.
Like so
explain
select d.short_id,mv.timestamp --,mv.I64_01
from device d, device_configuration dc, measurement_value mv,
pg_timezo
út 10. 11. 2020 v 8:18 odesílatel Peter Coppens
napsal:
> Michael
>
> Many thanks for spending your time on this. Your alternative does not help
> unfortunately (see execution plan)
>
> Still a sequential scan on the complete table. I have tried many
> alternatives and somehow whenever I add a co
Michael
Many thanks for spending your time on this. Your alternative does not help
unfortunately (see execution plan)
Still a sequential scan on the complete table. I have tried many alternatives
and somehow whenever I add a column that is not in the index (I64_01) the
optimizer decides not to
On Mon, Nov 9, 2020 at 1:11 PM Peter Coppens
wrote:
> Adding the tzn.utc_offset results in the fact that the execution plan no
> longer considers to use the index on the measurement_value table. Is there
> any way the SQL can be rewritten so that the index is used? Or any other
> solution so that
Hello,
Consider the following PostgreSQL 9.6.18 tables
- measurement_value: time series table with a unique key on
(device_id,timestamp) columns and a number of columns with measurements.
Table contains a large number of rows (>150million)
- device table: with device properties (short_id joins to
14 matches
Mail list logo
