explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 2aZE

Settings
# exclusive inclusive rows x rows loops node
1. 0.081 408.389 ↑ 1.0 100 1

Limit (cost=106,930.75..358,696.65 rows=100 width=629) (actual time=285.830..408.389 rows=100 loops=1)

2. 4.377 408.308 ↑ 54.3 100 1

Hash Right Join (cost=106,930.75..13,782,854.31 rows=5,432 width=629) (actual time=285.828..408.308 rows=100 loops=1)

  • Hash Cond: (pr.holding_id = r.holding_id)
3. 23.951 143.229 ↑ 87.5 6,164 1

Nested Loop (cost=0.00..13,628,677.18 rows=539,633 width=122) (actual time=12.158..143.229 rows=6,164 loops=1)

  • Join Filter: (pr.type_id = prt.id)
  • Rows Removed by Join Filter: 24274
4. 71.803 103.453 ↑ 102.3 15,825 1

Seq Scan on propright pr (cost=0.00..13,582,807.32 rows=1,618,898 width=64) (actual time=12.036..103.453 rows=15,825 loops=1)

  • Filter: ((COALESCE((current_setting('session.workdate'::text, true))::date, (now())::date) >= startdate) AND (COALESCE((current_setting('session.workdate'::text, true))::date, (now())::date) <= enddate))
  • Rows Removed by Filter: 1035
5.          

SubPlan (forSeq Scan)

6. 31.650 31.650 ↑ 1.0 1 15,825

Index Scan using subject_pkey on subject s (cost=0.29..8.30 rows=1 width=114) (actual time=0.002..0.002 rows=1 loops=15,825)

  • Index Cond: (pr.subject_id = id)
7. 15.812 15.825 ↑ 1.0 2 15,825

Materialize (cost=0.00..1.08 rows=2 width=90) (actual time=0.000..0.001 rows=2 loops=15,825)

8. 0.013 0.013 ↑ 1.0 2 1

Seq Scan on proprighttype prt (cost=0.00..1.07 rows=2 width=90) (actual time=0.011..0.013 rows=2 loops=1)

  • Filter: (syscode = ANY ('{OPER_UPR,HOS_VED}'::text[]))
  • Rows Removed by Filter: 4
9. 14.271 260.702 ↓ 2.4 12,937 1

Hash (cost=106,862.85..106,862.85 rows=5,432 width=507) (actual time=260.702..260.702 rows=12,937 loops=1)

  • Buckets: 16384 (originally 8192) Batches: 2 (originally 1) Memory Usage: 3969kB
10. 19.706 246.431 ↓ 2.4 12,937 1

Nested Loop Left Join (cost=7,115.07..106,862.85 rows=5,432 width=507) (actual time=42.155..246.431 rows=12,937 loops=1)

11. 11.906 123.229 ↓ 2.4 12,937 1

Hash Left Join (cost=7,106.59..60,650.11 rows=5,432 width=487) (actual time=42.129..123.229 rows=12,937 loops=1)

  • Hash Cond: (r.buildingtype_id = bt.id)
12. 12.061 111.156 ↓ 2.4 12,937 1

Hash Left Join (cost=7,100.68..60,575.70 rows=5,432 width=466) (actual time=41.957..111.156 rows=12,937 loops=1)

  • Hash Cond: (r.kni_id = k.id)
13. 11.478 98.534 ↓ 2.4 12,937 1

Hash Left Join (cost=7,078.23..60,484.70 rows=5,432 width=426) (actual time=41.392..98.534 rows=12,937 loops=1)

  • Hash Cond: (r.inreestrreason_id = inr.id)
14. 10.448 87.029 ↓ 2.4 12,937 1

Hash Left Join (cost=7,076.78..60,414.81 rows=5,432 width=392) (actual time=41.359..87.029 rows=12,937 loops=1)

  • Hash Cond: (r.outreestrreason_id = outr.id)
15. 13.078 76.550 ↓ 2.4 12,937 1

Nested Loop (cost=7,075.47..60,345.01 rows=5,432 width=359) (actual time=41.313..76.550 rows=12,937 loops=1)

16. 0.013 0.013 ↑ 1.0 1 1

Seq Scan on reestrtype rt (cost=0.00..1.35 rows=1 width=84) (actual time=0.008..0.013 rows=1 loops=1)

  • Filter: (syscode = 'building'::text)
  • Rows Removed by Filter: 27
17. 23.236 63.459 ↑ 1.5 12,937 1

Bitmap Heap Scan on reestr r (cost=7,075.47..60,153.53 rows=19,013 width=307) (actual time=41.300..63.459 rows=12,937 loops=1)

  • Recheck Cond: ((type_id = rt.id) AND (owner_id = ANY ('{b3236e73-03d2-33de-381c-30c4fd860b44}'::uuid[])))
  • Filter: ((COALESCE((current_setting('session.workdate'::text, true))::date, (now())::date) >= startdate) AND (COALESCE((current_setting('session.workdate'::text, true))::date, (now())::date) <= enddate))
  • Rows Removed by Filter: 948
  • Heap Blocks: exact=7289
18. 1.066 40.223 ↓ 0.0 0 1

BitmapAnd (cost=7,075.47..7,075.47 rows=20,356 width=0) (actual time=40.223..40.223 rows=0 loops=1)

19. 23.957 23.957 ↓ 2.3 320,436 1

Bitmap Index Scan on reestr_type_id_idx (cost=0.00..3,210.66 rows=136,831 width=0) (actual time=23.957..23.957 rows=320,436 loops=1)

  • Index Cond: (type_id = rt.id)
20. 15.200 15.200 ↓ 1.0 168,666 1

Bitmap Index Scan on reestr_owner_id_idx (cost=0.00..3,821.78 rows=162,847 width=0) (actual time=15.200..15.200 rows=168,666 loops=1)

  • Index Cond: (owner_id = ANY ('{b3236e73-03d2-33de-381c-30c4fd860b44}'::uuid[]))
21. 0.012 0.031 ↑ 1.0 14 1

Hash (cost=1.14..1.14 rows=14 width=65) (actual time=0.031..0.031 rows=14 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
22. 0.019 0.019 ↑ 1.0 14 1

Seq Scan on outreestrreason outr (cost=0.00..1.14 rows=14 width=65) (actual time=0.012..0.019 rows=14 loops=1)

23. 0.012 0.027 ↑ 1.0 20 1

Hash (cost=1.20..1.20 rows=20 width=66) (actual time=0.027..0.027 rows=20 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
24. 0.015 0.015 ↑ 1.0 20 1

Seq Scan on inreestrreason inr (cost=0.00..1.20 rows=20 width=66) (actual time=0.006..0.015 rows=20 loops=1)

25. 0.277 0.561 ↑ 1.0 553 1

Hash (cost=15.53..15.53 rows=553 width=72) (actual time=0.561..0.561 rows=553 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 65kB
26. 0.284 0.284 ↑ 1.0 553 1

Seq Scan on kni k (cost=0.00..15.53 rows=553 width=72) (actual time=0.006..0.284 rows=553 loops=1)

27. 0.085 0.167 ↑ 1.0 174 1

Hash (cost=3.74..3.74 rows=174 width=53) (actual time=0.167..0.167 rows=174 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 23kB
28. 0.082 0.082 ↑ 1.0 174 1

Seq Scan on buildingtype bt (cost=0.00..3.74 rows=174 width=53) (actual time=0.006..0.082 rows=174 loops=1)

29. 12.937 103.496 ↑ 1.0 1 12,937

Limit (cost=8.48..8.49 rows=1 width=24) (actual time=0.008..0.008 rows=1 loops=12,937)

30. 25.874 90.559 ↑ 1.0 1 12,937

Sort (cost=8.48..8.49 rows=1 width=24) (actual time=0.007..0.007 rows=1 loops=12,937)

  • Sort Key: hs.startdate DESC
  • Sort Method: quicksort Memory: 25kB
31. 64.685 64.685 ↑ 1.0 1 12,937

Index Scan using holdingstate_holding_id_idx on holdingstate hs (cost=0.42..8.47 rows=1 width=24) (actual time=0.005..0.005 rows=1 loops=12,937)

  • Index Cond: (holding_id = r.holding_id)
  • Filter: ((COALESCE((current_setting('session.workdate'::text, true))::date, (now())::date) >= startdate) AND (COALESCE((current_setting('session.workdate'::text, true))::date, (now())::date) <= enddate))
  • Rows Removed by Filter: 0