explain.depesz.com

PostgreSQL's explain analyze made readable

Result: BQsZf

Settings
# exclusive inclusive rows x rows loops node
1. 0.145 36,000.643 ↓ 15.0 15 1

Nested Loop (cost=1,432,279.17..1,694,731.86 rows=1 width=56) (actual time=29,663.870..36,000.643 rows=15 loops=1)

2. 192.363 35,958.933 ↓ 15.0 15 1

Merge Join (cost=1,432,278.74..1,694,592.07 rows=1 width=64) (actual time=29,659.474..35,958.933 rows=15 loops=1)

  • Merge Cond: (cvp.place_description_id = e.id)
3. 924.189 35,094.915 ↑ 1.1 2,864,170 1

Merge Join (cost=1,388,528.22..1,810,009.47 rows=3,008,072 width=56) (actual time=26,790.522..35,094.915 rows=2,864,170 loops=1)

  • Merge Cond: (e_1.id = cvp.place_description_id)
4. 1,453.972 32,061.165 ↑ 1.1 2,864,170 1

GroupAggregate (cost=1,388,527.79..1,618,560.35 rows=3,008,072 width=40) (actual time=26,790.466..32,061.165 rows=2,864,170 loops=1)

  • Group Key: e_1.id
5. 958.329 30,607.193 ↓ 1.0 3,026,914 1

Merge Join (cost=1,388,527.79..1,565,919.09 rows=3,008,072 width=16) (actual time=26,790.453..30,607.193 rows=3,026,914 loops=1)

  • Merge Cond: (e_1.id = cvp_1.place_description_id)
6. 2,008.288 2,008.288 ↑ 1.0 3,038,514 1

Index Only Scan using place_description_pkey on place_description e_1 (cost=0.43..129,381.70 rows=3,097,706 width=8) (actual time=0.060..2,008.288 rows=3,038,514 loops=1)

  • Heap Fetches: 3038514
7. 330.800 27,640.576 ↓ 1.1 3,026,914 1

Materialize (cost=1,388,521.29..1,402,326.50 rows=2,761,041 width=24) (actual time=26,790.382..27,640.576 rows=3,026,914 loops=1)

8. 2,112.178 27,309.776 ↓ 1.1 3,026,914 1

Sort (cost=1,388,521.29..1,395,423.89 rows=2,761,041 width=24) (actual time=26,790.376..27,309.776 rows=3,026,914 loops=1)

  • Sort Key: cvp_1.place_description_id
  • Sort Method: external merge Disk: 100984kB
9. 1,719.653 25,197.598 ↓ 1.1 3,033,495 1

Hash Join (cost=684,770.09..979,888.35 rows=2,761,041 width=24) (actual time=18,651.156..25,197.598 rows=3,033,495 loops=1)

  • Hash Cond: (cvp_1.place_description_version_id = v_1.id)
10. 1,461.676 9,920.580 ↓ 1.1 3,033,495 1

Hash Join (cost=453,719.65..685,378.16 rows=2,761,041 width=32) (actual time=5,093.261..9,920.580 rows=3,033,495 loops=1)

  • Hash Cond: (v_2.representative_id = cvp_1.place_description_id)
11. 1,662.587 5,974.050 ↑ 1.0 3,047,779 1

Hash Join (cost=344,693.05..523,620.58 rows=3,052,560 width=16) (actual time=2,607.364..5,974.050 rows=3,047,779 loops=1)

  • Hash Cond: (cvp_2.place_description_version_id = v_2.id)
12. 1,355.510 2,646.311 ↑ 1.0 3,047,779 1

Hash Join (cost=109,026.60..227,051.15 rows=3,052,560 width=16) (actual time=937.311..2,646.311 rows=3,047,779 loops=1)

  • Hash Cond: (e_2.id = cvp_2.place_description_id)
13. 354.502 354.502 ↑ 1.0 3,047,779 1

Seq Scan on place_description e_2 (cost=0.00..70,785.06 rows=3,097,706 width=8) (actual time=0.006..354.502 rows=3,047,779 loops=1)

14. 541.945 936.299 ↑ 1.0 3,047,779 1

Hash (cost=55,963.60..55,963.60 rows=3,052,560 width=16) (actual time=936.299..936.299 rows=3,047,779 loops=1)

  • Buckets: 131072 Batches: 64 Memory Usage: 3261kB
15. 394.354 394.354 ↑ 1.0 3,047,779 1

Seq Scan on place_description_current_version_pointer cvp_2 (cost=0.00..55,963.60 rows=3,052,560 width=16) (actual time=0.005..394.354 rows=3,047,779 loops=1)

16. 744.653 1,665.152 ↑ 1.0 4,726,242 1

Hash (cost=153,510.42..153,510.42 rows=4,726,242 width=16) (actual time=1,665.152..1,665.152 rows=4,726,242 loops=1)

  • Buckets: 131072 Batches: 128 Memory Usage: 2733kB
17. 920.499 920.499 ↑ 1.0 4,726,242 1

Seq Scan on place_description_version v_2 (cost=0.00..153,510.42 rows=4,726,242 width=16) (actual time=0.007..920.499 rows=4,726,242 loops=1)

18. 1,172.905 2,484.854 ↑ 1.0 3,047,779 1

Hash (cost=55,963.60..55,963.60 rows=3,052,560 width=16) (actual time=2,484.854..2,484.854 rows=3,047,779 loops=1)

  • Buckets: 131072 Batches: 64 Memory Usage: 3272kB
19. 1,311.949 1,311.949 ↑ 1.0 3,047,779 1

Seq Scan on place_description_current_version_pointer cvp_1 (cost=0.00..55,963.60 rows=3,052,560 width=16) (actual time=29.755..1,311.949 rows=3,047,779 loops=1)

20. 4,443.598 13,557.365 ↑ 1.0 4,726,242 1

Hash (cost=153,510.42..153,510.42 rows=4,726,242 width=8) (actual time=13,557.365..13,557.365 rows=4,726,242 loops=1)

  • Buckets: 131072 Batches: 64 Memory Usage: 3913kB
21. 9,113.767 9,113.767 ↑ 1.0 4,726,242 1

Seq Scan on place_description_version v_1 (cost=0.00..153,510.42 rows=4,726,242 width=8) (actual time=8.413..9,113.767 rows=4,726,242 loops=1)

22. 2,109.561 2,109.561 ↑ 1.0 3,038,513 1

Index Scan using place_description_current_version_pointer_ux1 on place_description_current_version_pointer cvp (cost=0.43..116,136.10 rows=3,052,560 width=16) (actual time=0.050..2,109.561 rows=3,038,513 loops=1)

23. 0.022 671.655 ↓ 15.0 15 1

Materialize (cost=43,750.53..43,758.57 rows=1 width=32) (actual time=671.482..671.655 rows=15 loops=1)

24. 0.060 671.633 ↓ 15.0 15 1

Nested Loop (cost=43,750.53..43,758.56 rows=1 width=32) (actual time=671.475..671.633 rows=15 loops=1)

25. 0.011 671.483 ↓ 15.0 15 1

Limit (cost=43,750.10..43,750.11 rows=1 width=8) (actual time=671.446..671.483 rows=15 loops=1)

26. 0.019 671.472 ↓ 15.0 15 1

Group (cost=43,750.10..43,750.11 rows=1 width=8) (actual time=671.444..671.472 rows=15 loops=1)

  • Group Key: v_3.representative_id
27. 0.000 671.453 ↓ 7.5 15 1

Sort (cost=43,750.10..43,750.10 rows=2 width=8) (actual time=671.442..671.453 rows=15 loops=1)

  • Sort Key: v_3.representative_id
  • Sort Method: quicksort Memory: 25kB
28. 15.069 671.511 ↓ 7.5 15 1

Gather (cost=43,749.88..43,750.09 rows=2 width=8) (actual time=653.795..671.511 rows=15 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
29. 0.004 656.442 ↓ 5.0 5 3 / 3

Group (cost=42,749.88..42,749.89 rows=1 width=8) (actual time=656.439..656.442 rows=5 loops=3)

  • Group Key: v_3.representative_id
30. 0.034 656.438 ↓ 5.0 5 3 / 3

Sort (cost=42,749.88..42,749.88 rows=1 width=8) (actual time=656.438..656.438 rows=5 loops=3)

  • Sort Key: v_3.representative_id
  • Sort Method: quicksort Memory: 25kB
31. 0.030 656.404 ↓ 5.0 5 3 / 3

Nested Loop (cost=1,197.84..42,749.87 rows=1 width=8) (actual time=349.560..656.404 rows=5 loops=3)

32. 0.022 622.164 ↓ 5.0 5 3 / 3

Nested Loop (cost=1,197.41..42,741.48 rows=1 width=16) (actual time=341.669..622.164 rows=5 loops=3)

33. 0.043 580.872 ↓ 5.0 5 3 / 3

Hash Join (cost=1,196.98..42,733.08 rows=1 width=24) (actual time=331.139..580.872 rows=5 loops=3)

  • Hash Cond: (e_3.data_source_id = ds.id)
34. 0.033 578.064 ↑ 13.2 6 3 / 3

Nested Loop (cost=1,188.80..42,724.69 rows=79 width=32) (actual time=258.823..578.064 rows=6 loops=3)

35. 189.268 538.047 ↑ 13.2 6 3 / 3

Hash Join (cost=1,188.37..42,684.10 rows=79 width=32) (actual time=252.824..538.047 rows=6 loops=3)

  • Hash Cond: (cvp_3.place_description_version_id = v_3.id)
36. 154.279 154.279 ↑ 1.3 1,015,926 3 / 3

Parallel Seq Scan on place_description_current_version_pointer cvp_3 (cost=0.00..38,157.00 rows=1,271,900 width=16) (actual time=0.007..154.279 rows=1,015,926 loops=3)

37. 0.010 194.500 ↑ 13.9 21 3 / 3

Hash (cost=1,184.72..1,184.72 rows=292 width=32) (actual time=194.500..194.500 rows=21 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
38. 0.102 194.490 ↑ 13.9 21 3 / 3

Bitmap Heap Scan on place_description_version v_3 (cost=58.26..1,184.72 rows=292 width=32) (actual time=194.417..194.490 rows=21 loops=3)

  • Recheck Cond: ((name)::text ~~* 'pias%'::text)
  • Rows Removed by Index Recheck: 5
  • Filter: (confidence >= '0'::numeric)
  • Heap Blocks: exact=26
39. 194.388 194.388 ↑ 11.2 26 3 / 3

Bitmap Index Scan on place_description_version_trigram_ix5 (cost=0.00..58.19 rows=292 width=0) (actual time=194.388..194.388 rows=26 loops=3)

  • Index Cond: ((name)::text ~~* 'pias%'::text)
40. 39.984 39.984 ↑ 1.0 1 18 / 3

Index Scan using place_description_pkey on place_description e_3 (cost=0.43..0.51 rows=1 width=16) (actual time=6.664..6.664 rows=1 loops=18)

  • Index Cond: (id = cvp_3.place_description_id)
41. 0.005 2.765 ↑ 1.0 1 3 / 3

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
42. 2.760 2.760 ↑ 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=2.759..2.760 rows=1 loops=3)

  • Index Cond: ((name)::text = 'SAFEGRAPH'::text)
43. 41.270 41.270 ↑ 1.0 1 15 / 3

Index Only Scan using address_pkey on address a (cost=0.43..8.39 rows=1 width=8) (actual time=8.254..8.254 rows=1 loops=15)

  • Index Cond: (id = v_3.address_id)
  • Heap Fetches: 4
44. 34.210 34.210 ↑ 1.0 1 15 / 3

Index Only Scan using geocode_pkey on geocode g (cost=0.43..8.39 rows=1 width=8) (actual time=6.842..6.842 rows=1 loops=15)

  • Index Cond: (id = v_3.geocode_id)
  • Heap Fetches: 4
45. 0.090 0.090 ↑ 1.0 1 15

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

  • Index Cond: (id = v_3.representative_id)
46. 40.905 40.905 ↑ 1.0 1 15

Index Only Scan using place_description_version_pkey on place_description_version v (cost=0.43..0.62 rows=1 width=8) (actual time=2.727..2.727 rows=1 loops=15)

  • Index Cond: (id = cvp.place_description_version_id)
  • Heap Fetches: 15
47.          

SubPlan (for Nested Loop)

48. 0.210 0.660 ↑ 1.0 1 15

Aggregate (cost=139.16..139.17 rows=1 width=32) (actual time=0.044..0.044 rows=1 loops=15)

49. 0.060 0.450 ↑ 10.0 1 15

Nested Loop (cost=1.29..139.11 rows=10 width=24) (actual time=0.029..0.030 rows=1 loops=15)

50. 0.120 0.330 ↑ 10.0 1 15

Nested Loop (cost=0.86..132.95 rows=10 width=32) (actual time=0.021..0.022 rows=1 loops=15)

51. 0.120 0.120 ↑ 10.0 1 15

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

  • Index Cond: (id = ANY ((array_agg(e_2.id))))
52. 0.090 0.090 ↑ 1.0 1 15

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.006..0.006 rows=1 loops=15)

  • Index Cond: (place_description_id = e_4.id)
53. 0.060 0.060 ↑ 1.0 1 15

Index Only Scan using place_description_version_pkey on place_description_version v_4 (cost=0.43..0.62 rows=1 width=8) (actual time=0.004..0.004 rows=1 loops=15)

  • Index Cond: (id = cvp_4.place_description_version_id)
  • Heap Fetches: 15
Planning time : 317.935 ms
Execution time : 36,019.436 ms