explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 7DiV

Settings
# exclusive inclusive rows x rows loops node
1. 0.063 22,071.408 ↑ 1.0 14 1

Nested Loop (cost=1,348,817.81..1,449,806.38 rows=14 width=56) (actual time=20,711.546..22,071.408 rows=14 loops=1)

2. 0.052 22,070.855 ↑ 1.0 14 1

Nested Loop (cost=1,348,817.37..1,447,868.81 rows=14 width=64) (actual time=20,711.450..22,070.855 rows=14 loops=1)

3. 156.507 22,070.607 ↑ 1.0 14 1

Merge Join (cost=1,348,816.94..1,447,861.79 rows=14 width=72) (actual time=20,711.410..22,070.607 rows=14 loops=1)

  • Merge Cond: (e.id = e_2.id)
4. 0.027 6,490.714 ↑ 1.0 14 1

Nested Loop (cost=65,513.71..65,633.13 rows=14 width=32) (actual time=6,490.582..6,490.714 rows=14 loops=1)

5. 0.006 6,490.603 ↑ 1.0 14 1

Limit (cost=65,513.28..65,514.73 rows=14 width=8) (actual time=6,490.568..6,490.603 rows=14 loops=1)

6. 0.000 6,490.597 ↑ 1.0 14 1

Group (cost=65,513.28..65,514.73 rows=14 width=8) (actual time=6,490.567..6,490.597 rows=14 loops=1)

  • Group Key: v_1.representative_id
7. 18.392 6,490.673 ↓ 1.2 14 1

Gather Merge (cost=65,513.28..65,514.70 rows=12 width=8) (actual time=6,490.566..6,490.673 rows=14 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
8. 0.003 6,472.281 ↑ 1.2 5 3 / 3

Group (cost=64,513.26..64,513.29 rows=6 width=8) (actual time=6,472.278..6,472.281 rows=5 loops=3)

  • Group Key: v_1.representative_id
9. 0.029 6,472.278 ↑ 1.2 5 3 / 3

Sort (cost=64,513.26..64,513.27 rows=6 width=8) (actual time=6,472.277..6,472.278 rows=5 loops=3)

  • Sort Key: v_1.representative_id
  • Sort Method: quicksort Memory: 25kB
10. 0.015 6,472.249 ↑ 1.2 5 3 / 3

Nested Loop (cost=9.90..64,513.18 rows=6 width=8) (actual time=1,007.977..6,472.249 rows=5 loops=3)

11. 0.014 6,472.187 ↑ 1.2 5 3 / 3

Nested Loop (cost=9.47..64,484.87 rows=6 width=16) (actual time=1,007.952..6,472.187 rows=5 loops=3)

12. 692.030 6,472.112 ↑ 1.2 5 3 / 3

Nested Loop (cost=9.04..64,454.52 rows=6 width=24) (actual time=1,007.916..6,472.112 rows=5 loops=3)

13. 937.570 3,061.573 ↓ 764.1 906,170 3 / 3

Nested Loop (cost=8.61..63,607.57 rows=1,186 width=8) (actual time=16.069..3,061.573 rows=906,170 loops=3)

14. 205.052 311.664 ↓ 764.7 906,170 3 / 3

Hash Join (cost=8.18..63,013.57 rows=1,185 width=8) (actual time=16.055..311.664 rows=906,170 loops=3)

  • Hash Cond: (e_1.data_source_id = ds.id)
15. 106.593 106.593 ↑ 1.2 1,015,086 3 / 3

Parallel Seq Scan on place_description e_1 (cost=0.00..59,665.44 rows=1,267,344 width=16) (actual time=0.004..106.593 rows=1,015,086 loops=3)

16. 0.003 0.019 ↑ 1.0 1 3 / 3

Hash (cost=8.17..8.17 rows=1 width=8) (actual time=0.019..0.019 rows=1 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
17. 0.016 0.016 ↑ 1.0 1 3 / 3

Index Scan using data_source_ux1 on data_source ds (cost=0.15..8.17 rows=1 width=8) (actual time=0.015..0.016 rows=1 loops=3)

  • Index Cond: ((name)::text = 'SAFEGRAPH'::text)
18. 1,812.339 1,812.339 ↑ 1.0 1 2,718,509 / 3

Index Scan using place_description_current_version_pointer_ux1 on place_description_current_version_pointer cvp_1 (cost=0.43..0.50 rows=1 width=16) (actual time=0.002..0.002 rows=1 loops=2,718,509)

  • Index Cond: (place_description_id = e_1.id)
19. 2,718.509 2,718.509 ↓ 0.0 0 2,718,509 / 3

Index Scan using place_description_version_pkey on place_description_version v_1 (cost=0.43..0.71 rows=1 width=32) (actual time=0.003..0.003 rows=0 loops=2,718,509)

  • Index Cond: (id = cvp_1.place_description_version_id)
  • Filter: ((confidence >= '0'::numeric) AND (lower((name)::text) ~~ 'amy''s ice cream%'::text))
  • Rows Removed by Filter: 1
20. 0.061 0.061 ↑ 1.0 1 14 / 3

Index Only Scan using address_pkey on address a (cost=0.43..5.06 rows=1 width=8) (actual time=0.013..0.013 rows=1 loops=14)

  • Index Cond: (id = v_1.address_id)
  • Heap Fetches: 5
21. 0.047 0.047 ↑ 1.0 1 14 / 3

Index Only Scan using geocode_pkey on geocode g (cost=0.43..4.72 rows=1 width=8) (actual time=0.010..0.010 rows=1 loops=14)

  • Index Cond: (id = v_1.geocode_id)
  • Heap Fetches: 5
22. 0.084 0.084 ↑ 1.0 1 14

Index Scan using place_description_pkey on place_description e (cost=0.43..8.45 rows=1 width=24) (actual time=0.006..0.006 rows=1 loops=14)

  • Index Cond: (id = v_1.representative_id)
23. 1,301.030 15,423.386 ↑ 1.1 2,750,907 1

GroupAggregate (cost=1,283,303.23..1,344,208.16 rows=3,041,626 width=40) (actual time=13,953.545..15,423.386 rows=2,750,907 loops=1)

  • Group Key: e_2.id
24. 1,092.993 14,122.356 ↑ 1.0 2,913,188 1

Sort (cost=1,283,303.23..1,290,931.43 rows=3,051,280 width=16) (actual time=13,953.523..14,122.356 rows=2,913,188 loops=1)

  • Sort Key: e_2.id
  • Sort Method: quicksort Memory: 240459kB
25. 1,120.605 13,029.363 ↑ 1.0 3,032,630 1

Hash Join (cost=838,688.27..954,665.38 rows=3,051,280 width=16) (actual time=7,145.025..13,029.363 rows=3,032,630 loops=1)

  • Hash Cond: (cvp_2.place_description_id = e_2.id)
26. 1,089.830 10,852.198 ↓ 1.1 3,032,630 1

Hash Join (cost=723,259.69..831,941.45 rows=2,779,174 width=24) (actual time=6,073.980..10,852.198 rows=3,032,630 loops=1)

  • Hash Cond: (cvp_2.place_description_version_id = v_2.id)
27. 1,143.974 8,064.943 ↓ 1.1 3,032,630 1

Hash Join (cost=472,499.13..573,885.55 rows=2,779,174 width=32) (actual time=4,347.621..8,064.943 rows=3,032,630 loops=1)

  • Hash Cond: (v_3.representative_id = cvp_2.place_description_id)
28. 1,107.709 5,815.061 ↑ 1.0 3,045,259 1

Hash Join (cost=372,171.03..465,560.50 rows=3,046,449 width=16) (actual time=3,227.208..5,815.061 rows=3,045,259 loops=1)

  • Hash Cond: (cvp_3.place_description_version_id = v_3.id)
29. 1,165.692 2,616.833 ↑ 1.0 3,045,259 1

Hash Join (cost=100,328.10..185,720.64 rows=3,046,449 width=16) (actual time=1,107.985..2,616.833 rows=3,045,259 loops=1)

  • Hash Cond: (e_3.id = cvp_3.place_description_id)
30. 359.413 359.413 ↓ 1.0 3,045,259 1

Seq Scan on place_description e_3 (cost=0.00..77,408.26 rows=3,041,626 width=8) (actual time=0.006..359.413 rows=3,045,259 loops=1)

31. 676.627 1,091.728 ↑ 1.0 3,045,259 1

Hash (cost=62,247.49..62,247.49 rows=3,046,449 width=16) (actual time=1,091.728..1,091.728 rows=3,045,259 loops=1)

  • Buckets: 4194304 Batches: 1 Memory Usage: 175515kB
32. 415.101 415.101 ↑ 1.0 3,045,259 1

Seq Scan on place_description_current_version_pointer cvp_3 (cost=0.00..62,247.49 rows=3,046,449 width=16) (actual time=0.012..415.101 rows=3,045,259 loops=1)

33. 1,146.622 2,090.519 ↑ 1.0 4,720,666 1

Hash (cost=212,657.41..212,657.41 rows=4,734,841 width=16) (actual time=2,090.519..2,090.519 rows=4,720,666 loops=1)

  • Buckets: 8388608 Batches: 1 Memory Usage: 283493kB
34. 943.897 943.897 ↑ 1.0 4,720,666 1

Seq Scan on place_description_version v_3 (cost=0.00..212,657.41 rows=4,734,841 width=16) (actual time=0.009..943.897 rows=4,720,666 loops=1)

35. 685.815 1,105.908 ↑ 1.0 3,045,259 1

Hash (cost=62,247.49..62,247.49 rows=3,046,449 width=16) (actual time=1,105.908..1,105.908 rows=3,045,259 loops=1)

  • Buckets: 4194304 Batches: 1 Memory Usage: 175515kB
36. 420.093 420.093 ↑ 1.0 3,045,259 1

Seq Scan on place_description_current_version_pointer cvp_2 (cost=0.00..62,247.49 rows=3,046,449 width=16) (actual time=0.012..420.093 rows=3,045,259 loops=1)

37. 1,040.976 1,697.425 ↑ 1.0 4,720,666 1

Hash (cost=191,575.05..191,575.05 rows=4,734,841 width=8) (actual time=1,697.425..1,697.425 rows=4,720,666 loops=1)

  • Buckets: 8388608 Batches: 1 Memory Usage: 249938kB
38. 656.449 656.449 ↑ 1.0 4,720,666 1

Index Only Scan using place_description_version_pkey on place_description_version v_2 (cost=0.43..191,575.05 rows=4,734,841 width=8) (actual time=0.044..656.449 rows=4,720,666 loops=1)

  • Heap Fetches: 30797
39. 661.981 1,056.560 ↓ 1.0 3,045,259 1

Hash (cost=77,408.26..77,408.26 rows=3,041,626 width=8) (actual time=1,056.560..1,056.560 rows=3,045,259 loops=1)

  • Buckets: 4194304 Batches: 1 Memory Usage: 151724kB
40. 394.579 394.579 ↓ 1.0 3,045,259 1

Seq Scan on place_description e_2 (cost=0.00..77,408.26 rows=3,041,626 width=8) (actual time=0.014..394.579 rows=3,045,259 loops=1)

41. 0.196 0.196 ↑ 1.0 1 14

Index Scan using place_description_current_version_pointer_ux1 on place_description_current_version_pointer cvp (cost=0.43..0.50 rows=1 width=16) (actual time=0.014..0.014 rows=1 loops=14)

  • Index Cond: (place_description_id = e.id)
42. 0.098 0.098 ↑ 1.0 1 14

Index Only Scan using place_description_version_pkey on place_description_version v (cost=0.43..0.49 rows=1 width=8) (actual time=0.007..0.007 rows=1 loops=14)

  • Index Cond: (id = cvp.place_description_version_id)
  • Heap Fetches: 0
43.          

SubPlan (for Nested Loop)

44. 0.126 0.392 ↑ 1.0 1 14

Aggregate (cost=137.90..137.91 rows=1 width=32) (actual time=0.028..0.028 rows=1 loops=14)

45. 0.028 0.266 ↑ 10.0 1 14

Nested Loop (cost=1.29..137.85 rows=10 width=24) (actual time=0.018..0.019 rows=1 loops=14)

46. 0.056 0.196 ↑ 10.0 1 14

Nested Loop (cost=0.86..132.95 rows=10 width=32) (actual time=0.013..0.014 rows=1 loops=14)

47. 0.098 0.098 ↑ 10.0 1 14

Index Scan using place_description_pkey on place_description e_4 (cost=0.43..48.48 rows=10 width=24) (actual time=0.006..0.007 rows=1 loops=14)

  • Index Cond: (id = ANY ((array_agg(e_3.id))))
48. 0.042 0.042 ↑ 1.0 1 14

Index Scan using place_description_current_version_pointer_ux1 on place_description_current_version_pointer cvp_4 (cost=0.43..8.45 rows=1 width=16) (actual time=0.003..0.003 rows=1 loops=14)

  • Index Cond: (place_description_id = e_4.id)
49. 0.042 0.042 ↑ 1.0 1 14

Index Only Scan using place_description_version_pkey on place_description_version v_4 (cost=0.43..0.49 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=14)

  • Index Cond: (id = cvp_4.place_description_version_id)
  • Heap Fetches: 0
Planning time : 4.391 ms
Execution time : 22,134.416 ms