How to improve performance on a SQL database table that has 60 million rows

Question

I have a table that has 60 million rows in a Postgresql database.

Table is as follows:

id varchar PK
cutomerId Integer
orderId Integer
modified timestamp. 

Now I want to create a summary of customer and number of orders on a particular day for example

SELECT customerId, COUNT(orderId) 
FROM orders o  
WHERE DATE(o.modified) = '2023-04-27' GROUP by customerId

This takes up to 5 minutes to return in SQL query UI.

Is there any way I can speed this up?

here is my explain analyze:

Finalize GroupAggregate  (cost=778305.85..778574.65 rows=1061 width=12) (actual time=43412.506..43435.712 rows=1089 loops=1)
  Group Key: customerId
  Buffers: shared hit=1414708 read=576689
  I/O Timings: read=123468.547
  ->  Gather Merge  (cost=778305.85..778553.43 rows=2122 width=12) (actual time=43412.472..43435.024 rows=3267 loops=1)
        Workers Planned: 2
        Workers Launched: 2
        Buffers: shared hit=1414708 read=576689
        I/O Timings: read=123468.547
        ->  Sort  (cost=777305.82..777308.47 rows=1061 width=12) (actual time=43341.758..43341.892 rows=1089 loops=3)
              Sort Key: customerId
              Sort Method: quicksort  Memory: 100kB
              Worker 0:  Sort Method: quicksort  Memory: 100kB
              Worker 1:  Sort Method: quicksort  Memory: 100kB
              Buffers: shared hit=1414708 read=576689
              I/O Timings: read=123468.547
              ->  Partial HashAggregate  (cost=777241.89..777252.50 rows=1061 width=12) (actual time=43340.904..43341.102 rows=1089 loops=3)
                    Group Key: customerId
                    Buffers: shared hit=1414694 read=576689
                    I/O Timings: read=123468.547
                    ->  Parallel Index Scan using orders_idx on orders o  (cost=0.43..770040.97 rows=1440183 width=8) (actual time=0.205..42743.905 rows=1097188 loops=3)
                          Index Cond: ((modified >= '2022-02-16'::date) AND (modified < '2022-02-17'::date))
                          Buffers: shared hit=1414694 read=576689
                          I/O Timings: read=123468.547
Planning Time: 10.171 ms
Execution Time: 43436.656 ms

Answer 1

You'd need an index on date(modified) for that.

A better solution would be to have an index on modified and change the query to

SELECT customerId, COUNT(orderId) 
FROM orders o  
WHERE o.modified >= '2023-04-27'::date AND o.modified < '2023-04-27'::date + 1
GROUP by customerId;

To improve the speed of the query, you can create a covering index:

CREATE INDEX ON orders (modified) INCLUDE (customerid, orderid);

VACUUM orders;

The VACUUM will update the visibility map, so that the index-only scan can become fast. Make sure that the table is vacuumed often enough.

Answer 2

One possibility is tuning your Postgres server, particularly the memory configuration settings that handle how the database cluster handles indexes. Out of the box, the Postgres config file is set up to allow the software to run on a very minimal computing environment. By understanding how Postgres uses resources you can customize your set-up for you hardware and optimize you performance.

Another, more basic step would be to run the EXPLAIN command on your query and see how your query planner is using your indexes. If you post the output of the EXPLAIN command here you'll likely get more helpful responses.