explain.depesz.com

PostgreSQL's explain analyze made readable

Result: qECg

Settings
# exclusive inclusive rows x rows loops node
1. 0.031 213.331 ↑ 824.5 2 1

Merge Join (cost=109,702.40..115,903.72 rows=1,649 width=764) (actual time=213.328..213.331 rows=2 loops=1)

  • Merge Cond: (permit.id = permit_1.id)
2. 0.020 17.540 ↑ 354.5 2 1

Sort (cost=36,736.54..36,738.31 rows=709 width=764) (actual time=17.540..17.540 rows=2 loops=1)

  • Sort Key: permit.id
  • Sort Method: quicksort Memory: 26kB
3. 0.011 17.520 ↑ 354.5 2 1

Nested Loop (cost=5,538.57..36,702.97 rows=709 width=764) (actual time=10.071..17.520 rows=2 loops=1)

4. 0.044 17.491 ↑ 354.5 2 1

Hash Join (cost=5,538.29..36,239.24 rows=709 width=739) (actual time=10.057..17.491 rows=2 loops=1)

  • Hash Cond: (permit.permitpublicspacemanagerid = psm.id)
5. 0.067 14.736 ↑ 354.5 2 1

Hash Join (cost=4,850.92..35,550.01 rows=709 width=710) (actual time=7.307..14.736 rows=2 loops=1)

  • Hash Cond: (permit.permitownerorganisationid = organisation.id)
6. 4.049 12.572 ↑ 354.5 2 1

Bitmap Heap Scan on permit (cost=4,163.55..34,860.78 rows=709 width=677) (actual time=5.151..12.572 rows=2 loops=1)

  • Recheck Cond: (permitstatustypeid = 6)
  • Filter: (SubPlan 1)
  • Rows Removed by Filter: 4830
  • Heap Blocks: exact=2568
7. 3.691 3.691 ↑ 1.0 4,832 1

Bitmap Index Scan on ix_permit_owner (cost=0.00..4,163.37 rows=4,920 width=0) (actual time=3.691..3.691 rows=4,832 loops=1)

  • Index Cond: (permitstatustypeid = 6)
8.          

SubPlan (forBitmap Heap Scan)

9. 4.832 4.832 ↓ 0.0 0 4,832

Index Scan using ix_permitadvisor_permitid on permitadvisor (cost=0.29..9.14 rows=1 width=4) (actual time=0.001..0.001 rows=0 loops=4,832)

  • Index Cond: (permitid = permit.id)
  • Filter: (userid = 13459)
  • Rows Removed by Filter: 0
10. 1.011 2.097 ↑ 1.0 6,372 1

Hash (cost=607.72..607.72 rows=6,372 width=33) (actual time=2.097..2.097 rows=6,372 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 482kB
11. 1.086 1.086 ↑ 1.0 6,372 1

Seq Scan on organisation (cost=0.00..607.72 rows=6,372 width=33) (actual time=0.021..1.086 rows=6,372 loops=1)

12. 1.132 2.711 ↑ 1.0 6,372 1

Hash (cost=607.72..607.72 rows=6,372 width=33) (actual time=2.711..2.711 rows=6,372 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 482kB
13. 1.579 1.579 ↑ 1.0 6,372 1

Seq Scan on organisation psm (cost=0.00..607.72 rows=6,372 width=33) (actual time=0.010..1.579 rows=6,372 loops=1)

14. 0.018 0.018 ↑ 1.0 1 2

Index Scan using pk_organisation on organisation owner (cost=0.28..0.65 rows=1 width=33) (actual time=0.009..0.009 rows=1 loops=2)

  • Index Cond: (id = organisation.id)
15. 0.060 195.760 ↑ 719.4 491 1

Unique (cost=72,965.87..74,731.95 rows=353,216 width=4) (actual time=195.671..195.760 rows=491 loops=1)

16. 0.178 195.700 ↑ 360.1 981 1

Sort (cost=72,965.87..73,848.91 rows=353,216 width=4) (actual time=195.670..195.700 rows=981 loops=1)

  • Sort Key: permit_1.id
  • Sort Method: quicksort Memory: 71kB
17. 0.037 195.522 ↑ 359.7 982 1

Append (cost=60.50..35,586.68 rows=353,216 width=4) (actual time=30.969..195.522 rows=982 loops=1)

18. 7.053 31.127 ↑ 608.8 491 1

Merge Join (cost=60.50..9,851.45 rows=298,937 width=4) (actual time=30.967..31.127 rows=491 loops=1)

  • Merge Cond: (ot1.id = permit_1.permitpublicspacemanagerid)
19. 0.098 0.342 ↑ 1,000.0 1 1

Sort (cost=60.08..62.58 rows=1,000 width=4) (actual time=0.341..0.342 rows=1 loops=1)

  • Sort Key: ot1.id
  • Sort Method: quicksort Memory: 25kB
20. 0.244 0.244 ↑ 1,000.0 1 1

Function Scan on getorgtree ot1 (cost=0.25..10.25 rows=1,000 width=4) (actual time=0.244..0.244 rows=1 loops=1)

21. 12.435 23.732 ↑ 1.2 123,429 1

Materialize (cost=0.42..5,681.97 rows=151,860 width=8) (actual time=0.014..23.732 rows=123,429 loops=1)

22. 11.297 11.297 ↑ 1.2 123,429 1

Index Only Scan using ix_permit_owner on permit permit_1 (cost=0.42..5,302.32 rows=151,860 width=8) (actual time=0.012..11.297 rows=123,429 loops=1)

  • Heap Fetches: 0
23. 0.187 164.358 ↑ 110.5 491 1

Hash Join (cost=20,914.01..22,203.06 rows=54,279 width=4) (actual time=164.306..164.358 rows=491 loops=1)

  • Hash Cond: (ot2.id = permit_2.permitpublicspacemanagerid)
24. 0.512 0.512 ↑ 500.0 2 1

Function Scan on getparentorgtree ot2 (cost=0.25..10.25 rows=1,000 width=4) (actual time=0.511..0.512 rows=2 loops=1)

25. 9.454 163.659 ↓ 2.6 71,719 1

Hash (cost=20,569.09..20,569.09 rows=27,574 width=8) (actual time=163.659..163.659 rows=71,719 loops=1)

  • Buckets: 131072 (originally 32768) Batches: 1 (originally 1) Memory Usage: 3826kB
26. 15.295 154.205 ↓ 2.6 71,719 1

Hash Join (cost=14,422.58..20,569.09 rows=27,574 width=8) (actual time=124.618..154.205 rows=71,719 loops=1)

  • Hash Cond: (permit_2.permitownerorganisationid = co.organisationid)
27. 14.308 14.308 ↑ 1.0 151,860 1

Index Only Scan using ix_permit_owner on permit permit_2 (cost=0.42..5,302.32 rows=151,860 width=12) (actual time=0.009..14.308 rows=151,860 loops=1)

  • Heap Fetches: 0
28. 0.059 124.602 ↓ 2.8 325 1

Hash (cost=14,420.71..14,420.71 rows=116 width=4) (actual time=124.602..124.602 rows=325 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 20kB
29. 55.905 124.543 ↓ 2.8 325 1

Hash Join (cost=49.11..14,420.71 rows=116 width=4) (actual time=80.628..124.543 rows=325 loops=1)

  • Hash Cond: (co.roadworkcontractownerid = c.id)
30. 68.616 68.616 ↑ 1.0 724,234 1

Seq Scan on roadworkcontractowners co (cost=0.00..12,467.56 rows=725,103 width=8) (actual time=0.011..68.616 rows=724,234 loops=1)

  • Filter: isactive
  • Rows Removed by Filter: 82422
31. 0.002 0.022 ↓ 2.5 5 1

Hash (cost=49.09..49.09 rows=2 width=4) (actual time=0.022..0.022 rows=5 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
32. 0.020 0.020 ↓ 2.5 5 1

Index Scan using ix_roadworkcontract_roadworkcontractpublicspacemanagerid on roadworkcontract c (cost=0.29..49.09 rows=2 width=4) (actual time=0.016..0.020 rows=5 loops=1)

  • Index Cond: (roadworkcontractpublicspacemanagerid = 4731)
  • Filter: (isactive AND (validfrom <= '2019-06-03 12:32:22'::timestamp without time zone) AND (validto >= '2019-06-03 12:32:22'::timestamp without time zone))
  • Rows Removed by Filter: 10
Planning time : 2.514 ms