explain.depesz.com

PostgreSQL's explain analyze made readable

Result: BpRV : Optimization for: Optimization for: Optimization for: plan #NtYB; plan #GWNr; plan #6YFu

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.000 2,719.465 ↑ 1.0 1 1

Limit (cost=9.11..21,634.92 rows=1 width=121) (actual time=2,719.465..2,719.465 rows=1 loops=1)

2.          

Initplan (for Limit)

3. 0.011 0.011 ↑ 1.0 1 1

Seq Scan on property_subtypes (cost=0.00..1.44 rows=1 width=8) (actual time=0.008..0.011 rows=1 loops=1)

  • Filter: ((code)::text = 'attached_doc'::text)
  • Rows Removed by Filter: 34
4. 0.006 0.006 ↑ 1.0 1 1

Seq Scan on property_statuses (cost=0.00..1.29 rows=1 width=8) (actual time=0.003..0.006 rows=1 loops=1)

  • Filter: ((description)::text = 'In Digital Files'::text)
  • Rows Removed by Filter: 22
5. 16.622 2,719.463 ↑ 186,561.0 1 1

Nested Loop (cost=6.38..4,034,532,342.09 rows=186,561 width=121) (actual time=2,719.463..2,719.463 rows=1 loops=1)

6. 0.007 2,702.831 ↑ 189,119.0 1 1

Nested Loop (cost=5.95..4,034,341,182.13 rows=189,119 width=97) (actual time=2,702.831..2,702.831 rows=1 loops=1)

7. 0.003 2,702.767 ↑ 40,281.0 1 1

Nested Loop (cost=5.52..4,034,284,025.67 rows=40,281 width=103) (actual time=2,702.767..2,702.767 rows=1 loops=1)

8. 373.693 2,702.751 ↑ 40,281.0 1 1

Nested Loop (cost=5.10..4,034,264,012.82 rows=40,281 width=89) (actual time=2,702.751..2,702.751 rows=1 loops=1)

  • Join Filter: (d.documentid = du.documentid)
  • Rows Removed by Join Filter: 3,716,498
9. 0.042 0.118 ↑ 786,351.5 2 1

Nested Loop (cost=0.43..966,870.17 rows=1,572,703 width=94) (actual time=0.062..0.118 rows=2 loops=1)

10. 0.004 0.004 ↑ 196,587.9 8 1

Seq Scan on doc_index di (cost=0.00..59,553.03 rows=1,572,703 width=69) (actual time=0.003..0.004 rows=8 loops=1)

11. 0.072 0.072 ↓ 0.0 0 8

Index Scan using yrk_attach_index on yrk_documentindex d (cost=0.43..0.57 rows=1 width=25) (actual time=0.009..0.009 rows=0 loops=8)

  • Index Cond: (documentid = split_part(di.filename, '.'::text, 1))
12. 723.209 2,328.940 ↓ 14.4 1,858,250 2

Materialize (cost=4.66..211,340.77 rows=128,962 width=13) (actual time=6.300..1,164.470 rows=1,858,250 loops=2)

13. 1,087.480 1,605.731 ↓ 23.6 3,046,230 1

Hash Join (cost=4.66..210,065.96 rows=128,962 width=13) (actual time=12.567..1,605.731 rows=3,046,230 loops=1)

  • Hash Cond: (du.usagetype = ut.usagetype)
14. 518.219 518.219 ↑ 1.0 5,585,212 1

Seq Scan on yrk_documentusage du (cost=0.00..187,815.40 rows=5,588,340 width=17) (actual time=12.520..518.219 rows=5,585,212 loops=1)

15. 0.003 0.032 ↑ 1.0 3 1

Hash (cost=4.62..4.62 rows=3 width=4) (actual time=0.032..0.032 rows=3 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
16. 0.029 0.029 ↑ 1.0 3 1

Seq Scan on yrk_globalusagetype ut (cost=0.00..4.62 rows=3 width=4) (actual time=0.009..0.029 rows=3 loops=1)

  • Filter: (usageid = 'IncidentID'::text)
  • Rows Removed by Filter: 127
17. 0.013 0.013 ↑ 1.0 1 1

Index Scan using yrk_incident_index on yrk_leincident i (cost=0.43..0.49 rows=1 width=18) (actual time=0.013..0.013 rows=1 loops=1)

  • Index Cond: (incidentid = du.usagekey)
18. 0.057 0.057 ↑ 30.0 1 1

Index Scan using yrk_cfs_incnum on yrk_callforservice c (cost=0.43..1.12 rows=30 width=22) (actual time=0.057..0.057 rows=1 loops=1)

  • Index Cond: (incidentnumber = i.incidentnumber)
19. 0.010 0.010 ↑ 1.0 1 1

Index Scan using stg_combined_incidents_wtrun_convertedid_idx on stg_combined_incidents sci (cost=0.43..0.50 rows=1 width=40) (actual time=0.010..0.010 rows=1 loops=1)

  • Index Cond: ((wtrun = 101) AND (convertedid = c.yrk_callforservice_zid))
Planning time : 38.702 ms
Execution time : 2,734.020 ms