explain.depesz.com

PostgreSQL's explain analyze made readable

Result: WtFf

Settings
# exclusive inclusive rows x rows loops node
1. 0.372 108.191 ↑ 168.0 14 1

Unique (cost=3,474,699.54..3,475,222.86 rows=2,352 width=5,760) (actual time=107.805..108.191 rows=14 loops=1)

2. 0.149 107.819 ↑ 168.0 14 1

Sort (cost=3,474,699.54..3,474,705.42 rows=2,352 width=5,760) (actual time=107.804..107.819 rows=14 loops=1)

  • Sort Key: offerings_offering.id DESC, offerings_offering.created_at, offerings_offering.archived_at, offerings_offering.name, offerings_offering.external_id, offerings_offering.discount_type, offerings_offering.flat_discount_amount, offerings_offer (...)
  • Sort Method: quicksort Memory: 53kB
3. 43.163 107.670 ↑ 168.0 14 1

GroupAggregate (cost=3,269,829.70..3,468,754.34 rows=2,352 width=5,760) (actual time=55.568..107.670 rows=14 loops=1)

  • Group Key: offerings_offering.id, (CASE WHEN ((offerings_offering.exp_date IS NULL) AND (offerings_offering.start_date IS NULL)) THEN true ELSE false END), (CASE WHEN ((offerings_offering.start_time IS NULL) OR (offerings_offering.end_time IS (...)
4. 49.264 64.507 ↑ 22.9 18,992 1

Sort (cost=3,269,829.70..3,270,915.40 rows=434,281 width=5,760) (actual time=55.483..64.507 rows=18,992 loops=1)

  • Sort Key: offerings_offering.id DESC, (CASE WHEN ((offerings_offering.exp_date IS NULL) AND (offerings_offering.start_date IS NULL)) THEN true ELSE false END), (CASE WHEN ((offerings_offering.start_time IS NULL) OR (offerings_offering.e (...)
  • Sort Method: external sort Disk: 28384kB
5. 6.979 15.243 ↑ 22.9 18,992 1

Merge Left Join (cost=316.27..9,579.54 rows=434,281 width=5,760) (actual time=1.307..15.243 rows=18,992 loops=1)

  • Merge Cond: (offerings_offering.id = offerings_offeringusage.offering_id)
6. 0.630 6.980 ↑ 6.7 2,756 1

Merge Left Join (cost=272.82..804.35 rows=18,452 width=5,760) (actual time=1.178..6.980 rows=2,756 loops=1)

  • Merge Cond: (offerings_offering.id = offerings_offering_coupons.offering_id)
7. 0.670 6.347 ↓ 1.5 2,756 1

Merge Left Join (cost=130.28..380.50 rows=1,809 width=5,760) (actual time=1.174..6.347 rows=2,756 loops=1)

  • Merge Cond: (offerings_offering.id = offerings_offering_points_of_sale.offering_id)
8. 3.347 5.646 ↓ 1.5 2,756 1

Nested Loop Left Join (cost=129.08..367.02 rows=1,809 width=5,756) (actual time=1.165..5.646 rows=2,756 loops=1)

  • Join Filter: (offerings_offering.offering_card_id = offerings_offeringcard.id)
  • Rows Removed by Join Filter: 16544
9. 0.717 2.299 ↓ 1.5 2,756 1

Merge Left Join (cost=127.95..174.77 rows=1,809 width=5,050) (actual time=1.147..2.299 rows=2,756 loops=1)

  • Merge Cond: (offerings_offering.id = offerings_offeringshare.offering_id)
10. 0.016 0.432 ↑ 1.4 62 1

Merge Left Join (cost=28.53..48.01 rows=84 width=5,017) (actual time=0.243..0.432 rows=62 loops=1)

  • Merge Cond: (offerings_offering.id = offerings_offering_venues.offering_id)
11. 0.035 0.409 ↑ 1.4 62 1

Merge Left Join (cost=27.47..46.61 rows=84 width=5,013) (actual time=0.235..0.409 rows=62 loops=1)

  • Merge Cond: (offerings_offering.id = offerings_offering_events.offering_id)
12. 0.020 0.260 ↑ 1.4 62 1

Merge Left Join (cost=13.81..32.24 rows=84 width=4,993) (actual time=0.121..0.260 rows=62 loops=1)

  • Merge Cond: (offerings_offering.id = offerings_offering_badges.offering_id)
13. 0.054 0.203 ↓ 4.4 62 1

Nested Loop (cost=5.07..23.23 rows=14 width=4,981) (actual time=0.085..0.203 rows=62 loops=1)

14. 0.034 0.149 ↓ 4.4 62 1

Merge Left Join (cost=5.07..20.91 rows=14 width=697) (actual time=0.078..0.149 rows=62 loops=1)

  • Merge Cond: (offerings_offering.id = offerings_offering_tags.offering_id)
15. 0.042 0.042 ↑ 1.0 14 1

Index Scan Backward using offerings_offering_pkey on offerings_offering (cost=0.14..15.74 rows=14 width=685) (actual time=0.009..0.042 rows=14 loops=1)

  • Filter: ((application_id = 10) AND (archive_revision_id = 2147483647))
  • Rows Removed by Filter: 14
16. 0.023 0.073 ↓ 3.3 69 1

Sort (cost=4.94..4.99 rows=21 width=16) (actual time=0.067..0.073 rows=69 loops=1)

  • Sort Key: offerings_offering_tags.offering_id DESC
  • Sort Method: quicksort Memory: 28kB
17. 0.018 0.050 ↓ 3.3 69 1

Hash Left Join (cost=2.77..4.48 rows=21 width=16) (actual time=0.035..0.050 rows=69 loops=1)

  • Hash Cond: (badges_tag.id = badges_usertag.tag_id)
18. 0.013 0.026 ↑ 1.0 21 1

Hash Right Join (cost=1.47..2.98 rows=21 width=12) (actual time=0.018..0.026 rows=21 loops=1)

  • Hash Cond: (badges_tag.id = offerings_offering_tags.tag_id)
19. 0.002 0.002 ↑ 1.0 22 1

Seq Scan on badges_tag (cost=0.00..1.22 rows=22 width=4) (actual time=0.001..0.002 rows=22 loops=1)

20. 0.005 0.011 ↑ 1.0 21 1

Hash (cost=1.21..1.21 rows=21 width=8) (actual time=0.011..0.011 rows=21 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
21. 0.006 0.006 ↑ 1.0 21 1

Seq Scan on offerings_offering_tags (cost=0.00..1.21 rows=21 width=8) (actual time=0.004..0.006 rows=21 loops=1)

22. 0.002 0.006 ↑ 1.0 13 1

Hash (cost=1.13..1.13 rows=13 width=12) (actual time=0.006..0.006 rows=13 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
23. 0.004 0.004 ↑ 1.0 13 1

Seq Scan on badges_usertag (cost=0.00..1.13 rows=13 width=12) (actual time=0.002..0.004 rows=13 loops=1)

24. 0.000 0.000 ↑ 1.0 1 62

Materialize (cost=0.00..2.14 rows=1 width=4,284) (actual time=0.000..0.000 rows=1 loops=62)

25. 0.004 0.004 ↑ 1.0 1 1

Seq Scan on merchants_application (cost=0.00..2.14 rows=1 width=4,284) (actual time=0.004..0.004 rows=1 loops=1)

  • Filter: (id = 10)
  • Rows Removed by Filter: 10
26. 0.006 0.037 ↑ 4.0 6 1

Sort (cost=8.74..8.80 rows=24 width=16) (actual time=0.035..0.037 rows=6 loops=1)

  • Sort Key: offerings_offering_badges.offering_id DESC
  • Sort Method: quicksort Memory: 25kB
27. 0.002 0.031 ↑ 6.0 4 1

Nested Loop Left Join (cost=1.24..8.19 rows=24 width=16) (actual time=0.015..0.031 rows=4 loops=1)

28. 0.009 0.025 ↑ 1.0 4 1

Hash Right Join (cost=1.09..3.96 rows=4 width=12) (actual time=0.011..0.025 rows=4 loops=1)

  • Hash Cond: (badges_badge.id = offerings_offering_badges.badge_id)
29. 0.012 0.012 ↑ 1.0 60 1

Seq Scan on badges_badge (cost=0.00..2.60 rows=60 width=4) (actual time=0.002..0.012 rows=60 loops=1)

30. 0.003 0.004 ↑ 1.0 4 1

Hash (cost=1.04..1.04 rows=4 width=8) (actual time=0.004..0.004 rows=4 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
31. 0.001 0.001 ↑ 1.0 4 1

Seq Scan on offerings_offering_badges (cost=0.00..1.04 rows=4 width=8) (actual time=0.001..0.001 rows=4 loops=1)

32. 0.004 0.004 ↓ 0.0 0 4

Index Scan using badges_userbadge_badge_id_080c0754 on badges_userbadge (cost=0.15..0.85 rows=21 width=12) (actual time=0.001..0.001 rows=0 loops=4)

  • Index Cond: (badges_badge.id = badge_id)
33. 0.007 0.114 ↓ 2.0 22 1

Sort (cost=13.66..13.69 rows=11 width=24) (actual time=0.113..0.114 rows=22 loops=1)

  • Sort Key: offerings_offering_events.offering_id DESC
  • Sort Method: quicksort Memory: 25kB
34. 0.069 0.107 ↑ 1.0 11 1

Hash Right Join (cost=1.25..13.47 rows=11 width=24) (actual time=0.028..0.107 rows=11 loops=1)

  • Hash Cond: (venues_event.id = offerings_offering_events.event_id)
35. 0.033 0.033 ↑ 1.0 299 1

Seq Scan on venues_event (cost=0.00..10.99 rows=299 width=20) (actual time=0.006..0.033 rows=299 loops=1)

36. 0.003 0.005 ↑ 1.0 11 1

Hash (cost=1.11..1.11 rows=11 width=8) (actual time=0.005..0.005 rows=11 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
37. 0.002 0.002 ↑ 1.0 11 1

Seq Scan on offerings_offering_events (cost=0.00..1.11 rows=11 width=8) (actual time=0.001..0.002 rows=11 loops=1)

38. 0.006 0.007 ↑ 1.0 3 1

Sort (cost=1.05..1.06 rows=3 width=8) (actual time=0.006..0.007 rows=3 loops=1)

  • Sort Key: offerings_offering_venues.offering_id DESC
  • Sort Method: quicksort Memory: 25kB
39. 0.001 0.001 ↑ 1.0 3 1

Seq Scan on offerings_offering_venues (cost=0.00..1.03 rows=3 width=8) (actual time=0.001..0.001 rows=3 loops=1)

40. 0.338 1.150 ↓ 4.5 2,740 1

Sort (cost=99.42..100.93 rows=603 width=37) (actual time=0.903..1.150 rows=2,740 loops=1)

  • Sort Key: offerings_offeringshare.offering_id DESC
  • Sort Method: quicksort Memory: 72kB
41. 0.115 0.812 ↓ 1.0 611 1

Hash Left Join (cost=59.25..71.57 rows=603 width=37) (actual time=0.614..0.812 rows=611 loops=1)

  • Hash Cond: (t15.id = t18.app_user_id)
42. 0.118 0.693 ↑ 1.0 603 1

Hash Right Join (cost=57.96..67.95 rows=603 width=37) (actual time=0.602..0.693 rows=603 loops=1)

  • Hash Cond: (t17.app_user_id = t15.id)
43. 0.018 0.018 ↑ 1.0 361 1

Seq Scan on badges_userbadge t17 (cost=0.00..6.61 rows=361 width=8) (actual time=0.002..0.018 rows=361 loops=1)

44. 0.123 0.557 ↑ 1.0 603 1

Hash (cost=50.42..50.42 rows=603 width=33) (actual time=0.557..0.557 rows=603 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 49kB
45. 0.124 0.434 ↑ 1.0 603 1

Hash Left Join (cost=30.10..50.42 rows=603 width=33) (actual time=0.264..0.434 rows=603 loops=1)

  • Hash Cond: (offerings_offeringshare.from_user_id = t15.id)
46. 0.058 0.058 ↑ 1.0 603 1

Seq Scan on offerings_offeringshare (cost=0.00..12.03 rows=603 width=29) (actual time=0.004..0.058 rows=603 loops=1)

47. 0.112 0.252 ↑ 1.0 1,071 1

Hash (cost=16.71..16.71 rows=1,071 width=4) (actual time=0.252..0.252 rows=1,071 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 54kB
48. 0.140 0.140 ↑ 1.0 1,071 1

Seq Scan on merchants_applicationuserprofile t15 (cost=0.00..16.71 rows=1,071 width=4) (actual time=0.015..0.140 rows=1,071 loops=1)

49. 0.002 0.004 ↑ 1.0 13 1

Hash (cost=1.13..1.13 rows=13 width=8) (actual time=0.004..0.004 rows=13 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
50. 0.002 0.002 ↑ 1.0 13 1

Seq Scan on badges_usertag t18 (cost=0.00..1.13 rows=13 width=8) (actual time=0.001..0.002 rows=13 loops=1)

51. 0.000 0.000 ↑ 1.0 7 2,756

Materialize (cost=1.14..2.33 rows=7 width=706) (actual time=0.000..0.000 rows=7 loops=2,756)

52. 0.009 0.013 ↑ 1.0 7 1

Hash Left Join (cost=1.14..2.29 rows=7 width=706) (actual time=0.011..0.013 rows=7 loops=1)

  • Hash Cond: (offerings_offeringcard.category_id = offerings_offeringcardcategory.id)
53. 0.001 0.001 ↑ 1.0 7 1

Seq Scan on offerings_offeringcard (cost=0.00..1.07 rows=7 width=480) (actual time=0.001..0.001 rows=7 loops=1)

54. 0.002 0.003 ↑ 1.0 6 1

Hash (cost=1.06..1.06 rows=6 width=226) (actual time=0.003..0.003 rows=6 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
55. 0.001 0.001 ↑ 1.0 6 1

Seq Scan on offerings_offeringcardcategory (cost=0.00..1.06 rows=6 width=226) (actual time=0.001..0.001 rows=6 loops=1)

56. 0.029 0.031 ↓ 25.5 204 1

Sort (cost=1.20..1.22 rows=8 width=8) (actual time=0.009..0.031 rows=204 loops=1)

  • Sort Key: offerings_offering_points_of_sale.offering_id DESC
  • Sort Method: quicksort Memory: 25kB
57. 0.002 0.002 ↑ 1.0 8 1

Seq Scan on offerings_offering_points_of_sale (cost=0.00..1.08 rows=8 width=8) (actual time=0.001..0.002 rows=8 loops=1)

58. 0.003 0.003 ↓ 0.0 0 1

Sort (cost=142.54..147.64 rows=2,040 width=4) (actual time=0.003..0.003 rows=0 loops=1)

  • Sort Key: offerings_offering_coupons.offering_id DESC
  • Sort Method: quicksort Memory: 25kB
59. 0.000 0.000 ↓ 0.0 0 1

Seq Scan on offerings_offering_coupons (cost=0.00..30.40 rows=2,040 width=4) (actual time=0.000..0.000 rows=0 loops=1)

60. 1.223 1.284 ↓ 27.2 17,955 1

Sort (cost=43.44..45.09 rows=659 width=4) (actual time=0.127..1.284 rows=17,955 loops=1)

  • Sort Key: offerings_offeringusage.offering_id DESC
  • Sort Method: quicksort Memory: 55kB
61. 0.061 0.061 ↑ 1.0 659 1

Seq Scan on offerings_offeringusage (cost=0.00..12.59 rows=659 width=4) (actual time=0.003..0.061 rows=659 loops=1)

Planning time : 7.494 ms
Execution time : 113.545 ms