Re: slow query with inline function on AWS RDS with RDS 24x large

[Pavel Stehule <pavel.stehule@xxxxxxxxx>]

pá 4. 6. 2021 v 10:07 odesílatel Ayub Khan <ayub.hp@xxxxxxxxx> napsal:

below query is slow even with no data

explain ANALYZE

WITH business AS( SELECT * FROM get_businessday_utc_f()  start_date)
 SELECT ro.order_id,
    ro.date_time,
    round(ro.order_amount, 2) AS order_amount,
    b.branch_id,
    b.branch_name,
    st_x(b.location) AS from_x,
    st_y(b.location) AS from_y,
    b.user_id AS branch_user_id,
    b.contact_info,
    r.restaurant_id,
   c.city_id,
   c.city_name,
   c.city_name_ar,
   st_linefromtext(((((((('LINESTRING('::text || st_x(b.location)) || ' '::text) || st_y(b.location)) || ','::text) || st_x(ro.location_geometry)) || ' '::text) || st_y(ro.location_geometry)) || ')'::text, 28355) AS from_to,
   to_char(ro.date_time, 'HH24:MI'::text) AS order_time,
   ro.customer_comment,
   'N'::text AS is_new_customer,
   ro.picked_up_time,
   ro.driver_assigned_date_time,
   oom.offer_amount,
    oom.offer_type_code AS offer_type,
   ro.uk_vat
 FROM business, restaurant_order ro

 JOIN branch b ON b.branch_id = ro.branch_id
JOIN restaurant r ON r.restaurant_id = b.restaurant_id
 JOIN city c ON c.city_id = b.city_id
LEFT JOIN order_offer_map oom using (order_id)
WHERE ro.date_time >= business.start_date AND ro.date_time<= f_now_immutable_with_tz();

Hash Left Join  (cost=55497.32..5417639.59 rows=5397276 width=291) (actual time=1056.926..1056.934 rows=0 loops=1)
  Hash Cond: (ro.order_id = oom.order_id)
  ->  Hash Join  (cost=6584.61..3674143.44 rows=5397276 width=209) (actual time=1056.926..1056.932 rows=0 loops=1)
        Hash Cond: (ro.branch_id = b.branch_id)
        ->  Nested Loop  (cost=5427.94..3546726.47 rows=19275986 width=108) (actual time=1036.809..1036.810 rows=0 loops=1)
              ->  Function Scan on start_date  (cost=0.00..0.01 rows=1 width=8) (actual time=0.006..0.008 rows=1 loops=1)
              ->  Bitmap Heap Scan on restaurant_order ro  (cost=5427.94..3353966.60 rows=19275986 width=108) (actual time=1036.793..1036.793 rows=0 loops=1)
                    Recheck Cond: ((date_time >= start_date.start_date) AND (date_time <= '2021-06-04 08:05:32.784199+00'::timestamp with time zone))
                    Rows Removed by Index Recheck: 5039976
                    Heap Blocks: lossy=275230
                    ->  Bitmap Index Scan on rest_ord_date_brin  (cost=0.00..608.94 rows=19359111 width=0) (actual time=14.037..14.038 rows=2917120 loops=1)
                          Index Cond: ((date_time >= start_date.start_date) AND (date_time <= '2021-06-04 08:05:32.784199+00'::timestamp with time zone))
        ->  Hash  (cost=1083.35..1083.35 rows=5866 width=109) (actual time=20.106..20.109 rows=20949 loops=1)
              Buckets: 32768 (originally 8192)  Batches: 1 (originally 1)  Memory Usage: 3112kB
              ->  Hash Join  (cost=343.29..1083.35 rows=5866 width=109) (actual time=1.620..14.539 rows=20949 loops=1)
                    Hash Cond: (b.restaurant_id = r.restaurant_id)
                    ->  Hash Join  (cost=2.26..726.91 rows=5866 width=109) (actual time=0.029..8.597 rows=20949 loops=1)
                          Hash Cond: (b.city_id = c.city_id)
                          ->  Seq Scan on branch b  (cost=0.00..668.49 rows=20949 width=88) (actual time=0.004..1.609 rows=20949 loops=1)
                          ->  Hash  (cost=1.56..1.56 rows=56 width=29) (actual time=0.020..0.021 rows=56 loops=1)
                                Buckets: 1024  Batches: 1  Memory Usage: 12kB
                                ->  Seq Scan on city c  (cost=0.00..1.56 rows=56 width=29) (actual time=0.004..0.010 rows=56 loops=1)
                    ->  Hash  (cost=233.42..233.42 rows=8609 width=8) (actual time=1.575..1.575 rows=8609 loops=1)
                          Buckets: 16384  Batches: 1  Memory Usage: 465kB
                          ->  Index Only Scan using "restaurant_idx$$_274b003d" on restaurant r  (cost=0.29..233.42 rows=8609 width=8) (actual time=0.006..0.684 rows=8609 loops=1)
                                Heap Fetches: 0
  ->  Hash  (cost=33000.09..33000.09 rows=1273009 width=13) (never executed)
        ->  Seq Scan on order_offer_map oom  (cost=0.00..33000.09 rows=1273009 width=13) (never executed)
Planning Time: 1.180 ms
Execution Time: 1057.535 ms

could some one explain why it is slow, if I insert 50k records the execution time reaches 20 seconds

 

              ->  Bitmap Heap Scan on restaurant_order ro  (cost=5427.94..3353966.60 rows=19275986 width=108) (actual time=1036.793..1036.793 rows=0 loops=1)
                    Recheck Cond: ((date_time >= start_date.start_date) AND (date_time <= '2021-06-04 08:05:32.784199+00'::timestamp with time zone))
                    Rows Removed by Index Recheck: 5039976
                    Heap Blocks: lossy=275230
                    ->  Bitmap Index Scan on rest_ord_date_brin  (cost=0.00..608.94 rows=19359111 width=0) (actual time=14.037..14.038 rows=2917120 loops=1)
                          Index Cond: ((date_time >= start_date.start_date) AND (date_time <= '2021-06-04 08:05:32.784199+00'::timestamp with time zone))

Looks so the BRIN index is not in good condition. Maybe you need reindex, maybe BRIN index is not good format for your data.

There are lot of data - few millions of rows

Regards

Pavel