explain.depesz.com

PostgreSQL's explain analyze made readable

Result: xRfL

Settings
# exclusive inclusive rows x rows loops node
1. 0.030 101.892 ↑ 824.5 2 1

Merge Join (cost=95,420.66..101,621.97 rows=1,649 width=764) (actual time=101.889..101.892 rows=2 loops=1)

  • Merge Cond: (permit.id = permit_1.id)
2. 0.086 17.282 ↑ 354.5 2 1

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

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

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

4. 0.041 17.180 ↑ 354.5 2 1

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

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

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

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

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

  • Recheck Cond: (permitstatustypeid = 6)
  • Filter: (SubPlan 1)
  • Rows Removed by Filter: 4830
  • Heap Blocks: exact=2568
7. 3.488 3.488 ↑ 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.488..3.488 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.246 2.774 ↑ 1.0 6,372 1

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

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

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

12. 1.124 2.749 ↑ 1.0 6,372 1

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

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

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

14. 0.012 0.012 ↑ 1.0 1 2

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

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

Unique (cost=58,684.12..60,450.20 rows=353,216 width=4) (actual time=84.492..84.580 rows=491 loops=1)

16. 0.190 84.520 ↑ 360.1 981 1

Sort (cost=58,684.12..59,567.16 rows=353,216 width=4) (actual time=84.490..84.520 rows=981 loops=1)

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

Append (cost=60.50..21,304.93 rows=353,216 width=4) (actual time=31.584..84.330 rows=982 loops=1)

18. 7.272 31.746 ↑ 608.8 491 1

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

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

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

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

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

21. 12.761 24.198 ↑ 1.2 123,429 1

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

22. 11.437 11.437 ↑ 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.009..11.437 rows=123,429 loops=1)

  • Heap Fetches: 0
23. 0.157 52.552 ↑ 110.5 491 1

Hash Join (cost=6,632.27..7,921.32 rows=54,279 width=4) (actual time=52.513..52.552 rows=491 loops=1)

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

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

25. 9.212 51.906 ↓ 2.6 71,719 1

Hash (cost=6,287.34..6,287.34 rows=27,574 width=8) (actual time=51.906..51.906 rows=71,719 loops=1)

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

Hash Join (cost=140.84..6,287.34 rows=27,574 width=8) (actual time=13.914..42.694 rows=71,719 loops=1)

  • Hash Cond: (permit_2.permitownerorganisationid = co.organisationid)
27. 13.972 13.972 ↑ 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.015..13.972 rows=151,860 loops=1)

  • Heap Fetches: 0
28. 0.035 13.892 ↓ 2.8 325 1

Hash (cost=138.97..138.97 rows=116 width=4) (actual time=13.892..13.892 rows=325 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 20kB
29. 0.025 13.857 ↓ 2.8 325 1

Nested Loop (cost=0.71..138.97 rows=116 width=4) (actual time=0.066..13.857 rows=325 loops=1)

30. 0.022 0.022 ↓ 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.018..0.022 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
31. 13.810 13.810 ↑ 1.4 65 5

Index Scan using ix_roadworkcontractowners_roadworkcontractownerid on roadworkcontractowners co (cost=0.42..44.00 rows=94 width=8) (actual time=2.745..2.762 rows=65 loops=5)

  • Index Cond: (roadworkcontractownerid = c.id)
  • Filter: isactive
  • Rows Removed by Filter: 6
Planning time : 3.214 ms