explain.depesz.com

PostgreSQL's explain analyze made readable

Result: j1cP : Optimization for: plan #84Q

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.003 16,251.982 ↓ 6.7 20 1

Limit (cost=30,327.51..30,327.52 rows=3 width=1,961) (actual time=16,251.975..16,251.982 rows=20 loops=1)

2. 0.461 16,251.979 ↓ 6.7 20 1

Sort (cost=30,327.51..30,327.52 rows=3 width=1,961) (actual time=16,251.973..16,251.979 rows=20 loops=1)

  • Sort Key: o.display_weight DESC NULLS LAST, o.publishtime DESC
  • Sort Method: top-N heapsort Memory: 57kB
3. 2.495 16,251.518 ↓ 78.0 234 1

WindowAgg (cost=375.03..30,327.49 rows=3 width=1,961) (actual time=16,251.210..16,251.518 rows=234 loops=1)

4. 4.318 16,249.023 ↓ 78.0 234 1

Nested Loop (cost=375.03..30,327.44 rows=3 width=1,925) (actual time=362.438..16,249.023 rows=234 loops=1)

  • Join Filter: (ca.coupon_id = oc.offer_id)
5. 8.686 15,510.905 ↓ 49.7 6,115 1

Nested Loop Left Join (cost=374.61..30,059.64 rows=123 width=1,933) (actual time=99.777..15,510.905 rows=6,115 loops=1)

6. 14.808 15,471.644 ↓ 49.7 6,115 1

Nested Loop Left Join (cost=374.18..29,695.77 rows=123 width=1,922) (actual time=83.659..15,471.644 rows=6,115 loops=1)

7. 16.789 15,358.996 ↓ 49.7 6,115 1

Nested Loop Left Join (cost=373.76..29,331.91 rows=123 width=1,911) (actual time=73.574..15,358.996 rows=6,115 loops=1)

8. 8.993 6,457.112 ↓ 49.7 6,115 1

Nested Loop Left Join (cost=373.34..29,262.93 rows=123 width=1,476) (actual time=56.725..6,457.112 rows=6,115 loops=1)

9. 10.827 5,995.609 ↓ 49.7 6,115 1

Nested Loop (cost=372.92..29,015.85 rows=123 width=1,468) (actual time=52.788..5,995.609 rows=6,115 loops=1)

10. 17.813 5,948.074 ↓ 49.7 6,118 1

Nested Loop (cost=372.64..28,977.85 rows=123 width=1,476) (actual time=38.243..5,948.074 rows=6,118 loops=1)

11. 26.096 5,679.013 ↓ 49.8 6,128 1

Hash Join (cost=372.36..28,930.37 rows=123 width=1,436) (actual time=38.192..5,679.013 rows=6,128 loops=1)

  • Hash Cond: (o.offer_id = ca.coupon_id)
12. 5,627.288 5,638.635 ↑ 1.1 9,497 1

Bitmap Heap Scan on offer o (cost=190.63..28,709.57 rows=10,090 width=1,428) (actual time=23.813..5,638.635 rows=9,497 loops=1)

  • Recheck Cond: (publishtime > (now() - '90 days'::interval))
  • Heap Blocks: exact=5072
13. 11.347 11.347 ↑ 1.1 9,497 1

Bitmap Index Scan on offer_publishtime_idx (cost=0.00..188.11 rows=10,090 width=0) (actual time=11.347..11.347 rows=9,497 loops=1)

  • Index Cond: (publishtime > (now() - '90 days'::interval))
14. 0.743 14.282 ↑ 1.0 6,477 1

Hash (cost=100.77..100.77 rows=6,477 width=8) (actual time=14.282..14.282 rows=6,477 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 318kB
15. 13.539 13.539 ↑ 1.0 6,477 1

Seq Scan on coupons_active ca (cost=0.00..100.77 rows=6,477 width=8) (actual time=11.075..13.539 rows=6,477 loops=1)

16. 251.248 251.248 ↑ 1.0 1 6,128

Index Scan using merchants_pkey on merchants m (cost=0.28..0.38 rows=1 width=40) (actual time=0.041..0.041 rows=1 loops=6,128)

  • Index Cond: (id = o.merchantid)
17. 36.708 36.708 ↑ 1.0 1 6,118

Index Only Scan using merchants_active_pkey on merchants_active ma (cost=0.28..0.30 rows=1 width=8) (actual time=0.006..0.006 rows=1 loops=6,118)

  • Index Cond: (merchant_id = o.merchantid)
  • Heap Fetches: 6115
18. 452.510 452.510 ↑ 1.0 1 6,115

Index Scan using offer_product_offer_id_idx on offer_product op (cost=0.42..2.00 rows=1 width=16) (actual time=0.074..0.074 rows=1 loops=6,115)

  • Index Cond: (offer_id = o.offer_id)
19. 8,885.095 8,885.095 ↑ 1.0 1 6,115

Index Scan using products_product_id_idx on products p (cost=0.42..0.55 rows=1 width=443) (actual time=1.453..1.453 rows=1 loops=6,115)

  • Index Cond: (product_id = op.product_id)
20. 97.840 97.840 ↑ 1.0 1 6,115

Index Scan using users_user_id_idx on users u (cost=0.42..2.95 rows=1 width=15) (actual time=0.016..0.016 rows=1 loops=6,115)

  • Index Cond: (user_id = o.user_id)
21. 30.575 30.575 ↑ 1.0 1 6,115

Index Scan using users_user_id_idx on users u1 (cost=0.42..2.95 rows=1 width=15) (actual time=0.005..0.005 rows=1 loops=6,115)

  • Index Cond: (user_id = o.creator_user_id)
22. 733.800 733.800 ↓ 0.0 0 6,115

Index Scan using offer_category_offer_id_idx on offer_category oc (cost=0.42..2.16 rows=1 width=8) (actual time=0.120..0.120 rows=0 loops=6,115)

  • Index Cond: (offer_id = o.offer_id)
  • Filter: (category_id = 110)
  • Rows Removed by Filter: 1
Planning time : 922.981 ms
Execution time : 16,252.954 ms