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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 





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
 

[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux