PostgreSQL query optimization issues

Question

I am trying to optimize a query on a PSQL table with ~14M rows, which normally would take ~10seconds to run:

SELECT r.cust_id as cust_id, r.payable as toBePaid, r.return_code as returnCode, COUNT(r) AS count,
MIN(EXTRACT(EPOCH FROM (r.response_ts - r.request_ts))) / 1.0 AS min,
AVG(EXTRACT(EPOCH FROM (r.response_ts - r.request_ts))) / 1.0 AS avg,
MAX(EXTRACT(EPOCH FROM (r.response_ts - r.request_ts))) / 1.0 AS max
FROM public.request_testing r WHERE r.cust_id LIKE '9999%'
and r.received_ts BETWEEN '2020-01-01' AND '2023-08-10' 
and r.response_ts is not null GROUP BY r.cust_id, r.payable, r.return_code;

Unfortunately, the DB is a managed service and my admin account does not have su privileges, this means:

• server is running psql (13.11, server 12.14)
• I cannot update postgresql.conf
• I cannot create a new tablespace with custom tablespace_option
• The DB comes with preset values for random_page_cost = 2.5 and that's about it

I have done some tweeking on the existing indexes after some previous research and also ran vacuum analyze and also full , to no avail( added text_pattern_ops on cust_id, partitioned indexes based on received_ts).

CREATE INDEX TESTING_custid_idx ON public.request_testing USING btree (cust_id text_pattern_ops);
CREATE INDEX TESTING_request_custid_idx_received_ts_idx2 ON public.request_testing USING btree (cust_id text_pattern_ops , received_ts DESC);
CREATE INDEX TESTING_request_ts_old_idx ON public.request_testing USING btree (cust_id text_pattern_ops , (received_ts::date) DESC NULLS LAST)
WHERE received_ts < '2023-01-01'::timestamp;
CREATE INDEX TESTING_request_ts_2023_idx ON public.request_testing USING btree (cust_id text_pattern_ops , (received_ts::date) DESC NULLS LAST)
WHERE received_ts >= '2023-01-01'::timestamp;

What is even more curious is that index TESTING_request_custid_idx_received_ts_idx2 was specifically created for this query and is not used at all. The planner would rather use 2 indexes in some cases ( TESTING_custid_idx AND TESTING_request_ts_idx ) than using the combined one. I have left out TESTING_request_ts_idx because it was already configured. It an index on the request_ts column, no fancy work.

I can set per transaction params:

SET random_page_cost = 1.5;
SET enable_seqscan = OFF;
SET enable_nestloop =OFF;

Explains look as if the parameters above force the query into an index scan which is suboptimal.

Original planning looks like so:

Finalize GroupAggregate  (cost=518072.76..518081.99 rows=32 width=47) (actual time=9364.008..9378.256 rows=27 loops=1)
   Group Key: cust_id, payable, return_code
   Buffers: shared hit=30695 read=294782
   I/O Timings: read=1767.991
   ->  Gather Merge  (cost=518072.76..518080.23 rows=64 width=71) (actual time=9363.995..9378.155 rows=52 loops=1)
         Workers Planned: 2
         Workers Launched: 2
         Buffers: shared hit=30695 read=294782
         I/O Timings: read=1767.991
         ->  Sort  (cost=517072.74..517072.82 rows=32 width=71) (actual time=9360.143..9360.168 rows=17 loops=3)
               Sort Key: cust_id, payable, return_code
               Sort Method: quicksort  Memory: 27kB
               Worker 0:  Sort Method: quicksort  Memory: 27kB
               Worker 1:  Sort Method: quicksort  Memory: 27kB
               Buffers: shared hit=30695 read=294782
               I/O Timings: read=1767.991
               ->  Partial HashAggregate  (cost=517071.62..517071.94 rows=32 width=71) (actual time=9360.046..9360.078 rows=17 loops=3)
                     Group Key: cust_id, payable, return_code
                     Buffers: shared hit=30649 read=294782
                     I/O Timings: read=1767.991
                     ->  Parallel Seq Scan on request_testing r  (cost=0.00..432926.03 rows=2589095 width=192) (actual time=37.317..5215.367 rows=2070326 loops=3)
                           Filter: ((response_ts IS NOT NULL) AND ((cust_id)::text ~~ '9999%'::text) AND (received_ts >= '2020-01-01 00:00:00'::timestamp without time zone) AND (received_ts <= '2023-08-10 00:00:00'::timestamp without time zone))
                           Rows Removed by Filter: 2844581
                           Buffers: shared hit=30649 read=294782
                           I/O Timings: read=1767.991
 Planning Time: 0.295 ms
 Execution Time: 9378.753 ms
(27 rows)

With the changes:

 Finalize GroupAggregate  (cost=576931.42..576940.64 rows=32 width=47) (actual time=11215.411..11229.533 rows=27 loops=1)
   Group Key: cust_id, payable, return_code
   Buffers: shared hit=22891 read=645798
   I/O Timings: read=4974.493
   ->  Gather Merge  (cost=576931.42..576938.88 rows=64 width=71) (actual time=11215.380..11229.281 rows=39 loops=1)
         Workers Planned: 2
         Workers Launched: 2
         Buffers: shared hit=22891 read=645798
         I/O Timings: read=4974.493
         ->  Sort  (cost=575931.39..575931.47 rows=32 width=71) (actual time=11206.507..11206.552 rows=13 loops=3)
               Sort Key: cust_id, payable, return_code
               Sort Method: quicksort  Memory: 27kB
               Worker 0:  Sort Method: quicksort  Memory: 25kB
               Worker 1:  Sort Method: quicksort  Memory: 26kB
               Buffers: shared hit=22891 read=645798
               I/O Timings: read=4974.493
               ->  Partial HashAggregate  (cost=575930.27..575930.59 rows=32 width=71) (actual time=11206.395..11206.437 rows=13 loops=3)
                     Group Key: cust_id, payable, return_code
                     Buffers: shared hit=22845 read=645798
                     I/O Timings: read=4974.493
                     ->  Parallel Index Scan using testing_cust_id_idx on request_testing r  (cost=0.56..491784.69 rows=2589095 width=192) (actual time=0.152..6948.560 rows=2070326 loops=3)
                           Index Cond: (((cust_id)::text ~>=~ '9999'::text) AND ((cust_id)::text ~<~ '999:'::text))
                           Filter: ((response_ts IS NOT NULL) AND ((cust_id)::text ~~ '9999%'::text) AND (received_ts >= '2020-01-01 00:00:00'::timestamp without time zone) AND (received_ts <= '2023-08-10 00:00:00'::timestamp without time zone))
                           Buffers: shared hit=22845 read=645798
                           I/O Timings: read=4974.493
 Planning Time: 0.572 ms
 Execution Time: 11229.693 ms
(27 rows)

I am scared of performance issues growing larger as the table size increases. What could be improved to make the indexes outperform the seq scan?

Thanks!

Answer 1

You are aggregating 40% of the rows in the table: 2070326 / (2070326 + 2844581). You could multiply the row counts by 3, due to parallel workers, but that would just cancel out when taking the ratio.

Even an ideal index is not going to be very effective when you need to process so many of the rows. You might get some small benefit from an index-only scan, but this would require you to include all the columns used in the query into the index. It would also require you to do count(*), not count(r).

create index on request_testing  (cust_id text_pattern_ops, received_ts, response_ts, request_ts, payable, return_code)

Because both cust_id and received_ts are used in range conditions, the combined index on those columns will not be very effective. Even if each condition was highly selective (which they aren't, otherwise you wouldn't be fetching 40% of the table) whichever column is listed first, the range scan on it will ruin the efficiency of the use of the next column. Only if you can get an index-only scan would the combined index start looking good.

If you really need this to be much more efficient, I think you will need to do something more drastic, like storing precomputed aggregates. If you group by cust_id, payable, return_code, and received_ts truncated to the date, then it should be efficient to filter the partials by cust_id and date, and reaggregate.