Re: slow query

[Ayub Khan <ayub.hp@xxxxxxxxx>]

below is function definition of is_menu_item_available,  for each item based on current day time it returns when it's available or not. The same api works fine on oracle, I am seeing this slowness after migrating the queries to postgresql RDS on AWS


CREATE OR REPLACE FUNCTION is_menu_item_available(
  i_menu_item_id bigint,
  i_check_availability character)
     RETURNS character
     LANGUAGE 'plpgsql'
     COST 100
     VOLATILE PARALLEL UNSAFE
 AS $BODY$
 DECLARE
     l_current_day NUMERIC(1);
     o_time CHARACTER VARYING(10);
     l_current_interval INTERVAL DAY TO SECOND(2);
     item_available_count NUMERIC(10);
 BEGIN
     item_available_count := 0;
 
     BEGIN
         IF i_check_availability = 'Y' THEN
             BEGIN
                 SELECT
                     CASE TO_CHAR(now(), 'fmday')
                         WHEN 'monday' THEN 1
                         WHEN 'tuesday' THEN 2
                         WHEN 'wednesday' THEN 3
                         WHEN 'thursday' THEN 4
                         WHEN 'friday' THEN 5
                         WHEN 'saturday' THEN 6
                         WHEN 'sunday' THEN 7
                     END AS d
                     INTO STRICT l_current_day;
               select (('0 ' ||
    EXTRACT (HOUR FROM ((now() at time zone 'UTC') at time zone '+03:00')) || ':' ||
      EXTRACT (minute FROM ((now() at time zone 'UTC') at time zone '+03:00')) || ':00') :: interval)
                         INTO l_current_interval;
 
             END;
 
             BEGIN
                 SELECT
                     COUNT(*)
                     INTO STRICT item_available_count
                     FROM menu_item_availability
                     WHERE menu_item_id = i_menu_item_id;
 
                 IF item_available_count = 0 THEN
                     RETURN 'Y';
                 ELSE
                     SELECT
     COUNT(*)
     INTO STRICT item_available_count
     FROM menu_item_availability AS mia, availability AS av
     WHERE mia.menu_item_id = i_menu_item_id
     AND mia.availability_id = av.id
     AND date_trunc('DAY',now()) + l_current_interval >= (CASE
         WHEN l_current_interval < '6 hour'::INTERVAL THEN date_trunc('DAY',now()) + av.start_time - (1::NUMERIC || ' days')::INTERVAL
         WHEN l_current_interval >= '6 hour'::INTERVAL THEN date_trunc('DAY',now())+ av.start_time
     END) AND date_trunc('DAY',now()) + l_current_interval <= (CASE
         WHEN l_current_interval < '6 hour'::INTERVAL THEN date_trunc('DAY',now()) + av.end_time - (1::NUMERIC || ' days')::INTERVAL
         WHEN l_current_interval >= '6 hour'::INTERVAL THEN date_trunc('DAY',now()) + av.end_time
     END) AND (av.day_of_week LIKE CONCAT_WS('', '%', l_current_day, '%') OR av.day_of_week LIKE '%0%') AND is_deleted = 0;
                 END IF;
             END;
 
             BEGIN
                 IF item_available_count > 0 THEN
                     RETURN 'Y';
                 ELSE
                     RETURN 'N';
                 END IF;
             END;
         ELSE
             RETURN 'Y';
         END IF;
     END;
 END;
 $BODY$;

On Tue, Jun 8, 2021 at 7:03 PM Ayub Khan <ayub.hp@xxxxxxxxx> wrote:

I checked all the indexes are defined on the tables however the query seems slow, below is the plan. Can any one give any pointers to verify ?

SELECT a.menu_item_id, a.menu_item_name, a.menu_item_category_id, b.menu_item_category_desc, c.menu_item_variant_id, c.menu_item_variant_type_id, c.price, c.size_id, c.parent_menu_item_variant_id, d.menu_item_variant_type_desc, e.size_desc, f.currency_code, a.image, a.mark_id, m.mark_name

 FROM menu_item_category AS b, menu_item_variant AS c, menu_item_variant_type AS d, item_size AS e, restaurant AS f, menu_item AS a

 LEFT OUTER JOIN mark AS m ON (a.mark_id = m.mark_id) WHERE a.menu_item_category_id = b.menu_item_category_id AND a.menu_item_id = c.menu_item_id AND c.menu_item_variant_type_id = d.menu_item_variant_type_id AND d.is_hidden = 'false' AND c.size_id = e.size_id AND a.restaurant_id = f.restaurant_id AND f.restaurant_id = 1528 AND (a.menu_item_category_id = NULL OR NULL IS NULL)

 AND c.menu_item_variant_id = (SELECT min(menu_item_variant_id) FROM menu_item_variant WHERE menu_item_id = a.menu_item_id AND deleted = 'N' limit 1) AND a.active = 'Y'
 AND (CONCAT_WS('', ',', a.hidden_branch_ids, ',') NOT LIKE CONCAT_WS('', '%,4191,%') OR NULL IS NULL)
 AND is_menu_item_available(a.menu_item_id, 'Y') = 'Y'

 ORDER BY a.row_order, menu_item_id;

below is the plan

Sort  (cost=189.27..189.27 rows=1 width=152) (actual time=5.876..5.885 rows=89 loops=1)
"  Sort Key: a.row_order, a.menu_item_id"
  Sort Method: quicksort  Memory: 48kB
  ->  Nested Loop Left Join  (cost=5.19..189.26 rows=1 width=152) (actual time=0.188..5.809 rows=89 loops=1)
        Join Filter: (a.mark_id = m.mark_id)
        Rows Removed by Join Filter: 267
        ->  Nested Loop  (cost=5.19..188.19 rows=1 width=148) (actual time=0.181..5.629 rows=89 loops=1)
              ->  Nested Loop  (cost=4.90..185.88 rows=1 width=152) (actual time=0.174..5.443 rows=89 loops=1)
                    ->  Nested Loop  (cost=4.61..185.57 rows=1 width=144) (actual time=0.168..5.272 rows=89 loops=1)
                          ->  Nested Loop  (cost=4.32..185.25 rows=1 width=136) (actual time=0.162..5.066 rows=89 loops=1)
                                ->  Nested Loop  (cost=0.71..179.62 rows=1 width=99) (actual time=0.137..3.986 rows=89 loops=1)
                                      ->  Index Scan using menu_item_restaurant_id on menu_item a  (cost=0.42..177.31 rows=1 width=87) (actual time=0.130..3.769 rows=89 loops=1)
                                            Index Cond: (restaurant_id = 1528)
"                                            Filter: ((active = 'Y'::bpchar) AND (is_menu_item_available(menu_item_id, 'Y'::bpchar) = 'Y'::bpchar))"
                                            Rows Removed by Filter: 194
                                      ->  Index Scan using menu_item_category_pk on menu_item_category b  (cost=0.29..2.31 rows=1 width=20) (actual time=0.002..0.002 rows=1 loops=89)
                                            Index Cond: (menu_item_category_id = a.menu_item_category_id)
                                ->  Index Scan using menu_item_variant_pk on menu_item_variant c  (cost=3.60..5.62 rows=1 width=45) (actual time=0.002..0.002 rows=1 loops=89)
                                      Index Cond: (menu_item_variant_id = (SubPlan 1))
                                      Filter: (a.menu_item_id = menu_item_id)
                                      SubPlan 1
                                        ->  Limit  (cost=3.17..3.18 rows=1 width=8) (actual time=0.009..0.009 rows=1 loops=89)
                                              ->  Aggregate  (cost=3.17..3.18 rows=1 width=8) (actual time=0.008..0.008 rows=1 loops=89)
                                                    ->  Index Scan using "idx$$_023a0001" on menu_item_variant  (cost=0.43..3.15 rows=8 width=8) (actual time=0.004..0.007 rows=7 loops=89)
                                                          Index Cond: (menu_item_id = a.menu_item_id)
                                                          Filter: (deleted = 'N'::bpchar)
                                                          Rows Removed by Filter: 4
                          ->  Index Scan using menu_item_variant_type_pk on menu_item_variant_type d  (cost=0.29..0.31 rows=1 width=16) (actual time=0.002..0.002 rows=1 loops=89)
                                Index Cond: (menu_item_variant_type_id = c.menu_item_variant_type_id)
                                Filter: ((is_hidden)::text = 'false'::text)
                    ->  Index Scan using size_pk on item_size e  (cost=0.29..0.31 rows=1 width=16) (actual time=0.001..0.001 rows=1 loops=89)
                          Index Cond: (size_id = c.size_id)
              ->  Index Scan using "restaurant_idx$$_274b003d" on restaurant f  (cost=0.29..2.30 rows=1 width=12) (actual time=0.001..0.002 rows=1 loops=89)
                    Index Cond: (restaurant_id = 1528)
        ->  Seq Scan on mark m  (cost=0.00..1.03 rows=3 width=12) (actual time=0.000..0.001 rows=3 loops=89)
Planning Time: 1.510 ms
Execution Time: 5.972 ms

--

--------------------------------------------------------------------

Sun Certified Enterprise Architect 1.5
Sun Certified Java Programmer 1.4
Microsoft Certified Systems Engineer 2000
http://in.linkedin.com/pub/ayub-khan/a/811/b81

mobile:+966-502674604

----------------------------------------------------------------------
It is proved that Hard Work and kowledge will get you close but attitude will get you there. However, it's the Love
of God that will put you over the top!!