query planner not using index, instead using squential scan

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

 



I am using postgres 12 on AWS RDS
could someone clarify why the LEFT JOIN order_offer_map oom using (order_id) in the below query is using sequential scan instead of using index on order_id which is defined in order_offer_map table. 



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 Join  (cost=39897.37..161872.06 rows=259399 width=494) (actual time=605.767..2060.712 rows=156253 loops=1)
  Hash Cond: (b.city_id = c.city_id)
  ->  Hash Join  (cost=39895.11..78778.16 rows=259399 width=355) (actual time=605.583..789.863 rows=156253 loops=1)
        Hash Cond: (b.restaurant_id = r.restaurant_id)
        ->  Hash Join  (cost=39542.41..77744.20 rows=259399 width=307) (actual time=602.096..738.765 rows=156253 loops=1)
              Hash Cond: (ro.branch_id = b.branch_id)
              ->  Hash Right Join  (cost=38607.06..76127.79 rows=259399 width=225) (actual time=591.342..672.039 rows=156253 loops=1)
                    Hash Cond: (oom.order_id = ro.order_id)
                    ->  Seq Scan on order_offer_map oom  (cost=0.00..34179.09 rows=1273009 width=15) (actual time=0.007..91.121 rows=1273009 loops=1)
                    ->  Hash  (cost=35364.57..35364.57 rows=259399 width=218) (actual time=244.571..244.571 rows=156253 loops=1)
                          Buckets: 262144  Batches: 1  Memory Usage: 29098kB
                          ->  Index Scan using "idx$$_00010001" on restaurant_order ro  (cost=0.56..35364.57 rows=259399 width=218) (actual time=0.033..195.939 rows=156253 loops=1)
                                Index Cond: ((date_time >= '2021-05-27 05:00:00'::timestamp without time zone) AND (date_time <= '2021-06-05 16:38:22.758875+00'::timestamp with time zone))
                                Filter: (order_status_code = 'D'::bpchar)
                                Rows Removed by Filter: 73969
              ->  Hash  (cost=673.49..673.49 rows=20949 width=90) (actual time=10.715..10.715 rows=20949 loops=1)
                    Buckets: 32768  Batches: 1  Memory Usage: 2758kB
                    ->  Seq Scan on branch b  (cost=0.00..673.49 rows=20949 width=90) (actual time=0.006..6.397 rows=20949 loops=1)
        ->  Hash  (cost=245.09..245.09 rows=8609 width=56) (actual time=3.466..3.467 rows=8609 loops=1)
              Buckets: 16384  Batches: 1  Memory Usage: 903kB
              ->  Seq Scan on restaurant r  (cost=0.00..245.09 rows=8609 width=56) (actual time=0.003..2.096 rows=8609 loops=1)
  ->  Hash  (cost=1.56..1.56 rows=56 width=29) (actual time=0.026..0.026 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.007..0.015 rows=56 loops=1)
Planning Time: 1.377 ms
Execution Time: 2071.965 ms

-Ayub

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

  Powered by Linux