I just moved a FrontEnd web from querying a small Redshift Cluster to Postgres, looking for, primarily getting rid of Redshift but mostly to get better performance and user experience from a reporting UI. Right now the query takes more than 20 seconds, that's a lot.
- Service: AWS RDS Postgres 15.3 Class: db.t4g.medium (2vcpu, 4gb ram) + General Purpose SSD (gp3) (upgraded from a db.t4g.micro, don't know if it makes any difference)
- FreeableMemory: 3Gb (so memory is not a constraint right now)
- Concurrent users: the cluster is idle, is just me and this query
- Default Postgres configuration (default parameter group)
- The database has 2 "big" tables, this and a 4.4Gb one, the other 10 table sizes are less than 1Gb.
- size: 2.7Gb
- rows: 18.557.143
CREATE INDEX stats_event_date_idx ON prd.stats USING btree (event_date, provider);
select event_date as datefield, sum(revenue) as value, lower(adsource) as dimension from prd.stats where event_date between '2023-07-16' and '2023-08-15' and provider <> 'flamecorp' group by event_date order by datefield
- Gets 4.5M rows before group by (around 23% of the table rows)
Finalize GroupAggregate (cost=485878.18..485910.26 rows=123 width=36) (actual time=20215.666..20215.807 rows=31 loops=1) Group Key: event_date Buffers: shared hit=16 read=339861 I/O Timings: shared/local read=56251.812 -> Gather Merge (cost=485878.18..485906.88 rows=246 width=36) (actual time=20215.653..20215.741 rows=93 loops=1) Workers Planned: 2 Workers Launched: 2 Buffers: shared hit=16 read=339861 I/O Timings: shared/local read=56251.812 -> Sort (cost=484878.15..484878.46 rows=123 width=36) (actual time=20211.754..20211.757 rows=31 loops=3) Sort Key: event_date Sort Method: quicksort Memory: 28kB Buffers: shared hit=16 read=339861 I/O Timings: shared/local read=56251.812 Worker 0: Sort Method: quicksort Memory: 28kB Worker 1: Sort Method: quicksort Memory: 28kB -> Partial HashAggregate (cost=484872.35..484873.88 rows=123 width=36) (actual time=20211.690..20211.713 rows=31 loops=3) Group Key: event_date Batches: 1 Memory Usage: 48kB Buffers: shared read=339861 I/O Timings: shared/local read=56251.812 Worker 0: Batches: 1 Memory Usage: 48kB Worker 1: Batches: 1 Memory Usage: 48kB -> Parallel Seq Scan on stats (cost=0.00..475231.52 rows=1928165 width=8) (actual time=2.918..19768.610 rows=1524242 loops=3) Filter: ((event_date >= '2023-07-16'::date) AND (event_date <= '2023-08-15'::date) AND ((provider)::text <> 'flamecorp'::text)) Rows Removed by Filter: 4661473 Buffers: shared read=339861 I/O Timings: shared/local read=56251.812 Planning: Buffers: shared hit=42 Planning Time: 0.234 ms Execution Time: 20215.881 ms
Had already run vacuum+analyze.
set enable_seqscan = off; makes things worse.
Any idea of what could I try to improve this table/query performance? Thanks!