explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 1TjQ : smth

Settings
# exclusive inclusive rows x rows loops node
1. 0.011 0.302 ↓ 2.0 2 1

Nested Loop (cost=3.70..51.93 rows=1 width=1,106) (actual time=0.231..0.302 rows=2 loops=1)

2. 0.006 0.163 ↓ 2.0 2 1

Nested Loop (cost=3.42..10.63 rows=1 width=1,012) (actual time=0.125..0.163 rows=2 loops=1)

  • Join Filter: ((h.house_address_guid)::text = (fiashousea1_.fias_house_address_id)::text)
3. 0.006 0.149 ↓ 2.0 2 1

Nested Loop (cost=3.14..10.26 rows=1 width=1,012) (actual time=0.116..0.149 rows=2 loops=1)

4. 0.005 0.115 ↓ 2.0 2 1

Nested Loop Anti Join (cost=2.86..7.96 rows=1 width=286) (actual time=0.092..0.115 rows=2 loops=1)

5. 0.027 0.072 ↓ 2.0 2 1

Bitmap Heap Scan on houses h (cost=2.58..4.61 rows=1 width=286) (actual time=0.070..0.072 rows=2 loops=1)

  • Recheck Cond: (((license_guid)::text = '2b40b990-5acc-4182-ad36-b614e30a0cf6'::text) OR (((original_license_guid)::text = 'aeaf18f3-88be-42a5-b493-18419e06aede'::text) AND (original_license_guid IS NOT NULL)))
  • Filter: ((original_license_guid IS NOT NULL) AND ((registry_status_type)::text = ANY ('{INCLUDED,EXCLUDED}'::text[])))
  • Rows Removed by Filter: 4
  • Heap Blocks: exact=2
6. 0.002 0.045 ↓ 0.0 0 1

BitmapOr (cost=2.58..2.58 rows=2 width=0) (actual time=0.045..0.045 rows=0 loops=1)

7. 0.022 0.022 ↓ 0.0 0 1

Bitmap Index Scan on fki_houses_license_guid (cost=0.00..1.29 rows=1 width=0) (actual time=0.022..0.022 rows=0 loops=1)

  • Index Cond: ((license_guid)::text = '2b40b990-5acc-4182-ad36-b614e30a0cf6'::text)
8. 0.021 0.021 ↓ 6.0 6 1

Bitmap Index Scan on fki_lcs_houses_original_license_guid (cost=0.00..1.29 rows=1 width=0) (actual time=0.021..0.021 rows=6 loops=1)

  • Index Cond: (((original_license_guid)::text = 'aeaf18f3-88be-42a5-b493-18419e06aede'::text) AND (original_license_guid IS NOT NULL))
9. 0.038 0.038 ↓ 0.0 0 2

Index Scan using fki_houses_house_address_guid on houses h2 (cost=0.28..3.33 rows=1 width=87) (actual time=0.019..0.019 rows=0 loops=2)

  • Index Cond: ((h.house_address_guid)::text = (house_address_guid)::text)
  • Filter: (((h.registry_status_type)::text = (registry_status_type)::text) AND ((h.original_license_guid)::text = (original_license_guid)::text) AND (base_document_period_to = (h.base_document_period_from - 1)))
  • Rows Removed by Filter: 3
10. 0.028 0.028 ↑ 1.0 1 2

Index Scan using pk_fias_house_address on fias_house_address fiashousea2_ (cost=0.28..2.30 rows=1 width=726) (actual time=0.014..0.014 rows=1 loops=2)

  • Index Cond: ((fias_house_address_id)::text = (h.house_address_guid)::text)
11. 0.008 0.008 ↑ 1.0 1 2

Index Scan using pk_fias_house_address on fias_house_address fiashousea1_ (cost=0.28..0.36 rows=1 width=37) (actual time=0.004..0.004 rows=1 loops=2)

  • Index Cond: ((fias_house_address_id)::text = (fiashousea2_.fias_house_address_id)::text)
  • Filter: ((region_root_guid)::text = 'd028ec4f-f6da-4843-ada6-b68b3e0efa3d'::text)
12. 0.018 0.018 ↑ 1.0 1 2

Index Scan using pk_root_houses on root_houses houseroote3_ (cost=0.28..2.30 rows=1 width=90) (actual time=0.009..0.009 rows=1 loops=2)

  • Index Cond: ((guid)::text = (h.root_house_guid)::text)
13.          

SubPlan (forNested Loop)

14. 0.000 0.110 ↑ 1.0 1 2

Limit (cost=38.75..39.00 rows=1 width=4) (actual time=0.055..0.055 rows=1 loops=2)

15.          

CTE group_rows

16. 0.004 0.090 ↑ 11.0 1 2

Recursive Union (cost=0.28..38.49 rows=11 width=45) (actual time=0.013..0.045 rows=1 loops=2)

17. 0.026 0.026 ↑ 1.0 1 2

Index Scan using pk_house on houses hs (cost=0.28..2.30 rows=1 width=45) (actual time=0.012..0.013 rows=1 loops=2)

  • Index Cond: ((house_id)::text = (h.house_id)::text)
18. 0.036 0.060 ↓ 0.0 0 2

Hash Join (cost=3.34..3.60 rows=1 width=45) (actual time=0.030..0.030 rows=0 loops=2)

  • Hash Cond: (gr.base_document_period_to = (hs_1.base_document_period_from - 1))
19. 0.002 0.002 ↑ 10.0 1 2

WorkTable Scan on group_rows gr (cost=0.00..0.20 rows=10 width=4) (actual time=0.001..0.001 rows=1 loops=2)

20. 0.008 0.022 ↑ 1.0 1 2

Hash (cost=3.32..3.32 rows=1 width=45) (actual time=0.011..0.011 rows=1 loops=2)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
21. 0.014 0.014 ↑ 1.0 1 2

Index Scan using fki_houses_house_address_guid on houses hs_1 (cost=0.28..3.32 rows=1 width=45) (actual time=0.005..0.007 rows=1 loops=2)

  • Index Cond: ((house_address_guid)::text = (h.house_address_guid)::text)
  • Filter: (((registry_status_type)::text = (h.registry_status_type)::text) AND ((original_license_guid)::text = (h.original_license_guid)::text))
  • Rows Removed by Filter: 2
22.          

Initplan (forLimit)

23. 0.006 0.072 ↑ 1.0 1 2

Aggregate (cost=0.25..0.26 rows=1 width=4) (actual time=0.036..0.036 rows=1 loops=2)

24. 0.066 0.066 ↑ 11.0 1 2

CTE Scan on group_rows (cost=0.00..0.22 rows=11 width=4) (actual time=0.000..0.033 rows=1 loops=2)

25. 0.108 0.108 ↑ 1.0 1 2

CTE Scan on group_rows group_rows_1 (cost=0.00..0.25 rows=1 width=4) (actual time=0.054..0.054 rows=1 loops=2)

  • Filter: (base_document_period_from = $6)