Materialized view question and best practices

TimescaleDB and PostgreSQL
1

My intention is to downsample a very large table for Grafana purpose.
I create a materialized view like this.

DROP MATERIALIZED VIEW IF EXISTS public.net_drop_1day;
CREATE MATERIALIZED VIEW public.net_drop_1day
WITH (timescaledb.continuous) AS 
SELECT time_bucket(INTERNAL '1 day',t."time") as time,
tt.host,
tt.interface,
tt.drop_in,
tt.drop_out,
max(drop_in) as max_dropin,
max(drop_out) as max_dropout FROM telegraf.net t,telegraf.net_tag tt where (t.tag_id = tt.tag_id) group by 1,2,3,4,5;

It took about 2 hours to generate this view on a very large server (512GB of memory).

When I run the query
select * from net_drop_1day order by desc limit 100
it takes close to 30 secs to get the data back. Do I need do any sort of re-indexing?

2

Hi @Rita_Morgan , can you share what timescaledb version do you have?

Before 2.11 the materialized_only option toggled the defaults, so maybe the problem is that it’s real time and you need to be materialized_only.

You can check by looking the explain analyze of the query.

3

Hello
I am running 2.11.1