explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 5BtC

Settings
# exclusive inclusive rows x rows loops node
1. 0.003 412.193 ↑ 1.7 18 1

Limit (cost=44,018.97..44,020.10 rows=30 width=270) (actual time=412.182..412.193 rows=18 loops=1)

2. 0.009 412.190 ↑ 94.2 18 1

Unique (cost=44,018.97..44,082.57 rows=1,696 width=270) (actual time=412.181..412.190 rows=18 loops=1)

3. 0.015 412.181 ↑ 94.2 18 1

Sort (cost=44,018.97..44,023.21 rows=1,696 width=270) (actual time=412.180..412.181 rows=18 loops=1)

  • Sort Key: ap.applicationid DESC NULLS LAST, cl.clientname, ap.name, ap.state, ap.bi_state, ap.owner, (to_char(ap.created_on, 'yyyy-mm-dd'::text)), (to_char(ap.updated_on, 'yyyy-mm-dd'::text)), ap.short_name, ap.internetfacing, ap.pci_compliant, ap.sox_compliant, (count(*)), (count(*) OVER (?))
  • Sort Method: quicksort Memory: 27kB
4. 0.034 412.166 ↑ 94.2 18 1

WindowAgg (cost=43,864.40..43,928.00 rows=1,696 width=270) (actual time=412.153..412.166 rows=18 loops=1)

5. 0.018 412.132 ↑ 94.2 18 1

GroupAggregate (cost=43,864.40..43,898.32 rows=1,696 width=198) (actual time=412.121..412.132 rows=18 loops=1)

  • Group Key: ap.applicationid, cl.clientname
6. 0.015 412.114 ↑ 89.3 19 1

Sort (cost=43,864.40..43,868.64 rows=1,696 width=190) (actual time=412.112..412.114 rows=19 loops=1)

  • Sort Key: ap.applicationid DESC NULLS LAST, cl.clientname
  • Sort Method: quicksort Memory: 27kB
7. 0.017 412.099 ↑ 89.3 19 1

Nested Loop (cost=60.63..43,773.42 rows=1,696 width=190) (actual time=48.945..412.099 rows=19 loops=1)

  • Join Filter: (ap.state = ds.datastateid)
8. 6.209 412.082 ↑ 89.3 19 1

Hash Join (cost=60.63..43,708.78 rows=1,696 width=190) (actual time=48.937..412.082 rows=19 loops=1)

  • Hash Cond: (ap.applicationid = (fn_get_all_entities('711'::bigint, 'network'::character varying, '2'::bigint)))
9. 15.284 357.030 ↓ 20.3 68,712 1

Nested Loop Left Join (cost=38.37..43,658.74 rows=3,393 width=190) (actual time=0.088..357.030 rows=68,712 loops=1)

10. 1.924 8.308 ↓ 8.7 9,807 1

Nested Loop Left Join (cost=1.26..1,588.52 rows=1,131 width=198) (actual time=0.042..8.308 rows=9,807 loops=1)

11. 0.107 1.038 ↓ 1.5 198 1

Nested Loop Left Join (cost=0.84..212.07 rows=129 width=198) (actual time=0.031..1.038 rows=198 loops=1)

12. 0.023 0.792 ↓ 1.1 139 1

Nested Loop (cost=0.70..170.88 rows=129 width=190) (actual time=0.023..0.792 rows=139 loops=1)

13. 0.034 0.491 ↓ 1.0 139 1

Nested Loop (cost=0.42..114.55 rows=137 width=185) (actual time=0.015..0.491 rows=139 loops=1)

14. 0.179 0.179 ↓ 1.0 139 1

Index Scan Backward using application_id_idx on application ap (cost=0.15..26.47 rows=137 width=185) (actual time=0.008..0.179 rows=139 loops=1)

  • Filter: (assetgrouptypeordinal = 3)
  • Rows Removed by Filter: 185
15. 0.278 0.278 ↑ 1.0 1 139

Index Scan using applicationgroup_id on applicationgroup apg (cost=0.28..0.64 rows=1 width=16) (actual time=0.002..0.002 rows=1 loops=139)

  • Index Cond: (applicationgroupid = ap.applicationgroupid)
16. 0.278 0.278 ↑ 1.0 1 139

Index Scan using client_pkey on client cl (cost=0.28..0.41 rows=1 width=21) (actual time=0.002..0.002 rows=1 loops=139)

  • Index Cond: (clientid = apg.clientid)
  • Filter: is_active
17. 0.139 0.139 ↑ 2.0 1 139

Index Only Scan using application_asset_app_id_incl on application_asset aa (cost=0.15..0.30 rows=2 width=16) (actual time=0.001..0.001 rows=1 loops=139)

  • Index Cond: (application_id = ap.applicationid)
  • Heap Fetches: 52
18. 5.346 5.346 ↓ 8.2 49 198

Index Scan using issue_assetid_index on issue i (cost=0.42..10.61 rows=6 width=24) (actual time=0.002..0.027 rows=49 loops=198)

  • Index Cond: (assetid = aa.asset_id)
19. 29.421 333.438 ↓ 2.3 7 9,807

HashAggregate (cost=37.11..37.14 rows=3 width=25) (actual time=0.033..0.034 rows=7 loops=9,807)

  • Group Key: p.projectid, p.is_active, p.projectstatusid, p.projectenddate
20. 9.807 304.017 ↓ 2.3 7 9,807

Append (cost=0.28..37.08 rows=3 width=25) (actual time=0.004..0.031 rows=7 loops=9,807)

21. 59.132 156.912 ↓ 7.0 7 9,807

Nested Loop (cost=0.28..16.32 rows=1 width=25) (actual time=0.003..0.016 rows=7 loops=9,807)

22. 29.421 29.421 ↓ 7.0 7 9,807

Index Scan using project_asset_asset_id_hash on project_asset pa (cost=0.00..8.02 rows=1 width=8) (actual time=0.001..0.003 rows=7 loops=9,807)

  • Index Cond: (asset_id = aa.asset_id)
23. 68.359 68.359 ↑ 1.0 1 68,359

Index Scan using project_projectid_clientid on project p (cost=0.28..8.30 rows=1 width=25) (actual time=0.001..0.001 rows=1 loops=68,359)

  • Index Cond: (projectid = pa.project_id)
24. 9.374 137.298 ↓ 0.0 0 9,807

Nested Loop (cost=0.28..20.71 rows=2 width=25) (actual time=0.014..0.014 rows=0 loops=9,807)

25. 127.491 127.491 ↓ 0.0 0 9,807

Seq Scan on application_project ap_prj (cost=0.00..4.11 rows=2 width=8) (actual time=0.012..0.013 rows=0 loops=9,807)

  • Filter: (application_id = ap.applicationid)
  • Rows Removed by Filter: 179
26. 0.433 0.433 ↑ 1.0 1 433

Index Scan using project_projectid_clientid on project p_1 (cost=0.28..8.30 rows=1 width=25) (actual time=0.001..0.001 rows=1 loops=433)

  • Index Cond: (projectid = ap_prj.project_id)
27. 0.010 48.843 ↑ 4.7 43 1

Hash (cost=19.77..19.77 rows=200 width=8) (actual time=48.843..48.843 rows=43 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
28. 0.017 48.833 ↑ 4.7 43 1

HashAggregate (cost=17.77..19.77 rows=200 width=8) (actual time=48.828..48.833 rows=43 loops=1)

  • Group Key: fn_get_all_entities('711'::bigint, 'network'::character varying, '2'::bigint)
29. 48.815 48.816 ↑ 23.3 43 1

ProjectSet (cost=0.00..5.27 rows=1,000 width=8) (actual time=48.810..48.816 rows=43 loops=1)

30. 0.001 0.001 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.000..0.001 rows=1 loops=1)

31. 0.000 0.000 ↑ 3.0 1 19

Materialize (cost=0.00..1.04 rows=3 width=8) (actual time=0.000..0.000 rows=1 loops=19)

32. 0.005 0.005 ↑ 3.0 1 1

Seq Scan on datastate ds (cost=0.00..1.03 rows=3 width=8) (actual time=0.005..0.005 rows=1 loops=1)

Planning time : 2.408 ms
Execution time : 412.361 ms