explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 6REE

Settings
# exclusive inclusive rows x rows loops node
1. 63.672 33,698.991 ↓ 102.5 32,686 1

Hash Left Join (cost=2,305,574.48..2,365,169.48 rows=319 width=797) (actual time=32,862.697..33,698.991 rows=32,686 loops=1)

  • Hash Cond: (log.number_of_classes = number_of_classes.id)
2.          

CTE venue_records

3. 2,393.455 21,232.208 ↓ 1.0 2,999,717 1

WindowAgg (cost=1,378,985.22..1,443,445.74 rows=2,864,912 width=60) (actual time=17,884.771..21,232.208 rows=2,999,717 loops=1)

4. 3,719.891 18,838.753 ↓ 1.0 2,999,717 1

Sort (cost=1,378,985.22..1,386,147.50 rows=2,864,912 width=60) (actual time=17,884.765..18,838.753 rows=2,999,717 loops=1)

  • Sort Key: venue_1.venue_id, venue_source_type.priority, venue_parameters_log.created_on DESC
  • Sort Method: external sort Disk: 170080kB
5. 3,221.538 15,118.862 ↓ 1.0 2,999,717 1

WindowAgg (cost=686,550.22..856,290.55 rows=2,864,912 width=60) (actual time=6,121.887..15,118.862 rows=2,999,717 loops=1)

6. 2,156.951 11,897.324 ↓ 1.0 2,999,717 1

Merge Join (cost=686,550.22..806,154.59 rows=2,864,912 width=60) (actual time=6,121.838..11,897.324 rows=2,999,717 loops=1)

  • Merge Cond: (venue_1.venue_id = venue_parameters_log.venue_id)
7. 813.483 813.483 ↑ 1.0 1,453,566 1

Index Only Scan using venue_id_idx on venue venue_1 (cost=0.43..65,932.59 rows=1,457,639 width=4) (actual time=0.015..813.483 rows=1,453,566 loops=1)

  • Heap Fetches: 1441614
8. 1,306.081 8,926.890 ↓ 1.0 2,999,717 1

Materialize (cost=686,542.91..700,867.47 rows=2,864,912 width=56) (actual time=6,121.817..8,926.890 rows=2,999,717 loops=1)

9. 4,556.957 7,620.809 ↓ 1.0 2,999,717 1

Sort (cost=686,542.91..693,705.19 rows=2,864,912 width=56) (actual time=6,121.812..7,620.809 rows=2,999,717 loops=1)

  • Sort Key: venue_parameters_log.venue_id
  • Sort Method: external merge Disk: 134912kB
10. 1,686.664 3,063.852 ↓ 1.0 2,999,717 1

Hash Join (cost=14,976.59..183,434.25 rows=2,864,912 width=56) (actual time=16.042..3,063.852 rows=2,999,717 loops=1)

  • Hash Cond: (venue_parameters_log.venue_source_id = venue_source.venue_source_id)
11. 1,361.198 1,361.198 ↓ 1.0 2,999,717 1

Seq Scan on venue_parameters_log (cost=0.00..129,065.12 rows=2,864,912 width=24) (actual time=0.032..1,361.198 rows=2,999,717 loops=1)

12. 0.072 15.990 ↑ 1.3 176 1

Hash (cost=14,973.65..14,973.65 rows=235 width=40) (actual time=15.990..15.990 rows=176 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 17kB
13. 0.221 15.918 ↑ 1.3 176 1

Nested Loop (cost=0.15..14,973.65 rows=235 width=40) (actual time=0.016..15.918 rows=176 loops=1)

14. 15.521 15.521 ↑ 1.3 176 1

Seq Scan on venue_source (cost=0.00..14,883.35 rows=235 width=8) (actual time=0.008..15.521 rows=176 loops=1)

15. 0.176 0.176 ↑ 1.0 1 176

Index Scan using venue_source_type_pkey on venue_source_type (cost=0.15..0.37 rows=1 width=40) (actual time=0.001..0.001 rows=1 loops=176)

  • Index Cond: (venue_source_type_id = venue_source.venue_source_type_id)
16.          

CTE venue_reviews

17. 23,121.372 23,121.372 ↓ 185.5 4,451 1

CTE Scan on venue_records (cost=0.00..85,947.36 rows=24 width=20) (actual time=20,630.488..23,121.372 rows=4,451 loops=1)

  • Filter: ((review_count >= 2) AND (created_on <= '2020-01-05 00:00:00'::timestamp without time zone) AND (created_on >= '2020-01-01 00:00:00'::timestamp without time zone) AND (rank = 1))
  • Rows Removed by Filter: 2995266
18.          

CTE filtered_venues

19. 1.056 30,665.662 ↓ 116.8 2,803 1

Limit (cost=657,944.04..657,944.28 rows=24 width=16) (actual time=30,663.404..30,665.662 rows=2,803 loops=1)

20. 10.539 30,664.606 ↓ 116.8 2,803 1

HashAggregate (cost=657,944.04..657,944.28 rows=24 width=16) (actual time=30,663.402..30,664.606 rows=2,803 loops=1)

  • Group Key: venue_reviews.venue_id, venue_reviews.resolved_venue_parameters_log_id, venue_reviews.review_count
21. 1,222.150 30,654.067 ↓ 85.5 24,196 1

Hash Join (cost=333,703.33..657,941.91 rows=283 width=16) (actual time=26,527.523..30,654.067 rows=24,196 loops=1)

  • Hash Cond: (address_2.address_id = venue_reviews.address_id)
22. 4,401.825 6,306.136 ↑ 1.4 5,157,771 1

Seq Scan on address address_2 (cost=333,702.55..630,232.94 rows=7,388,096 width=4) (actual time=2,483.218..6,306.136 rows=5,157,771 loops=1)

  • Filter: (hashed SubPlan 3)
  • Rows Removed by Filter: 9608339
23.          

SubPlan (for Seq Scan)

24. 1,904.311 1,904.311 ↓ 7.2 663,133 1

Seq Scan on address address_1 (cost=0.00..333,470.86 rows=92,676 width=4) (actual time=67.007..1,904.311 rows=663,133 loops=1)

  • Filter: ((address_component_name = 'COUNTRY'::text) AND (address_component_value = 'United States'::text))
  • Rows Removed by Filter: 14102977
25. 1.648 23,125.781 ↓ 185.5 4,451 1

Hash (cost=0.48..0.48 rows=24 width=20) (actual time=23,125.781..23,125.781 rows=4,451 loops=1)

  • Buckets: 8192 (originally 1024) Batches: 1 (originally 1) Memory Usage: 291kB
26. 23,124.133 23,124.133 ↓ 185.5 4,451 1

CTE Scan on venue_reviews (cost=0.00..0.48 rows=24 width=20) (actual time=20,630.489..23,124.133 rows=4,451 loops=1)

27. 20.806 33,569.938 ↓ 102.5 32,686 1

Nested Loop Left Join (cost=118,236.02..170,026.51 rows=319 width=773) (actual time=32,862.649..33,569.938 rows=32,686 loops=1)

28. 2.591 33,530.212 ↓ 126.1 3,784 1

Hash Left Join (cost=118,235.58..169,987.27 rows=30 width=744) (actual time=32,862.625..33,530.212 rows=3,784 loops=1)

  • Hash Cond: (venue_genre.genre_id = genre.genre_id)
29. 446.429 33,527.569 ↓ 126.1 3,784 1

Hash Right Join (cost=118,234.49..169,986.04 rows=30 width=716) (actual time=32,862.556..33,527.569 rows=3,784 loops=1)

  • Hash Cond: (place_properties.venue_id = venue.venue_id)
30. 431.627 431.627 ↑ 1.0 1,654,177 1

Seq Scan on place_properties (cost=0.00..45,460.73 rows=1,677,473 width=153) (actual time=0.007..431.627 rows=1,654,177 loops=1)

31. 4.761 32,649.513 ↓ 129.9 3,378 1

Hash (cost=118,234.17..118,234.17 rows=26 width=571) (actual time=32,649.513..32,649.513 rows=3,378 loops=1)

  • Buckets: 4096 (originally 1024) Batches: 1 (originally 1) Memory Usage: 1770kB
32. 4.817 32,644.752 ↓ 129.9 3,378 1

Nested Loop Left Join (cost=95,365.13..118,234.17 rows=26 width=571) (actual time=32,332.162..32,644.752 rows=3,378 loops=1)

33. 2.195 32,634.321 ↓ 117.0 2,807 1

Nested Loop Left Join (cost=95,364.70..118,221.77 rows=24 width=567) (actual time=32,332.147..32,634.321 rows=2,807 loops=1)

34. 3.297 32,623.705 ↓ 117.0 2,807 1

Nested Loop Left Join (cost=95,364.27..118,210.72 rows=24 width=567) (actual time=32,331.556..32,623.705 rows=2,807 loops=1)

35. 3.615 32,617.601 ↓ 117.0 2,807 1

Nested Loop Left Join (cost=95,364.12..118,206.40 rows=24 width=539) (actual time=32,331.546..32,617.601 rows=2,807 loops=1)

36. 3.303 32,611.179 ↓ 117.0 2,807 1

Nested Loop Left Join (cost=95,363.97..118,202.26 rows=24 width=534) (actual time=32,331.538..32,611.179 rows=2,807 loops=1)

37. 3.875 32,605.069 ↓ 117.0 2,807 1

Nested Loop Left Join (cost=95,363.82..118,197.94 rows=24 width=506) (actual time=32,331.530..32,605.069 rows=2,807 loops=1)

38. 3.850 32,595.588 ↓ 116.8 2,803 1

Nested Loop Left Join (cost=95,363.39..118,186.32 rows=24 width=502) (actual time=32,331.515..32,595.588 rows=2,803 loops=1)

39. 3.128 32,588.935 ↓ 116.8 2,803 1

Nested Loop (cost=95,363.24..118,177.10 rows=24 width=470) (actual time=32,331.506..32,588.935 rows=2,803 loops=1)

40. 4.105 32,580.201 ↓ 116.8 2,803 1

Nested Loop (cost=95,362.84..118,166.27 rows=24 width=446) (actual time=32,331.494..32,580.201 rows=2,803 loops=1)

41. 56.929 32,567.687 ↓ 116.8 2,803 1

Merge Left Join (cost=95,362.41..117,963.29 rows=24 width=147) (actual time=32,331.461..32,567.687 rows=2,803 loops=1)

  • Merge Cond: (venue.venue_id = scores.venue_id)
42. 3.439 30,679.601 ↓ 116.8 2,803 1

Sort (cost=203.95..204.01 rows=24 width=143) (actual time=30,678.189..30,679.601 rows=2,803 loops=1)

  • Sort Key: venue.venue_id
  • Sort Method: quicksort Memory: 823kB
43. 3.329 30,676.162 ↓ 116.8 2,803 1

Nested Loop (cost=0.43..203.40 rows=24 width=143) (actual time=30,663.433..30,676.162 rows=2,803 loops=1)

44. 30,667.227 30,667.227 ↓ 116.8 2,803 1

CTE Scan on filtered_venues (cost=0.00..0.48 rows=24 width=16) (actual time=30,663.410..30,667.227 rows=2,803 loops=1)

45. 5.606 5.606 ↑ 1.0 1 2,803

Index Scan using venue_id_idx on venue (cost=0.43..8.45 rows=1 width=131) (actual time=0.002..0.002 rows=1 loops=2,803)

  • Index Cond: (venue_id = filtered_venues.venue_id)
46. 107.318 1,831.157 ↓ 65.6 228,051 1

Materialize (cost=95,151.96..117,750.52 rows=3,475 width=12) (actual time=728.614..1,831.157 rows=228,051 loops=1)

47. 200.764 1,723.839 ↓ 65.6 228,051 1

Subquery Scan on scores (cost=95,151.96..117,741.83 rows=3,475 width=12) (actual time=728.611..1,723.839 rows=228,051 loops=1)

  • Filter: (scores."row" = 1)
  • Rows Removed by Filter: 458680
48. 472.761 1,523.075 ↑ 1.0 686,731 1

WindowAgg (cost=95,151.96..109,053.42 rows=695,073 width=20) (actual time=728.599..1,523.075 rows=686,731 loops=1)

49. 833.140 1,050.314 ↑ 1.0 686,731 1

Sort (cost=95,151.96..96,889.64 rows=695,073 width=20) (actual time=728.588..1,050.314 rows=686,731 loops=1)

  • Sort Key: venue_score.venue_id, venue_score.created_time DESC
  • Sort Method: external merge Disk: 20248kB
50. 217.174 217.174 ↑ 1.0 692,475 1

Seq Scan on venue_score (cost=0.00..13,450.73 rows=695,073 width=20) (actual time=0.006..217.174 rows=692,475 loops=1)

51. 8.409 8.409 ↑ 1.0 1 2,803

Index Scan using venue_parameters_log_pkey on venue_parameters_log log (cost=0.43..8.45 rows=1 width=303) (actual time=0.003..0.003 rows=1 loops=2,803)

  • Index Cond: (venue_parameters_log_id = filtered_venues.resolved_venue_parameters_log_id)
52. 5.606 5.606 ↑ 1.0 1 2,803

Index Scan using venue_source_pkey on venue_source source (cost=0.40..0.44 rows=1 width=28) (actual time=0.002..0.002 rows=1 loops=2,803)

  • Index Cond: (venue_source_id = log.venue_source_id)
53. 2.803 2.803 ↑ 1.0 1 2,803

Index Scan using venue_source_type_pkey on venue_source_type source_type (cost=0.15..0.37 rows=1 width=36) (actual time=0.001..0.001 rows=1 loops=2,803)

  • Index Cond: (venue_source_type_id = source.venue_source_type_id)
54. 5.606 5.606 ↑ 1.0 1 2,803

Index Scan using venue_inventory_type_venue_parameters_log_id_idx on venue_inventory_type (cost=0.43..0.47 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=2,803)

  • Index Cond: (venue_parameters_log_id = log.venue_parameters_log_id)
55. 2.807 2.807 ↑ 1.0 1 2,807

Index Scan using inventory_type_pkey on inventory_type inventory (cost=0.15..0.17 rows=1 width=36) (actual time=0.001..0.001 rows=1 loops=2,807)

  • Index Cond: (inventory_type_id = venue_inventory_type.inventory_type_id)
56. 2.807 2.807 ↑ 1.0 1 2,807

Index Scan using scheduling_software_pkey on scheduling_software software (cost=0.14..0.16 rows=1 width=13) (actual time=0.001..0.001 rows=1 loops=2,807)

  • Index Cond: (scheduling_software_id = log.scheduling_software_id)
57. 2.807 2.807 ↑ 1.0 1 2,807

Index Scan using contact_title_pkey on contact_title (cost=0.15..0.17 rows=1 width=36) (actual time=0.001..0.001 rows=1 loops=2,807)

  • Index Cond: (contact_title_id = log.contact_title_id)
58. 8.421 8.421 ↑ 1.0 1 2,807

Index Only Scan using venue_competitor_venue_parameters_log_id_idx on venue_competitor (cost=0.42..0.45 rows=1 width=4) (actual time=0.002..0.003 rows=1 loops=2,807)

  • Index Cond: (venue_parameters_log_id = log.venue_parameters_log_id)
  • Heap Fetches: 2807
59. 5.614 5.614 ↑ 2.0 1 2,807

Index Scan using venue_genre_venue_parameters_log_id_idx on venue_genre (cost=0.43..0.50 rows=2 width=8) (actual time=0.002..0.002 rows=1 loops=2,807)

  • Index Cond: (venue_parameters_log_id = log.venue_parameters_log_id)
60. 0.031 0.052 ↓ 14.2 57 1

Hash (cost=1.04..1.04 rows=4 width=36) (actual time=0.052..0.052 rows=57 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
61. 0.021 0.021 ↓ 14.2 57 1

Seq Scan on genre (cost=0.00..1.04 rows=4 width=36) (actual time=0.006..0.021 rows=57 loops=1)

62. 18.920 18.920 ↑ 2.7 9 3,784

Index Scan using address_address_id_idx on address (cost=0.43..1.07 rows=24 width=29) (actual time=0.002..0.005 rows=9 loops=3,784)

  • Index Cond: (address_id = log.address_id)
63. 0.004 0.009 ↑ 1.0 4 1

Hash (cost=1.04..1.04 rows=4 width=36) (actual time=0.009..0.009 rows=4 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
64. 0.005 0.005 ↑ 1.0 4 1

Seq Scan on number_of_classes (cost=0.00..1.04 rows=4 width=36) (actual time=0.004..0.005 rows=4 loops=1)

65.          

SubPlan (for Hash Left Join)

66. 0.000 0.000 ↓ 0.0 0

Seq Scan on venue_source_scraper (cost=0.00..3.01 rows=1 width=4) (never executed)

  • Filter: (venue_source_scraper_id = source.source_id)
67. 32.686 65.372 ↑ 1.0 1 32,686

Bitmap Heap Scan on venue_source_admin_user (cost=16.40..20.42 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=32,686)

  • Recheck Cond: (venue_source_admin_user_id = source.source_id)
  • Heap Blocks: exact=32686
68. 32.686 32.686 ↑ 1.0 1 32,686

Bitmap Index Scan on venue_source_admin_user_pkey (cost=0.00..16.40 rows=1 width=0) (actual time=0.001..0.001 rows=1 loops=32,686)

  • Index Cond: (venue_source_admin_user_id = source.source_id)
69. 0.000 0.000 ↓ 0.0 0

Seq Scan on venue_source_manual_user (cost=0.00..1.02 rows=1 width=4) (never executed)

  • Filter: (venue_source_manual_user_id = source.source_id)
Planning time : 4.538 ms
Execution time : 33,786.114 ms