explain.depesz.com

PostgreSQL's explain analyze made readable

Result: uDRj

Settings
# exclusive inclusive rows x rows loops node
1. 51.388 47,231.797 ↓ 102.5 32,686 1

Hash Left Join (cost=2,305,578.92..2,363,902.31 rows=319 width=811) (actual time=46,326.216..47,231.797 rows=32,686 loops=1)

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

CTE venue_records

3. 2,316.393 22,596.552 ↓ 1.0 3,000,549 1

WindowAgg (cost=1,378,986.63..1,443,447.15 rows=2,864,912 width=60) (actual time=19,039.493..22,596.552 rows=3,000,549 loops=1)

4. 4,063.224 20,280.159 ↓ 1.0 3,000,549 1

Sort (cost=1,378,986.63..1,386,148.91 rows=2,864,912 width=60) (actual time=19,039.486..20,280.159 rows=3,000,549 loops=1)

  • Sort Key: venue_1.venue_id, venue_source_type.priority, venue_parameters_log.created_on DESC
  • Sort Method: external sort Disk: 170,128kB
5. 3,182.248 16,216.935 ↓ 1.0 3,000,549 1

WindowAgg (cost=686,551.64..856,291.97 rows=2,864,912 width=60) (actual time=7,198.409..16,216.935 rows=3,000,549 loops=1)

6. 2,090.189 13,034.687 ↓ 1.0 3,000,549 1

Merge Join (cost=686,551.64..806,156.01 rows=2,864,912 width=60) (actual time=7,198.367..13,034.687 rows=3,000,549 loops=1)

  • Merge Cond: (venue_1.venue_id = venue_parameters_log.venue_id)
7. 826.962 826.962 ↑ 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.037..826.962 rows=1,453,566 loops=1)

  • Heap Fetches: 1,441,614
8. 1,294.294 10,117.536 ↓ 1.0 3,000,549 1

Materialize (cost=686,544.33..700,868.89 rows=2,864,912 width=56) (actual time=7,198.324..10,117.536 rows=3,000,549 loops=1)

9. 5,206.319 8,823.242 ↓ 1.0 3,000,549 1

Sort (cost=686,544.33..693,706.61 rows=2,864,912 width=56) (actual time=7,198.319..8,823.242 rows=3,000,549 loops=1)

  • Sort Key: venue_parameters_log.venue_id
  • Sort Method: external merge Disk: 134,960kB
10. 1,959.574 3,616.923 ↓ 1.0 3,000,549 1

Hash Join (cost=14,978.01..183,435.67 rows=2,864,912 width=56) (actual time=43.257..3,616.923 rows=3,000,549 loops=1)

  • Hash Cond: (venue_parameters_log.venue_source_id = venue_source.venue_source_id)
11. 1,614.160 1,614.160 ↓ 1.0 3,000,549 1

Seq Scan on venue_parameters_log (cost=0.00..129,065.12 rows=2,864,912 width=24) (actual time=0.038..1,614.160 rows=3,000,549 loops=1)

12. 0.121 43.189 ↑ 1.4 176 1

Hash (cost=14,974.98..14,974.98 rows=242 width=40) (actual time=43.189..43.189 rows=176 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 17kB
13. 0.340 43.068 ↑ 1.4 176 1

Nested Loop (cost=0.15..14,974.98 rows=242 width=40) (actual time=0.024..43.068 rows=176 loops=1)

14. 42.376 42.376 ↑ 1.4 176 1

Seq Scan on venue_source (cost=0.00..14,883.42 rows=242 width=8) (actual time=0.012..42.376 rows=176 loops=1)

15. 0.352 0.352 ↑ 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.002..0.002 rows=1 loops=176)

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

CTE venue_reviews

17. 24,443.115 24,443.115 ↓ 185.5 4,451 1

CTE Scan on venue_records (cost=0.00..85,947.36 rows=24 width=20) (actual time=21,728.672..24,443.115 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: 2,996,098
18.          

CTE filtered_venues

19. 1.842 43,460.819 ↓ 116.8 2,803 1

Limit (cost=657,944.42..657,944.66 rows=24 width=16) (actual time=43,456.993..43,460.819 rows=2,803 loops=1)

20. 16.439 43,458.977 ↓ 116.8 2,803 1

HashAggregate (cost=657,944.42..657,944.66 rows=24 width=16) (actual time=43,456.991..43,458.977 rows=2,803 loops=1)

  • Group Key: venue_reviews.venue_id, venue_reviews.resolved_venue_parameters_log_id, venue_reviews.review_count
21. 1,601.124 43,442.538 ↓ 85.5 24,196 1

Hash Join (cost=333,703.72..657,942.30 rows=283 width=16) (actual time=37,179.699..43,442.538 rows=24,196 loops=1)

  • Hash Cond: (address_2.address_id = venue_reviews.address_id)
22. 6,184.883 17,393.892 ↑ 1.4 5,157,771 1

Seq Scan on address address_2 (cost=333,702.94..630,233.33 rows=7,388,096 width=4) (actual time=11,844.348..17,393.892 rows=5,157,771 loops=1)

  • Filter: (hashed SubPlan 3)
  • Rows Removed by Filter: 9,608,339
23.          

SubPlan (for Seq Scan)

24. 11,209.009 11,209.009 ↓ 7.1 663,133 1

Seq Scan on address address_1 (cost=0.00..333,470.86 rows=92,831 width=4) (actual time=451.293..11,209.009 rows=663,133 loops=1)

  • Filter: ((address_component_value ~~* 'United States'::text) AND (address_component_name = 'COUNTRY'::text))
  • Rows Removed by Filter: 14,102,977
25. 1.694 24,447.522 ↓ 185.5 4,451 1

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

  • Buckets: 8,192 (originally 1024) Batches: 1 (originally 1) Memory Usage: 291kB
26. 24,445.828 24,445.828 ↓ 185.5 4,451 1

CTE Scan on venue_reviews (cost=0.00..0.48 rows=24 width=20) (actual time=21,728.675..24,445.828 rows=4,451 loops=1)

27. 24.451 47,082.338 ↓ 102.5 32,686 1

Hash Left Join (cost=118,238.65..170,033.53 rows=319 width=787) (actual time=46,326.154..47,082.338 rows=32,686 loops=1)

  • Hash Cond: (venue_competitor.competitor_id = competitor.competitor_id)
28. 28.885 47,057.820 ↓ 102.5 32,686 1

Nested Loop Left Join (cost=118,235.87..170,026.37 rows=319 width=781) (actual time=46,326.061..47,057.820 rows=32,686 loops=1)

29. 3.036 47,006.231 ↓ 126.1 3,784 1

Hash Left Join (cost=118,235.44..169,987.13 rows=30 width=752) (actual time=46,326.037..47,006.231 rows=3,784 loops=1)

  • Hash Cond: (venue_genre.genre_id = genre.genre_id)
30. 452.355 47,003.149 ↓ 126.1 3,784 1

Hash Right Join (cost=118,234.35..169,985.90 rows=30 width=724) (actual time=46,325.964..47,003.149 rows=3,784 loops=1)

  • Hash Cond: (place_properties.venue_id = venue.venue_id)
31. 438.272 438.272 ↑ 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..438.272 rows=1,654,177 loops=1)

32. 4.842 46,112.522 ↓ 129.9 3,378 1

Hash (cost=118,234.02..118,234.02 rows=26 width=579) (actual time=46,112.522..46,112.522 rows=3,378 loops=1)

  • Buckets: 4,096 (originally 1024) Batches: 1 (originally 1) Memory Usage: 1,802kB
33. 5.014 46,107.680 ↓ 129.9 3,378 1

Nested Loop Left Join (cost=95,365.13..118,234.02 rows=26 width=579) (actual time=45,790.882..46,107.680 rows=3,378 loops=1)

34. 4.251 46,097.052 ↓ 117.0 2,807 1

Nested Loop Left Join (cost=95,364.70..118,221.63 rows=24 width=575) (actual time=45,790.868..46,097.052 rows=2,807 loops=1)

35. 3.399 46,087.187 ↓ 117.0 2,807 1

Nested Loop Left Join (cost=95,364.27..118,210.58 rows=24 width=567) (actual time=45,790.851..46,087.187 rows=2,807 loops=1)

36. 3.421 46,080.981 ↓ 117.0 2,807 1

Nested Loop Left Join (cost=95,364.12..118,206.25 rows=24 width=539) (actual time=45,790.842..46,080.981 rows=2,807 loops=1)

37. 3.350 46,074.753 ↓ 117.0 2,807 1

Nested Loop Left Join (cost=95,363.97..118,202.11 rows=24 width=534) (actual time=45,790.834..46,074.753 rows=2,807 loops=1)

38. 3.845 46,068.596 ↓ 117.0 2,807 1

Nested Loop Left Join (cost=95,363.82..118,197.79 rows=24 width=506) (actual time=45,790.824..46,068.596 rows=2,807 loops=1)

39. 3.662 46,059.145 ↓ 116.8 2,803 1

Nested Loop Left Join (cost=95,363.39..118,186.18 rows=24 width=502) (actual time=45,790.809..46,059.145 rows=2,803 loops=1)

40. 3.044 46,052.680 ↓ 116.8 2,803 1

Nested Loop (cost=95,363.24..118,177.10 rows=24 width=470) (actual time=45,790.800..46,052.680 rows=2,803 loops=1)

41. 3.945 46,044.030 ↓ 116.8 2,803 1

Nested Loop (cost=95,362.84..118,166.27 rows=24 width=446) (actual time=45,790.788..46,044.030 rows=2,803 loops=1)

42. 59.274 46,031.676 ↓ 116.8 2,803 1

Merge Left Join (cost=95,362.41..117,963.29 rows=24 width=147) (actual time=45,790.752..46,031.676 rows=2,803 loops=1)

  • Merge Cond: (venue.venue_id = scores.venue_id)
43. 4.554 43,496.824 ↓ 116.8 2,803 1

Sort (cost=203.95..204.01 rows=24 width=143) (actual time=43,495.332..43,496.824 rows=2,803 loops=1)

  • Sort Key: venue.venue_id
  • Sort Method: quicksort Memory: 823kB
44. 13.400 43,492.270 ↓ 116.8 2,803 1

Nested Loop (cost=0.43..203.40 rows=24 width=143) (actual time=43,457.040..43,492.270 rows=2,803 loops=1)

45. 43,467.658 43,467.658 ↓ 116.8 2,803 1

CTE Scan on filtered_venues (cost=0.00..0.48 rows=24 width=16) (actual time=43,457.009..43,467.658 rows=2,803 loops=1)

46. 11.212 11.212 ↑ 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.003..0.004 rows=1 loops=2,803)

  • Index Cond: (venue_id = filtered_venues.venue_id)
47. 110.232 2,475.578 ↓ 65.6 228,051 1

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

48. 210.153 2,365.346 ↓ 65.6 228,051 1

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

  • Filter: (scores."row" = 1)
  • Rows Removed by Filter: 458,680
49. 499.701 2,155.193 ↑ 1.0 686,731 1

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

50. 1,258.339 1,655.492 ↑ 1.0 686,731 1

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

  • Sort Key: venue_score.venue_id, venue_score.created_time DESC
  • Sort Method: external merge Disk: 20,248kB
51. 397.153 397.153 ↑ 1.0 692,475 1

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

52. 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)
53. 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)
54. 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)
55. 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)
56. 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)
57. 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)
58. 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)
59. 5.614 5.614 ↑ 1.0 1 2,807

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

  • Index Cond: (venue_parameters_log_id = log.venue_parameters_log_id)
60. 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)
61. 0.026 0.046 ↓ 14.2 57 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 11kB
62. 0.020 0.020 ↓ 14.2 57 1

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

63. 22.704 22.704 ↑ 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.003..0.006 rows=9 loops=3,784)

  • Index Cond: (address_id = log.address_id)
64. 0.033 0.067 ↓ 1.3 102 1

Hash (cost=1.79..1.79 rows=79 width=14) (actual time=0.067..0.067 rows=102 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 13kB
65. 0.034 0.034 ↓ 1.3 102 1

Seq Scan on competitor (cost=0.00..1.79 rows=79 width=14) (actual time=0.005..0.034 rows=102 loops=1)

66. 0.006 0.013 ↑ 1.0 4 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
67. 0.007 0.007 ↑ 1.0 4 1

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

68.          

SubPlan (for Hash Left Join)

69. 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)
70. 65.372 98.058 ↑ 1.0 1 32,686

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

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

Bitmap Index Scan on venue_source_admin_user_pkey (cost=0.00..12.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)
72. 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 : 12.239 ms
Execution time : 47,313.847 ms