explain.depesz.com

PostgreSQL's explain analyze made readable

Result: KipD

Settings
# exclusive inclusive rows x rows loops node
1. 0.037 1,212,755.681 ↑ 1.0 3 1

Limit (cost=403,795.74..403,795.85 rows=3 width=743) (actual time=1,212,755.608..1,212,755.681 rows=3 loops=1)

2. 0.039 1,212,755.644 ↑ 1.0 3 1

Unique (cost=403,795.74..403,795.85 rows=3 width=743) (actual time=1,212,755.598..1,212,755.644 rows=3 loops=1)

3. 0.046 1,212,755.605 ↑ 1.0 3 1

Sort (cost=403,795.74..403,795.75 rows=3 width=743) (actual time=1,212,755.588..1,212,755.605 rows=3 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: 25kB
4. 0.067 1,212,755.559 ↑ 1.0 3 1

WindowAgg (cost=403,672.59..403,795.72 rows=3 width=743) (actual time=1,212,755.538..1,212,755.559 rows=3 loops=1)

5. 128.815 1,212,755.492 ↑ 1.0 3 1

GroupAggregate (cost=403,672.59..403,795.66 rows=3 width=671) (actual time=1,212,620.149..1,212,755.492 rows=3 loops=1)

  • Group Key: ap.applicationid, cl.clientname
6. 306.208 1,212,626.677 ↓ 2.4 29,577 1

Sort (cost=403,672.59..403,703.35 rows=12,304 width=663) (actual time=1,212,499.032..1,212,626.677 rows=29,577 loops=1)

  • Sort Key: ap.applicationid DESC NULLS LAST, cl.clientname
  • Sort Method: quicksort Memory: 4,928kB
7. 418.619 1,212,320.469 ↓ 2.4 29,577 1

Nested Loop (cost=1,223.56..402,836.73 rows=12,304 width=663) (actual time=267.368..1,212,320.469 rows=29,577 loops=1)

  • Join Filter: (ap.state = ds.datastateid)
8. 279.396 1,211,783.542 ↓ 2.4 29,577 1

Hash Join (cost=1,223.56..402,374.29 rows=12,304 width=663) (actual time=267.328..1,211,783.542 rows=29,577 loops=1)

  • Hash Cond: (ap.applicationid = (fn_get_all_entities('71'::bigint, 'network'::character varying, '1'::bigint)))
9. 282.796 1,211,489.107 ↓ 1.2 29,577 1

Hash Left Join (cost=1,201.29..402,150.54 rows=24,609 width=663) (actual time=252.272..1,211,489.107 rows=29,577 loops=1)

  • Hash Cond: (aa.asset_id = i.assetid)
10. 190,418.225 1,211,030.268 ↓ 6.7 29,513 1

Nested Loop Left Join (cost=0.00..400,245.92 rows=4,417 width=671) (actual time=76.170..1,211,030.268 rows=29,513 loops=1)

  • Join Filter: (SubPlan 1)
  • Rows Removed by Join Filter: 3,155,487
11. 60.716 112.043 ↓ 357.1 2,500 1

Nested Loop Left Join (cost=0.00..150.20 rows=7 width=671) (actual time=9.610..112.043 rows=2,500 loops=1)

  • Join Filter: (aa.application_id = ap.applicationid)
  • Rows Removed by Join Filter: 8,276
12. 0.090 0.765 ↓ 3.0 3 1

Nested Loop (cost=0.00..46.40 rows=1 width=663) (actual time=0.213..0.765 rows=3 loops=1)

  • Join Filter: (apg.applicationgroupid = ap.applicationgroupid)
  • Rows Removed by Join Filter: 6
13. 0.089 0.183 ↓ 3.0 3 1

Nested Loop (cost=0.00..2.08 rows=1 width=524) (actual time=0.038..0.183 rows=3 loops=1)

  • Join Filter: (apg.clientid = cl.clientid)
  • Rows Removed by Join Filter: 6
14. 0.034 0.034 ↓ 3.0 3 1

Seq Scan on client cl (cost=0.00..1.01 rows=1 width=524) (actual time=0.014..0.034 rows=3 loops=1)

  • Filter: is_active
15. 0.060 0.060 ↑ 1.0 3 3

Seq Scan on applicationgroup apg (cost=0.00..1.03 rows=3 width=16) (actual time=0.006..0.020 rows=3 loops=3)

16. 0.492 0.492 ↑ 1.0 3 3

Seq Scan on application ap (cost=0.00..44.29 rows=3 width=155) (actual time=0.142..0.164 rows=3 loops=3)

  • Filter: (assetgrouptypeordinal = 3)
  • Rows Removed by Filter: 500
17. 50.562 50.562 ↓ 1.0 3,592 3

Seq Scan on application_asset aa (cost=0.00..58.91 rows=3,591 width=16) (actual time=0.010..16.854 rows=3,592 loops=3)

18. 14,034.018 14,040.000 ↓ 1.0 1,274 2,500

Materialize (cost=0.00..171.93 rows=1,262 width=16) (actual time=0.005..5.616 rows=1,274 loops=2,500)

19. 5.982 5.982 ↓ 1.0 1,274 1

Seq Scan on project prj (cost=0.00..165.62 rows=1,262 width=16) (actual time=0.012..5.982 rows=1,274 loops=1)

20.          

SubPlan (for Nested Loop Left Join)

21. 356,720.000 1,006,460.000 ↑ 1.0 12 3,185,000

HashAggregate (cost=45.18..45.30 rows=12 width=8) (actual time=0.265..0.316 rows=12 loops=3,185,000)

  • Group Key: pa.project_id
22. 372,645.000 649,740.000 ↑ 1.0 12 3,185,000

Append (cost=4.07..45.15 rows=12 width=8) (actual time=0.027..0.204 rows=12 loops=3,185,000)

23. 213,395.000 232,505.000 ↓ 1.3 12 3,185,000

Bitmap Heap Scan on project_asset pa (cost=4.07..34.52 rows=9 width=8) (actual time=0.016..0.073 rows=12 loops=3,185,000)

  • Recheck Cond: (asset_id = aa.asset_id)
  • Heap Blocks: exact=37,597,014
24. 19,110.000 19,110.000 ↓ 1.3 12 3,185,000

Bitmap Index Scan on project_asset_asset_id_hash (cost=0.00..4.07 rows=9 width=0) (actual time=0.006..0.006 rows=12 loops=3,185,000)

  • Index Cond: (asset_id = aa.asset_id)
25. 28,665.000 44,590.000 ↓ 0.0 0 3,185,000

Bitmap Heap Scan on application_project (cost=4.30..10.45 rows=3 width=8) (actual time=0.014..0.014 rows=0 loops=3,185,000)

  • Recheck Cond: (application_id = ap.applicationid)
26. 15,925.000 15,925.000 ↓ 0.0 0 3,185,000

Bitmap Index Scan on fki_application_project_application_fk (cost=0.00..4.30 rows=3 width=0) (actual time=0.005..0.005 rows=0 loops=3,185,000)

  • Index Cond: (application_id = ap.applicationid)
27. 87.400 176.043 ↓ 1.0 19,986 1

Hash (cost=952.24..952.24 rows=19,924 width=24) (actual time=176.039..176.043 rows=19,986 loops=1)

  • Buckets: 32,768 Batches: 1 Memory Usage: 1,193kB
28. 88.643 88.643 ↓ 1.0 19,986 1

Seq Scan on issue i (cost=0.00..952.24 rows=19,924 width=24) (actual time=0.012..88.643 rows=19,986 loops=1)

29. 2.198 15.039 ↓ 2.5 503 1

Hash (cost=19.77..19.77 rows=200 width=8) (actual time=15.034..15.039 rows=503 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 28kB
30. 4.389 12.841 ↓ 2.5 503 1

HashAggregate (cost=17.77..19.77 rows=200 width=8) (actual time=10.672..12.841 rows=503 loops=1)

  • Group Key: fn_get_all_entities('71'::bigint, 'network'::character varying, '1'::bigint)
31. 8.439 8.452 ↑ 2.0 503 1

ProjectSet (cost=0.00..5.27 rows=1,000 width=8) (actual time=6.274..8.452 rows=503 loops=1)

32. 0.013 0.013 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.005..0.013 rows=1 loops=1)

33. 118.292 118.308 ↑ 3.0 1 29,577

Materialize (cost=0.00..1.04 rows=3 width=8) (actual time=0.004..0.004 rows=1 loops=29,577)

34. 0.016 0.016 ↑ 3.0 1 1

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

Planning time : 1.951 ms
Execution time : 1,212,756.871 ms