Hello everyone,
We recently migrated to TimescaleDB and are currently working on optimizing some long-running queries. We’ve added several additional indexes in hopes of improving performance, but the planner rarely chooses to use them.
To simplify timezone-based grouping, we decided to store only hourly aggregates in a materialized view and use the time_bucket function at query time to group by day or month. This approach allows us to handle timezone-specific grouping dynamically without maintaining separate views for each timezone.
In some cases, we’ve tried creating simplified views that omit unnecessary columns, which helped a bit — but unfortunately, this method is not universally applicable across our queries.
I’d be very grateful for any advice, best practices, or suggestions related to query optimization, schema design, or indexing strategies that could help us reduce execution times. Thanks in advance!
Query details:
-
Total records in the materialized view: ~32 million
-
Records relevant to the queried customer: ~23 million
-
Query execution time: ~10–15 seconds
-
We understand that one customer accounts for ~2/3 of all data, which may impact index usage and planner decisions.
SELECT sum(i.value) as value, p.provider, i.panel, i.type, time_bucket('1 day', i.hour AT TIME ZONE 'Africa/Blantyre') as date FROM increments_hourly i
INNER JOIN provider p ON p.id = i.provider
WHERE i.account IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20)
AND i.hour >= @dateFrom AND i.hour <= @dateTo
AND p.provider IN ('provider1.com', 'provider2.com', 'provider3.com', 'provider4.com', 'provider5.com', 'provider6.com', 'provider7.com', 'provider8.com', 'provider9.com', 'provider10.com')
AND i.panel = 'panel'
GROUP BY p.provider, i.panel, i.type, date;
Hypertable & Environment:
Chunk time interval: initially 1 day, changed to 7 days
Hypertable:
- retention 32 days
TimescaleDB version: 2.17.2
Materialized view: increments_hourly (continuous aggregate based on increments_ht):
- no retention
Hypertable
CREATE TABLE public.increments_ht (
account int4 NULL,
"date" timestamptz NOT NULL,
"type" int4 NULL,
vps int4 NULL,
panel text NULL,
value int4 NULL,
from_email int4 NULL,
from_domain int4 NULL,
channel int4 NULL,
provider int4 NULL,
CONSTRAINT increments_ht_channel_id_fk FOREIGN KEY (channel) REFERENCES public.channel(id),
CONSTRAINT increments_ht_from_domain_id_fk FOREIGN KEY (from_domain) REFERENCES public.from_domain(id),
CONSTRAINT increments_ht_from_email_id_fk FOREIGN KEY (from_email) REFERENCES public.from_email(id),
CONSTRAINT increments_ht_mail_type_id_fk FOREIGN KEY ("type") REFERENCES public.mail_type(id),
CONSTRAINT increments_ht_provider_id_fk FOREIGN KEY (provider) REFERENCES public.provider(id),
CONSTRAINT increments_ht_vps_id_fk FOREIGN KEY (vps) REFERENCES public.vps(id)
);
CREATE INDEX increments_ht_date_idx ON public.increments_ht USING btree (date DESC);
Materialized view “increments_hourly”:
CREATE MATERIALIZED VIEW increments_hourly
WITH (timescaledb.continuous) AS
SELECT
account,
time_bucket('1 hour', date) AS hour,
type,
vps,
panel,
from_email,
from_domain,
channel,
provider,
SUM(value) as value
FROM
increments_ht
GROUP BY
account, type, vps, panel, from_email, from_domain, channel, provider, hour;
CREATE INDEX _materialized_hypertable_31_account_hour_idx ON _timescaledb_internal._materialized_hypertable_31 USING btree (account, hour DESC);
CREATE INDEX _materialized_hypertable_31_channel_hour_idx ON _timescaledb_internal._materialized_hypertable_31 USING btree (channel, hour DESC);
CREATE INDEX _materialized_hypertable_31_from_domain_hour_idx ON _timescaledb_internal._materialized_hypertable_31 USING btree (from_domain, hour DESC);
CREATE INDEX _materialized_hypertable_31_from_email_hour_idx ON _timescaledb_internal._materialized_hypertable_31 USING btree (from_email, hour DESC);
CREATE INDEX _materialized_hypertable_31_hour_idx ON _timescaledb_internal._materialized_hypertable_31 USING btree (hour DESC);
CREATE INDEX _materialized_hypertable_31_panel_hour_idx ON _timescaledb_internal._materialized_hypertable_31 USING btree (panel, hour DESC);
CREATE INDEX _materialized_hypertable_31_provider_hour_idx ON _timescaledb_internal._materialized_hypertable_31 USING btree (provider, hour DESC);
CREATE INDEX _materialized_hypertable_31_type_hour_idx ON _timescaledb_internal._materialized_hypertable_31 USING btree (type, hour DESC);
CREATE INDEX _materialized_hypertable_31_vps_hour_idx ON _timescaledb_internal._materialized_hypertable_31 USING btree (vps, hour DESC);
CREATE INDEX idx_increments_hourly_opt_by_account_provider ON _timescaledb_internal._materialized_hypertable_31 USING btree (account, provider, panel, hour) INCLUDE (value, type);
CREATE INDEX idx_increments_hourly_opt_by_provider ON _timescaledb_internal._materialized_hypertable_31 USING btree (provider, panel, hour) INCLUDE (value, type);
CREATE INDEX idx_increments_hourly_opt_by_type ON _timescaledb_internal._materialized_hypertable_31 USING btree (account, type, panel, hour) INCLUDE (value);
CREATE INDEX idx_increments_hourly_panel ON _timescaledb_internal._materialized_hypertable_31 USING btree (panel);
Table "provider":
CREATE TABLE public.provider (
id serial4 NOT NULL,
panel varchar(100) NULL,
provider varchar(100) NULL,
CONSTRAINT provider_pk PRIMARY KEY (id)
);
CREATE INDEX idx_provider_id_provider ON public.provider USING btree (id, provider);
CREATE INDEX idx_provider_provider ON public.provider USING btree (provider);
CREATE UNIQUE INDEX provider_panel_provider_uindex ON public.provider USING btree (panel, provider);
Explain:
Finalize GroupAggregate (cost=480281.74..480299.79 rows=133 width=66) (actual time=4324.209..4944.966 rows=2091 loops=1)
Group Key: p.provider, "*SELECT* 2".type, (time_bucket('1 day'::interval, ("*SELECT* 2".hour AT TIME ZONE 'Africa/Blantyre'::text)))
Buffers: shared hit=208 read=266311, temp read=54694 written=54789
-> Gather Merge (cost=480281.74..480296.08 rows=110 width=66) (actual time=4324.166..4943.289 rows=3186 loops=1)
Workers Planned: 2
Workers Launched: 2
Buffers: shared hit=208 read=266311, temp read=54694 written=54789
-> Partial GroupAggregate (cost=479281.71..479283.36 rows=55 width=66) (actual time=4234.692..4721.384 rows=1062 loops=3)
Group Key: p.provider, "*SELECT* 2".type, (time_bucket('1 day'::interval, ("*SELECT* 2".hour AT TIME ZONE 'Africa/Blantyre'::text)))
Buffers: shared hit=208 read=266311, temp read=54694 written=54789
-> Sort (cost=479281.71..479281.85 rows=55 width=42) (actual time=4234.380..4564.804 rows=1476649 loops=3)
Sort Key: p.provider, "*SELECT* 2".type, (time_bucket('1 day'::interval, ("*SELECT* 2".hour AT TIME ZONE 'Africa/Blantyre'::text)))
Sort Method: external merge Disk: 84336kB
Buffers: shared hit=208 read=266311, temp read=54694 written=54789
Worker 0: Sort Method: external merge Disk: 69200kB
Worker 1: Sort Method: external merge Disk: 65296kB
-> Hash Join (cost=94.84..479280.12 rows=55 width=42) (actual time=85.746..3049.765 rows=1476649 loops=3)
Hash Cond: ("*SELECT* 2".provider = p.id)
Buffers: shared hit=164 read=266311
-> Parallel Append (cost=0.00..471063.40 rows=3093928 width=32) (actual time=85.343..2182.359 rows=3307294 loops=3)
Buffers: shared hit=69 read=266311
-> Subquery Scan on "*SELECT* 2" (cost=0.00..0.02 rows=1 width=56) (actual time=58.234..58.235 rows=0 loops=1)
-> HashAggregate (cost=0.00..0.01 rows=1 width=76) (actual time=58.232..58.233 rows=0 loops=1)
Group Key: account, type, vps, from_email, from_domain, channel, provider, time_bucket('01:00:00'::interval, date)
Worker 0: Batches: 1 Memory Usage: 32kB
-> Result (cost=0.00..0.00 rows=0 width=72) (actual time=58.227..58.227 rows=0 loops=1)
One-Time Filter: false
-> Subquery Scan on "*SELECT* 1" (cost=0.07..455593.74 rows=3093927 width=32) (actual time=65.929..1950.899 rows=3307294 loops=3)
Buffers: shared hit=69 read=266311
-> Parallel Append (cost=0.07..424654.47 rows=3093927 width=52) (actual time=35.803..1527.322 rows=3307294 loops=3)
Buffers: shared hit=69 read=266311
-> Parallel Bitmap Heap Scan on _hyper_31_1896_chunk (cost=332.12..9051.87 rows=2650 width=52) (actual time=4.441..50.336 rows=8327 loops=1)
Recheck Cond: ((account IN (@accounts)) AND (panel = @panel) AND (hour <= @dateTo) AND (hour >= @dateFrom) AND (hour <= @dateTo))
Buffers: shared hit=69 read=4548
-> Bitmap Index Scan on _hyper_31_1896_chunk_idx_increments_hourly_account_panel_hour (cost=0.00..330.46 rows=6360 width=0) (actual time=3.526..3.526 rows=8327 loops=1)
Index Cond: ((account IN (@accounts)) AND (panel = @panel) AND (hour <= @dateTo) AND (hour >= @dateFrom) AND (hour <= @dateTo))
Buffers: shared hit=68 read=72
-> Parallel Seq Scan on _hyper_31_36339_chunk (cost=0.07..18447.66 rows=142543 width=52) (actual time=40.821..170.570 rows=438028 loops=1)
Filter: ((hour <= @dateTo) AND (hour >= @dateFrom) AND (hour <= @dateTo) AND (panel = @panel) AND (account IN (@accounts)))
Rows Removed by Filter: 128429
Buffers: shared read=12547
-> Parallel Seq Scan on _hyper_31_36303_chunk (cost=0.07..18240.18 rows=116685 width=52) (actual time=35.390..224.223 rows=386122 loops=1)
Filter: ((hour <= @dateTo) AND (hour >= @dateFrom) AND (hour <= @dateTo) AND (panel = @panel) AND (account IN (@accounts)))
Rows Removed by Filter: 137089
Buffers: shared read=12790
-> Parallel Seq Scan on _hyper_31_36157_chunk (cost=0.07..17848.12 rows=130035 width=52) (actual time=0.095..190.556 rows=414327 loops=1)
Filter: ((hour <= @dateTo) AND (hour >= @dateFrom) AND (hour <= @dateTo) AND (panel = @panel) AND (account IN (@accounts)))
Rows Removed by Filter: 133454
Buffers: shared read=12142
-> Parallel Seq Scan on _hyper_31_2316_chunk (cost=0.07..17668.40 rows=130579 width=52) (actual time=0.115..243.061 rows=411093 loops=1)
Filter: ((hour <= @dateTo) AND (hour >= @dateFrom) AND (hour <= @dateTo) AND (panel = @panel) AND (account IN (@accounts)))
Rows Removed by Filter: 131147
Buffers: shared read=12020
-> Parallel Seq Scan on _hyper_31_1949_chunk (cost=0.07..16461.61 rows=116418 width=52) (actual time=0.102..196.506 rows=374217 loops=1)
Filter: ((hour <= @dateTo) AND (hour >= @dateFrom) AND (hour <= @dateTo) AND (panel = @panel) AND (account IN (@accounts)))
Rows Removed by Filter: 130411
Buffers: shared read=11205
-> Parallel Seq Scan on _hyper_31_2257_chunk (cost=0.07..16372.63 rows=126822 width=52) (actual time=0.108..152.444 rows=390467 loops=1)
Filter: ((hour <= @dateTo) AND (hour >= @dateFrom) AND (hour <= @dateTo) AND (panel = @panel) AND (account IN (@accounts)))
Rows Removed by Filter: 112339
Buffers: shared read=11135
-> Parallel Seq Scan on _hyper_31_36319_chunk (cost=0.07..16353.71 rows=127868 width=52) (actual time=10.421..146.907 rows=391811 loops=1)
Filter: ((hour <= @dateTo) AND (hour >= @dateFrom) AND (hour <= @dateTo) AND (panel = @panel) AND (account IN (@accounts)))
Rows Removed by Filter: 110235
Buffers: shared read=11124
-> Parallel Seq Scan on _hyper_31_36332_chunk (cost=0.07..15901.71 rows=122420 width=52) (actual time=0.091..145.319 rows=376847 loops=1)
Filter: ((hour <= @dateTo) AND (hour >= @dateFrom) AND (hour <= @dateTo) AND (panel = @panel) AND (account IN (@accounts)))
Rows Removed by Filter: 111087
Buffers: shared read=10819
-> Parallel Seq Scan on _hyper_31_2228_chunk (cost=0.07..15432.48 rows=113283 width=52) (actual time=0.104..137.971 rows=359425 loops=1)
Filter: ((hour <= @dateTo) AND (hour >= @dateFrom) AND (hour <= @dateTo) AND (panel = @panel) AND (account IN (@accounts)))
Rows Removed by Filter: 114183
Buffers: shared read=10499
-> Parallel Seq Scan on _hyper_31_2244_chunk (cost=0.07..14983.24 rows=107215 width=52) (actual time=0.086..127.127 rows=345157 loops=1)
Filter: ((hour <= @dateTo) AND (hour >= @dateFrom) AND (hour <= @dateTo) AND (panel = @panel) AND (account IN (@accounts)))
Rows Removed by Filter: 115276
Buffers: shared read=10187
-> Parallel Seq Scan on _hyper_31_1933_chunk (cost=0.07..14959.30 rows=97879 width=52) (actual time=0.091..130.737 rows=327551 loops=1)
Filter: ((hour <= @dateTo) AND (hour >= @dateFrom) AND (hour <= @dateTo) AND (panel = @panel) AND (account IN (@accounts)))
Rows Removed by Filter: 131160
Buffers: shared read=10181
-> Parallel Seq Scan on _hyper_31_2302_chunk (cost=0.07..14847.71 rows=90985 width=52) (actual time=0.092..128.581 rows=314478 loops=1)
Filter: ((hour <= @dateTo) AND (hour >= @dateFrom) AND (hour <= @dateTo) AND (panel = @panel) AND (account IN (@accounts)))
Rows Removed by Filter: 140432
Buffers: shared read=10109
-> Parallel Seq Scan on _hyper_31_36210_chunk (cost=0.07..14514.97 rows=133158 width=52) (actual time=0.103..137.167 rows=442011 loops=1)
Filter: ((hour <= @dateTo) AND (hour >= @dateFrom) AND (hour <= @dateTo) AND (panel = @panel) AND (account IN (@accounts)))
Rows Removed by Filter: 169980
Buffers: shared read=8140
-> Parallel Seq Scan on _hyper_31_36295_chunk (cost=0.07..14237.15 rows=90384 width=52) (actual time=0.097..128.323 rows=308190 loops=1)
Filter: ((hour <= @dateTo) AND (hour >= @dateFrom) AND (hour <= @dateTo) AND (panel = @panel) AND (account IN (@accounts)))
Rows Removed by Filter: 128042
Buffers: shared read=9693
-> Parallel Seq Scan on _hyper_31_1915_chunk (cost=0.07..13849.28 rows=102997 width=52) (actual time=2.772..132.984 rows=324362 loops=1)
Filter: ((hour <= @dateTo) AND (hour >= @dateFrom) AND (hour <= @dateTo) AND (panel = @panel) AND (account IN (@accounts)))
Rows Removed by Filter: 100651
Buffers: shared read=9422
-> Parallel Seq Scan on _hyper_31_36257_chunk (cost=0.07..13703.45 rows=93299 width=52) (actual time=0.094..120.887 rows=312214 loops=1)
Filter: ((hour <= @dateTo) AND (hour >= @dateFrom) AND (hour <= @dateTo) AND (panel = @panel) AND (account IN (@accounts)))
Rows Removed by Filter: 125871
Buffers: shared read=9140
-> Parallel Seq Scan on _hyper_31_36355_chunk (cost=0.07..13381.85 rows=99465 width=52) (actual time=8.350..36.921 rows=104259 loops=3)
Filter: ((hour <= @dateTo) AND (hour >= @dateFrom) AND (hour <= @dateTo) AND (panel = @panel) AND (account IN (@accounts)))
Rows Removed by Filter: 32758
Buffers: shared read=9100
-> Parallel Seq Scan on _hyper_31_12600_chunk (cost=0.07..13160.02 rows=126266 width=52) (actual time=0.068..59.499 rows=196851 loops=2)
Filter: ((hour <= @dateTo) AND (hour >= @dateFrom) AND (hour <= @dateTo) AND (panel = @panel) AND (account IN (@accounts)))
Rows Removed by Filter: 60091
Buffers: shared read=7807
-> Parallel Seq Scan on _hyper_31_36228_chunk (cost=0.07..12921.21 rows=127375 width=52) (actual time=0.048..114.582 rows=405971 loops=1)
Filter: ((hour <= @dateTo) AND (hour >= @dateFrom) AND (hour <= @dateTo) AND (panel = @panel) AND (account IN (@accounts)))
Rows Removed by Filter: 128667
Buffers: shared read=7352
-> Parallel Seq Scan on _hyper_31_36170_chunk (cost=0.07..12575.53 rows=91365 width=52) (actual time=0.053..92.237 rows=289505 loops=1)
Filter: ((hour <= @dateTo) AND (hour >= @dateFrom) AND (hour <= @dateTo) AND (panel = @panel) AND (account IN (@accounts)))
Rows Removed by Filter: 96364
Buffers: shared read=8556
-> Parallel Seq Scan on _hyper_31_36363_chunk (cost=0.07..12234.08 rows=83878 width=52) (actual time=7.835..96.320 rows=268298 loops=1)
Filter: ((hour <= @dateTo) AND (hour >= @dateFrom) AND (hour <= @dateTo) AND (panel = @panel) AND (account IN (@accounts)))
Rows Removed by Filter: 102551
Buffers: shared read=8371
-> Parallel Seq Scan on _hyper_31_28044_chunk (cost=0.07..11737.48 rows=122633 width=52) (actual time=0.050..105.124 rows=391215 loops=1)
Filter: ((hour <= @dateTo) AND (hour >= @dateFrom) AND (hour <= @dateTo) AND (panel = @panel) AND (account IN (@accounts)))
Rows Removed by Filter: 127033
Buffers: shared read=6339
-> Parallel Seq Scan on _hyper_31_36270_chunk (cost=0.07..11220.22 rows=77773 width=52) (actual time=0.064..95.740 rows=253545 loops=1)
Filter: ((hour <= @dateTo) AND (hour >= @dateFrom) AND (hour <= @dateTo) AND (panel = @panel) AND (account IN (@accounts)))
Rows Removed by Filter: 90534
Buffers: shared read=7636
-> Parallel Seq Scan on _hyper_31_2270_chunk (cost=0.07..11117.67 rows=80399 width=52) (actual time=0.050..97.276 rows=263489 loops=1)
Filter: ((hour <= @dateTo) AND (hour >= @dateFrom) AND (hour <= @dateTo) AND (panel = @panel) AND (account IN (@accounts)))
Rows Removed by Filter: 96953
Buffers: shared read=7363
-> Parallel Seq Scan on _hyper_31_36200_chunk (cost=0.07..10768.18 rows=113338 width=52) (actual time=0.049..117.442 rows=368933 loops=1)
Filter: ((hour <= @dateTo) AND (hour >= @dateFrom) AND (hour <= @dateTo) AND (panel = @panel) AND (account IN (@accounts)))
Rows Removed by Filter: 131718
Buffers: shared read=5553
-> Parallel Seq Scan on _hyper_31_36278_chunk (cost=0.07..10746.44 rows=73439 width=52) (actual time=10.012..96.947 rows=240283 loops=1)
Filter: ((hour <= @dateTo) AND (hour >= @dateFrom) AND (hour <= @dateTo) AND (panel = @panel) AND (account IN (@accounts)))
Rows Removed by Filter: 89225
Buffers: shared read=7314
-> Parallel Seq Scan on _hyper_31_2284_chunk (cost=0.07..10471.97 rows=78290 width=52) (actual time=0.049..102.863 rows=251833 loops=1)
Filter: ((hour <= @dateTo) AND (hour >= @dateFrom) AND (hour <= @dateTo) AND (panel = @panel) AND (account IN (@accounts)))
Rows Removed by Filter: 83486
Buffers: shared read=6979
-> Parallel Seq Scan on _hyper_31_36239_chunk (cost=0.07..8416.03 rows=88995 width=52) (actual time=0.050..86.212 rows=288858 loops=1)
Filter: ((hour <= @dateTo) AND (hour >= @dateFrom) AND (hour <= @dateTo) AND (panel = @panel) AND (account IN (@accounts)))
Rows Removed by Filter: 102243
Buffers: shared read=4342
-> Parallel Seq Scan on _hyper_31_36182_chunk (cost=0.07..7560.68 rows=85491 width=52) (actual time=62.141..135.266 rows=268846 loops=1)
Filter: ((hour <= @dateTo) AND (hour >= @dateFrom) AND (hour <= @dateTo) AND (panel = @panel) AND (account IN (@accounts)))
Rows Removed by Filter: 82765
Buffers: shared read=3898
-> Hash (cost=94.68..94.68 rows=13 width=18) (actual time=0.272..0.273 rows=20 loops=3)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
Buffers: shared hit=95
-> Bitmap Heap Scan on provider p (cost=44.33..94.68 rows=13 width=18) (actual time=0.259..0.263 rows=20 loops=3)
Recheck Cond: ((provider)::text = ANY ('{'provider1.com', 'provider2.com', 'provider3.com', 'provider4.com', 'provider5.com', 'provider6.com', 'provider7.com', 'provider8.com', 'provider9.com', 'provider10.com'}'::text[]))
Heap Blocks: exact=1
Buffers: shared hit=95
-> Bitmap Index Scan on idx_provider_provider (cost=0.00..44.33 rows=13 width=0) (actual time=0.235..0.235 rows=20 loops=3)
Index Cond: ((provider)::text = ANY ('{'provider1.com', 'provider2.com', 'provider3.com', 'provider4.com', 'provider5.com', 'provider6.com', 'provider7.com', 'provider8.com', 'provider9.com', 'provider10.com'}'::text[]))
Buffers: shared hit=92
Planning:
Buffers: shared hit=509 read=20
Planning Time: 7.770 ms
JIT:
Functions: 252
Options: Inlining false, Optimization false, Expressions true, Deforming true
Timing: Generation 23.107 ms, Inlining 0.000 ms, Optimization 10.870 ms, Emission 200.817 ms, Total 234.793 ms
Execution Time: 4964.362 ms