explain.depesz.com

A tool for finding a real cause for slow queries.

Result: k1X

options
# exclusive inclusive rows x rows loops node
1. 0.000 0.000 ↓ 0.0
→

Subquery Scan vieweligibleterminaloffers_enterprise (cost=96498724.33..96519744.88 rows=27749 width=30) (actual time=.. rows= loops=)

  • Filter: (vieweligibleterminaloffers_enterprise.incentiveid = ANY ('{14015,14022,14024,14066,14068}'::integer[]))
2. 0.000 0.000 ↓ 0.0
→

Sort (cost=96498724.33..96501527.07 rows=1121096 width=30) (actual time=.. rows= loops=)

  • Sort Key: *SELECT* 1 .locationid, *SELECT* 1 .terminaltypeid, *SELECT* 1 .rewardoptionid, *SELECT* 1 .incentiveid, *SELECT* 1 .sendissuance, *SELECT* 1 .everytod, *SELECT* 1 .employeesonly, *SELECT* 1 .employeesexcluded, *SELECT* 1 .manufacturercoupon
3. 0.000 0.000 ↓ 0.0
→

HashSetOp Except (cost=96295572.88..96386073.92 rows=1121096 width=30) (actual time=.. rows= loops=)

4. 0.000 0.000 ↓ 0.0
→

Append (cost=96295572.88..96360747.95 rows=1125599 width=30) (actual time=.. rows= loops=)

5. 0.000 0.000 ↓ 0.0
→

Subquery Scan *SELECT* 1 (cost=96295572.88..96334811.24 rows=1121096 width=30) (actual time=.. rows= loops=)

6. 0.000 0.000 ↓ 0.0
→

Unique (cost=96295572.88..96323600.28 rows=1121096 width=30) (actual time=.. rows= loops=)

7.          

Initplan (forUnique)
8. 0.000 0.000 ↓ 0.0
→

Seq Scan on terminaltypes (cost=0.00..1.02 rows=1 width=4) (actual time=.. rows= loops=)

  • Filter: anyterminal
9. 0.000 0.000 ↓ 0.0
→

Sort (cost=96295571.86..96298374.60 rows=1121096 width=30) (actual time=.. rows= loops=)

  • Sort Key: l.locationid, tt.terminaltypeid, ro.rewardoptionid, i.incentiveid, i.sendissuance, i.everytod, i.employeesonly, i.employeesexcluded, (COALESCE(i.manufacturercoupon, 0))
10. 0.000 0.000 ↓ 0.0
→

Merge Right Join (cost=860245.21..96182921.45 rows=1121096 width=30) (actual time=.. rows= loops=)

  • Merge Cond: ((lgi.locationgroupid = lg.locationgroupid) AND (lgi.locationid = l.locationid))
  • Filter: ((lgi.pkid IS NOT NULL) OR ((lg.alllocations = B'1':: bit ) AND (NOT (alternatives: SubPlan 1 or hashed SubPlan 2))))
11. 0.000 0.000 ↓ 0.0
→

Index Scan using locgroupitems_idx on locgroupitems lgi (cost=0.00..51128.84 rows=1076495 width=12) (actual time=.. rows= loops=)

12. 0.000 0.000 ↓ 0.0
→

Sort (cost=860245.21..863047.95 rows=1121096 width=40) (actual time=.. rows= loops=)

  • Sort Key: lg.locationgroupid, l.locationid
13. 0.000 0.000 ↓ 0.0
→

Nested Loop (cost=754.33..747594.81 rows=1121096 width=40) (actual time=.. rows= loops=)

  • Join Filter: (((i.eligibilitystartdate)::timestamp with time zone <= timestamptz(date(timezone((l.timezone)::text, now())))) AND ((i.eligibilityenddate)::timestamp with time zone >= timestamptz(date(timezone((l.timezone)::text, now())))))
14. 0.000 0.000 ↓ 0.0
→

Nested Loop (cost=625.53..242904.86 rows=2241 width=52) (actual time=.. rows= loops=)

15. 0.000 0.000 ↓ 0.0
→

Nested Loop (cost=625.53..241952.04 rows=2241 width=56) (actual time=.. rows= loops=)

  • Join Filter: ((tt.terminaltypeid = itt.terminaltypeid) OR ((tt.specificpromosonly = 0) AND (itt.terminaltypeid = $4)))
16. 0.000 0.000 ↓ 0.0
→

Nested Loop (cost=624.51..241889.39 rows=2241 width=56) (actual time=.. rows= loops=)

17. 0.000 0.000 ↓ 0.0
→

Merge Join (cost=624.51..721.70 rows=4459 width=22) (actual time=.. rows= loops=)

  • Merge Cond: (lg.locationgroupid = ol.locationgroupid)
18. 0.000 0.000 ↓ 0.0
→

Index Scan using locationgroups_pkey on locationgroups lg (cost=0.00..29.15 rows=460 width=10) (actual time=.. rows= loops=)

19. 0.000 0.000 ↓ 0.0
→

Sort (cost=624.51..635.66 rows=4459 width=16) (actual time=.. rows= loops=)

  • Sort Key: ol.locationgroupid
20. 0.000 0.000 ↓ 0.0
→

Hash Join (cost=125.26..354.24 rows=4459 width=16) (actual time=.. rows= loops=)

  • Hash Cond: (ol.offerid = itt.incentiveid)
21. 0.000 0.000 ↓ 0.0
→

Seq Scan on offerlocations ol (cost=0.00..144.81 rows=5277 width=8) (actual time=.. rows= loops=)

  • Filter: (excluded = B'0':: bit )
22. 0.000 0.000 ↓ 0.0
→

Hash (cost=69.56..69.56 rows=4456 width=8) (actual time=.. rows= loops=)

23. 0.000 0.000 ↓ 0.0
→

Seq Scan on incentiveterminals itt (cost=0.00..69.56 rows=4456 width=8) (actual time=.. rows= loops=)

24. 0.000 0.000 ↓ 0.0
→

Index Scan using incentives_pkey on incentives i (cost=0.00..54.07 rows=1 width=34) (actual time=.. rows= loops=)

  • Index Cond: (i.incentiveid = itt.incentiveid)
  • Filter: ((COALESCE(i.everydow, 1) = 1) OR (alternatives: SubPlan 4 or hashed SubPlan 5))
25.          

SubPlan (forIndex Scan)
26. 0.000 0.000 ↓ 0.0
→

Seq Scan on incentivedow idw (cost=0.00..53.65 rows=1 width=0) (actual time=.. rows= loops=)

  • Filter: ((incentiveid = $0) AND ((dowid)::double precision = date_part('dow'::text, now())))
27. 0.000 0.000 ↓ 0.0
→

Seq Scan on incentivedow idw (cost=0.00..48.80 rows=10 width=4) (actual time=.. rows= loops=)

  • Filter: ((dowid)::double precision = date_part('dow'::text, now()))
28. 0.000 0.000 ↓ 0.0
→

Materialize (cost=1.02..1.03 rows=1 width=8) (actual time=.. rows= loops=)

29. 0.000 0.000 ↓ 0.0
→

Seq Scan on terminaltypes tt (cost=0.00..1.02 rows=1 width=8) (actual time=.. rows= loops=)

  • Filter: (NOT anyterminal)
30. 0.000 0.000 ↓ 0.0
→

Index Scan using ix_rewardoptions_inc_1 on rewardoptions ro (cost=0.00..0.41 rows=1 width=8) (actual time=.. rows= loops=)

  • Index Cond: (ro.incentiveid = i.incentiveid)
31. 0.000 0.000 ↓ 0.0
→

Materialize (cost=128.79..173.82 rows=4503 width=12) (actual time=.. rows= loops=)

32. 0.000 0.000 ↓ 0.0
→

Seq Scan on locations l (cost=0.00..124.29 rows=4503 width=12) (actual time=.. rows= loops=)

  • Filter: (NOT testinglocation)
33.          

SubPlan (forMerge Right Join)
34. 0.000 0.000 ↓ 0.0
→

Nested Loop (cost=0.00..172.38 rows=1 width=0) (actual time=.. rows= loops=)

35. 0.000 0.000 ↓ 0.0
→

Seq Scan on offerlocations offl (cost=0.00..163.97 rows=1 width=4) (actual time=.. rows= loops=)

  • Filter: ((offerid = $0) AND (excluded = B'1':: bit ))
36. 0.000 0.000 ↓ 0.0
→

Index Scan using locgroupitems_idx on locgroupitems locgi (cost=0.00..8.39 rows=1 width=4) (actual time=.. rows= loops=)

  • Index Cond: ((locgi.locationgroupid = offl.locationgroupid) AND (locgi.locationid = $1))
37. 0.000 0.000 ↓ 0.0
→

Hash Join (cost=30040.14..111039.63 rows=6264021 width=8) (actual time=.. rows= loops=)

  • Hash Cond: (offl.locationgroupid = locgi.locationgroupid)
38. 0.000 0.000 ↓ 0.0
→

Seq Scan on offerlocations offl (cost=0.00..144.81 rows=2388 width=8) (actual time=.. rows= loops=)

  • Filter: (excluded = B'1':: bit )
39. 0.000 0.000 ↓ 0.0
→

Hash (cost=16583.95..16583.95 rows=1076495 width=8) (actual time=.. rows= loops=)

40. 0.000 0.000 ↓ 0.0
→

Seq Scan on locgroupitems locgi (cost=0.00..16583.95 rows=1076495 width=8) (actual time=.. rows= loops=)

41. 0.000 0.000 ↓ 0.0
→

Subquery Scan *SELECT* 2 (cost=25779.10..25936.70 rows=4503 width=30) (actual time=.. rows= loops=)

42. 0.000 0.000 ↓ 0.0
→

Unique (cost=25779.10..25891.67 rows=4503 width=30) (actual time=.. rows= loops=)

43. 0.000 0.000 ↓ 0.0
→

Sort (cost=25779.10..25790.36 rows=4503 width=30) (actual time=.. rows= loops=)

  • Sort Key: l.locationid, tt.terminaltypeid, ro.rewardoptionid, i.incentiveid, i.sendissuance, i.everytod, i.employeesonly, i.employeesexcluded, (COALESCE(i.manufacturercoupon, 0))
44. 0.000 0.000 ↓ 0.0
→

Nested Loop Left Join (cost=0.00..25505.84 rows=4503 width=30) (actual time=.. rows= loops=)

45. 0.000 0.000 ↓ 0.0
→

Nested Loop (cost=0.00..478.23 rows=4503 width=34) (actual time=.. rows= loops=)

46. 0.000 0.000 ↓ 0.0
→

Nested Loop (cost=0.00..308.91 rows=1 width=30) (actual time=.. rows= loops=)

47. 0.000 0.000 ↓ 0.0
→

Nested Loop (cost=0.00..300.63 rows=1 width=34) (actual time=.. rows= loops=)

  • Join Filter: (itt.terminaltypeid = tt.terminaltypeid)
48. 0.000 0.000 ↓ 0.0
→

Nested Loop (cost=0.00..299.59 rows=1 width=34) (actual time=.. rows= loops=)

49. 0.000 0.000 ↓ 0.0
→

Nested Loop (cost=0.00..299.30 rows=1 width=34) (actual time=.. rows= loops=)

  • Join Filter: (i.incentiveid = ol.offerid)
50. 0.000 0.000 ↓ 0.0
→

Nested Loop (cost=0.00..77.84 rows=1 width=26) (actual time=.. rows= loops=)

51. 0.000 0.000 ↓ 0.0
→

Seq Scan on incentiveterminals itt (cost=0.00..69.56 rows=1 width=8) (actual time=.. rows= loops=)

  • Filter: excluded
52. 0.000 0.000 ↓ 0.0
→

Index Scan using incentives_pkey on incentives i (cost=0.00..8.27 rows=1 width=18) (actual time=.. rows= loops=)

  • Index Cond: (i.incentiveid = itt.incentiveid)
53. 0.000 0.000 ↓ 0.0
→

Seq Scan on offerlocations ol (cost=0.00..125.65 rows=7665 width=8) (actual time=.. rows= loops=)

54. 0.000 0.000 ↓ 0.0
→

Index Scan using locationgroups_pkey on locationgroups lg (cost=0.00..0.27 rows=1 width=4) (actual time=.. rows= loops=)

  • Index Cond: (lg.locationgroupid = ol.locationgroupid)
55. 0.000 0.000 ↓ 0.0
→

Seq Scan on terminaltypes tt (cost=0.00..1.02 rows=2 width=4) (actual time=.. rows= loops=)

56. 0.000 0.000 ↓ 0.0
→

Index Scan using ix_rewardoptions_inc_1 on rewardoptions ro (cost=0.00..8.27 rows=1 width=8) (actual time=.. rows= loops=)

  • Index Cond: (ro.incentiveid = i.incentiveid)
57. 0.000 0.000 ↓ 0.0
→

Seq Scan on locations l (cost=0.00..124.29 rows=4503 width=4) (actual time=.. rows= loops=)

  • Filter: (NOT l.testinglocation)
58. 0.000 0.000 ↓ 0.0
→

Index Scan using locgroupitems_idx on locgroupitems lgi (cost=0.00..5.54 rows=1 width=8) (actual time=.. rows= loops=)

  • Index Cond: ((lg.locationgroupid = lgi.locationgroupid) AND (l.locationid = lgi.locationid))