explain.depesz.com

PostgreSQL's explain analyze made readable

Result: JrtG

Settings
# exclusive inclusive rows x rows loops node
1. 0.218 8,862.579 ↑ 12.0 14 1

Unique (cost=185,645.04..185,682.42 rows=168 width=6,338) (actual time=8,862.354..8,862.579 rows=14 loops=1)

2. 0.139 8,862.361 ↑ 12.0 14 1

Sort (cost=185,645.04..185,645.46 rows=168 width=6,338) (actual time=8,862.352..8,862.361 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. 3,572.270 8,862.222 ↑ 12.0 14 1

GroupAggregate (cost=171,838.63..185,638.83 rows=168 width=6,338) (actual time=4,585.940..8,862.222 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. 4,199.415 5,289.952 ↓ 48.7 1,507,086 1

Sort (cost=171,838.63..171,916.06 rows=30,973 width=6,338) (actual time=4,585.830..5,289.952 rows=1,507,086 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: 2244504kB
5. 559.530 1,090.537 ↓ 48.7 1,507,086 1

Merge Left Join (cost=254.88..989.24 rows=30,973 width=6,338) (actual time=0.886..1,090.537 rows=1,507,086 loops=1)

  • Merge Cond: (offerings_offering.id = offerings_offeringusage.offering_id)
6. 42.009 429.935 ↓ 136.4 179,511 1

Merge Left Join (cost=211.43..323.04 rows=1,316 width=6,338) (actual time=0.753..429.935 rows=179,511 loops=1)

  • Merge Cond: (offerings_offering.id = offerings_offering_coupons.offering_id)
7. 45.603 387.922 ↓ 1,391.6 179,511 1

Merge Left Join (cost=68.89..156.82 rows=129 width=6,338) (actual time=0.749..387.922 rows=179,511 loops=1)

  • Merge Cond: (offerings_offering.id = offerings_offering_points_of_sale.offering_id)
8. 44.464 342.117 ↓ 1,391.6 179,511 1

Merge Left Join (cost=67.69..154.74 rows=129 width=6,334) (actual time=0.742..342.117 rows=179,511 loops=1)

  • Merge Cond: (offerings_offering.id = offerings_offering_venues.offering_id)
9. 217.190 297.648 ↓ 1,391.6 179,511 1

Nested Loop Left Join (cost=66.64..153.15 rows=129 width=6,330) (actual time=0.736..297.648 rows=179,511 loops=1)

  • Join Filter: (offerings_offering.offering_card_id = offerings_offeringcard.id)
  • Rows Removed by Join Filter: 1077633
10. 48.440 80.458 ↓ 1,391.6 179,511 1

Merge Left Join (cost=65.50..137.30 rows=129 width=5,624) (actual time=0.715..80.458 rows=179,511 loops=1)

  • Merge Cond: (offerings_offering.id = offerings_offeringshare.offering_id)
11. 3.222 15.698 ↓ 779.0 4,674 1

Nested Loop Left Join (cost=25.62..93.10 rows=6 width=5,599) (actual time=0.559..15.698 rows=4,674 loops=1)

12. 3.793 12.476 ↓ 779.0 4,674 1

Nested Loop Left Join (cost=25.35..77.04 rows=6 width=5,599) (actual time=0.558..12.476 rows=4,674 loops=1)

  • Join Filter: (offerings_offering.id = offerings_offering_events.offering_id)
  • Rows Removed by Join Filter: 50170
13. 2.260 4.009 ↓ 779.0 4,674 1

Nested Loop Left Join (cost=24.10..62.55 rows=6 width=5,579) (actual time=0.490..4.009 rows=4,674 loops=1)

  • Join Filter: (badges_tag.id = badges_usertag.tag_id)
  • Rows Removed by Join Filter: 3924
14. 0.026 0.643 ↓ 2.3 14 1

Nested Loop Left Join (cost=1.79..24.68 rows=6 width=5,571) (actual time=0.058..0.643 rows=14 loops=1)

15. 0.012 0.617 ↓ 2.3 14 1

Nested Loop Left Join (cost=1.51..22.12 rows=6 width=5,567) (actual time=0.056..0.617 rows=14 loops=1)

16. 0.015 0.591 ↓ 14.0 14 1

Nested Loop Left Join (cost=1.36..21.06 rows=1 width=5,563) (actual time=0.054..0.591 rows=14 loops=1)

17. 0.047 0.506 ↓ 14.0 14 1

Nested Loop Left Join (cost=1.23..20.32 rows=1 width=5,559) (actual time=0.051..0.506 rows=14 loops=1)

  • Join Filter: (offerings_offering.id = offerings_offering_tags.offering_id)
  • Rows Removed by Join Filter: 282
18. 0.030 0.417 ↓ 14.0 14 1

Nested Loop (cost=1.23..18.85 rows=1 width=5,555) (actual time=0.046..0.417 rows=14 loops=1)

19. 0.121 0.345 ↓ 14.0 14 1

Nested Loop Left Join (cost=1.23..16.70 rows=1 width=1,271) (actual time=0.040..0.345 rows=14 loops=1)

  • Join Filter: (offerings_offering.id = offerings_offering_badges.offering_id)
  • Rows Removed by Join Filter: 55
  • -> Index Scan Backward using offerings_offering_pkey on offerings_offering (cost=0.14..12.70 rows=1 width=1263) (actual time=0.010..0.057 rows=1 (...)
  • Filter: ((application_id = 10) AND (archive_revision_id = 2147483647))
  • Rows Removed by Filter: 14
20. 0.134 0.224 ↑ 1.0 4 14

Hash Right Join (cost=1.09..3.96 rows=4 width=12) (actual time=0.005..0.016 rows=4 loops=14)

  • Hash Cond: (badges_badge.id = offerings_offering_badges.badge_id)
21. 0.084 0.084 ↑ 1.0 60 14

Seq Scan on badges_badge (cost=0.00..2.60 rows=60 width=4) (actual time=0.003..0.006 rows=60 loops=14)

22. 0.002 0.006 ↑ 1.0 4 1

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

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

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

24. 0.042 0.042 ↑ 1.0 1 14

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

  • Filter: (id = 10)
  • Rows Removed by Filter: 10
25. 0.042 0.042 ↑ 1.0 21 14

Seq Scan on offerings_offering_tags (cost=0.00..1.21 rows=21 width=8) (actual time=0.001..0.003 rows=21 loops=14)

26. 0.070 0.070 ↑ 1.0 1 14

Index Only Scan using badges_tag_pkey on badges_tag (cost=0.14..0.73 rows=1 width=4) (actual time=0.003..0.005 rows=1 loops=14)

  • Index Cond: (id = offerings_offering_tags.tag_id)
  • Heap Fetches: 12
27. 0.014 0.014 ↓ 0.0 0 14

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=14)

  • Index Cond: (badges_badge.id = badge_id)
28. 0.000 0.000 ↓ 0.0 0 14

Index Only Scan using merchants_applicationuserprofile_pkey on merchants_applicationuserprofile (cost=0.28..0.42 rows=1 width=4) (actual time=0.000..0.000 rows=0 loops=14)

  • Index Cond: (id = badges_userbadge.app_user_id)
  • Heap Fetches: 1
29. 0.770 1.106 ↓ 47.2 614 14

Materialize (cost=22.31..36.74 rows=13 width=16) (actual time=0.012..0.079 rows=614 loops=14)

30. 0.145 0.336 ↓ 47.2 614 1

Hash Right Join (cost=22.31..36.67 rows=13 width=16) (actual time=0.161..0.336 rows=614 loops=1)

  • Hash Cond: (t19.from_user_id = t13.id)
31. 0.041 0.041 ↑ 1.0 603 1

Seq Scan on offerings_offeringshare t19 (cost=0.00..12.03 rows=603 width=8) (actual time=0.003..0.041 rows=603 loops=1)

32. 0.004 0.150 ↑ 1.0 13 1

Hash (cost=22.15..22.15 rows=13 width=16) (actual time=0.150..0.150 rows=13 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
33. 0.083 0.146 ↑ 1.0 13 1

Hash Right Join (cost=1.29..22.15 rows=13 width=16) (actual time=0.054..0.146 rows=13 loops=1)

  • Hash Cond: (t13.id = badges_usertag.app_user_id)
34. 0.057 0.057 ↑ 1.0 1,071 1

Seq Scan on merchants_applicationuserprofile t13 (cost=0.00..16.71 rows=1,071 width=4) (actual time=0.003..0.057 rows=1,071 loops=1)

35. 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
36. 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)

37. 4.609 4.674 ↑ 1.0 11 4,674

Materialize (cost=1.25..13.52 rows=11 width=24) (actual time=0.000..0.001 rows=11 loops=4,674)

38. 0.032 0.065 ↑ 1.0 11 1

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

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

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

40. 0.001 0.004 ↑ 1.0 11 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
41. 0.003 0.003 ↑ 1.0 11 1

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

42. 0.000 0.000 ↓ 0.0 0 4,674

Index Scan using offerings_offeringshare_from_user_id_af8b7bf2 on offerings_offeringshare t17 (cost=0.28..1.82 rows=86 width=8) (actual time=0.000..0.000 rows=0 loops=4,674)

  • Index Cond: (merchants_applicationuserprofile.id = from_user_id)
43. 16.256 16.320 ↓ 295.8 178,342 1

Sort (cost=39.88..41.38 rows=603 width=29) (actual time=0.154..16.320 rows=178,342 loops=1)

  • Sort Key: offerings_offeringshare.offering_id DESC
  • Sort Method: quicksort Memory: 72kB
44. 0.064 0.064 ↑ 1.0 603 1

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

45. 0.000 0.000 ↑ 1.0 7 179,511

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

46. 0.010 0.015 ↑ 1.0 7 1

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

  • Hash Cond: (offerings_offeringcard.category_id = offerings_offeringcardcategory.id)
47. 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)

48. 0.003 0.004 ↑ 1.0 6 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
49. 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)

50. 0.004 0.005 ↑ 1.0 3 1

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

  • Sort Key: offerings_offering_venues.offering_id DESC
  • Sort Method: quicksort Memory: 25kB
51. 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)

52. 0.200 0.202 ↓ 314.2 2,514 1

Sort (cost=1.20..1.22 rows=8 width=8) (actual time=0.005..0.202 rows=2,514 loops=1)

  • Sort Key: offerings_offering_points_of_sale.offering_id DESC
  • Sort Method: quicksort Memory: 25kB
53. 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)

54. 0.003 0.004 ↓ 0.0 0 1

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

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

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

56. 101.004 101.072 ↓ 2,128.1 1,402,391 1

Sort (cost=43.44..45.09 rows=659 width=4) (actual time=0.131..101.072 rows=1,402,391 loops=1)

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

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

Planning time : 6.119 ms
Execution time : 9,096.435 ms