explain.depesz.com

PostgreSQL's explain analyze made readable

Result: GKwM

Settings
# exclusive inclusive rows x rows loops node
1. 0.002 178,730.805 ↓ 0.0 0 1

Limit (cost=9,643,800.65..9,643,800.70 rows=20 width=147) (actual time=178,730.805..178,730.805 rows=0 loops=1)

2. 0.017 178,730.803 ↓ 0.0 0 1

Sort (cost=9,643,800.65..9,655,021.27 rows=4,488,246 width=147) (actual time=178,730.803..178,730.803 rows=0 loops=1)

  • Sort Key: couponmanagementtable.couponcreationtimestamp DESC
  • Sort Method: quicksort Memory: 25kB
3. 0.001 178,730.786 ↓ 0.0 0 1

Hash Left Join (cost=4,832,423.77..9,524,370.04 rows=4,488,246 width=147) (actual time=178,730.785..178,730.786 rows=0 loops=1)

  • Hash Cond: (couponmanagementtable.couponid = couponcataloguemapping.couponid)
4. 2.147 178,730.785 ↓ 0.0 0 1

Hash Join (cost=4,832,383.22..9,467,665.37 rows=4,488,246 width=114) (actual time=178,730.785..178,730.785 rows=0 loops=1)

  • Hash Cond: (couponmanagementtable.coupontypeid = coupontypetable.coupontypeid)
  • Join Filter: ((couponstoremapping.storeid = 2,581,195) OR (coupontypetable.coupontype = ANY ('{All,"Some Catalogues"}'::text[])))
  • Rows Removed by Join Filter: 2,335
5. 20,351.341 178,728.626 ↑ 1,922.2 2,335 1

Hash Left Join (cost=4,832,382.17..9,419,696.20 rows=4,488,246 width=82) (actual time=116,710.833..178,728.626 rows=2,335 loops=1)

  • Hash Cond: ((couponmanagementtable.couponid = couponusedmapping.couponid) AND (couponstoremapping.storeid = couponusedmapping.storeid))
  • Filter: (NOT CASE WHEN ((couponmanagementtable.enddate >= ('2019-09-25'::cstring)::timestamp without time zone) AND (couponmanagementtable.numberoftimesvalid >= COALESCE(couponusedmapping.numberoftimesused, 0))) THEN false ELSE true END)
  • Rows Removed by Filter: 487,342
6. 22,232.639 50,578.950 ↑ 18.3 489,677 1

Hash Right Join (cost=160,401.45..3,737,353.90 rows=8,976,491 width=78) (actual time=1,852.410..50,578.950 rows=489,677 loops=1)

  • Hash Cond: (couponstoremapping.couponid = couponmanagementtable.couponid)
7. 26,531.109 26,531.109 ↓ 1.0 125,493,643 1

Seq Scan on couponstoremapping (cost=0.00..2,057,940.04 rows=122,191,304 width=16) (actual time=0.012..26,531.109 rows=125,493,643 loops=1)

8. 300.487 1,815.202 ↓ 1.2 489,677 1

Hash (cost=150,101.38..150,101.38 rows=425,286 width=70) (actual time=1,815.201..1,815.202 rows=489,677 loops=1)

  • Buckets: 524,288 Batches: 2 Memory Usage: 28,736kB
9. 1,514.715 1,514.715 ↓ 1.2 489,677 1

Seq Scan on couponmanagementtable (cost=0.00..150,101.38 rows=425,286 width=70) (actual time=0.015..1,514.715 rows=489,677 loops=1)

  • Filter: ((NOT isshop101coupon) AND (couponstatus = 'active'::text))
  • Rows Removed by Filter: 5,299,470
10. 62,008.824 107,798.335 ↑ 1.0 125,493,532 1

Hash (cost=2,054,260.09..2,054,260.09 rows=125,493,709 width=20) (actual time=107,798.335..107,798.335 rows=125,493,532 loops=1)

  • Buckets: 524,288 Batches: 256 Memory Usage: 30,886kB
11. 45,789.511 45,789.511 ↑ 1.0 125,493,532 1

Seq Scan on couponusedmapping (cost=0.00..2,054,260.09 rows=125,493,709 width=20) (actual time=0.015..45,789.511 rows=125,493,532 loops=1)

12. 0.002 0.012 ↓ 2.5 5 1

Hash (cost=1.02..1.02 rows=2 width=40) (actual time=0.012..0.012 rows=5 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
13. 0.010 0.010 ↓ 2.5 5 1

Seq Scan on coupontypetable (cost=0.00..1.02 rows=2 width=40) (actual time=0.008..0.010 rows=5 loops=1)

14. 0.000 0.000 ↓ 0.0 0

Hash (cost=38.05..38.05 rows=200 width=40) (never executed)

15. 0.000 0.000 ↓ 0.0 0

HashAggregate (cost=33.55..36.05 rows=200 width=40) (never executed)

  • Group Key: couponcataloguemapping.couponid
16. 0.000 0.000 ↓ 0.0 0

Seq Scan on couponcataloguemapping (cost=0.00..25.70 rows=1,570 width=16) (never executed)