Re: slow query with inline function on AWS RDS with RDS 24x large - Mailing list pgsql-performance

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