1

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.

Some details:

  • 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.

Table:

  • size: 2.7Gb
  • rows: 18.557.143
  • index: CREATE INDEX stats_event_date_idx ON prd.stats USING btree (event_date, provider);

Query:

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)

Explain (analyze,buffers)

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. Setting: set enable_seqscan = off; makes things worse.

Any idea of what could I try to improve this table/query performance? Thanks!

Improve this question
5
  • That is about the performance I would expect, if your gp3 is at baseline configuration. Can you lay out what you expected, and why?
    – jjanes
    Aug 17 at 14:17
  • I remember querying a lot more rows, from MUCH bigger tables (>200gb) on another database engine (Mysql) with <5s response time, went with Postgres because the migration is much easier coming from Redshift. And because of the info collected from reading user experience from Postgres on transactional databases. Was hoping to get faster query times.
    – Alejandro
    Aug 17 at 14:38
  • Unless you are comparing the same query, this is a pretty useless comparison. You can certainly get fast queries on 200GB tables, but probably not when the query needs to process 25% of the table.
    – jjanes
    Aug 17 at 14:59
  • I took the morning to try on MySQL, same machine, data type, and tables...same result, it seems that I will give Apache Druid a go, thanks!
    – Alejandro
    Aug 17 at 18:23
  • Maybe the issue is that MySQL is crowding out PostgreSQL's RAM usage. A 2.7Gb table certainly can be cached in 4GB RAM, but it is also not surprising if it isn't. (And in your case, it isn't according to the plan, but that only accounts for shared_buffer hits, not file cache hits. The performance suggest you aren't getting many file cache hits either as it is only slightly better than baseline reading throughput for gp3.)
    – jjanes
    Aug 17 at 19:09

1 Answer 1

Reset to default
1

You could try a covering index, which would enable Postgres to drop the table scan and do an index scan instead (visiting only the 25% of the table in the date range):

CREATE INDEX stats_event_date_idx ON prd.stats USING btree (event_date, provider, revenue, adsource);
Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.