explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Lh2k

Settings
# exclusive inclusive rows x rows loops node
1. 0.103 2.535 ↓ 41.0 41 1

Nested Loop (cost=4.46..201.31 rows=1 width=1,106) (actual time=0.189..2.535 rows=41 loops=1)

2. 0.067 1.243 ↓ 41.0 41 1

Nested Loop (cost=4.18..161.03 rows=1 width=1,012) (actual time=0.108..1.243 rows=41 loops=1)

  • Join Filter: ((h.house_address_guid)::text = (fiashousea2_.fias_house_address_id)::text)
3. 0.075 1.094 ↓ 41.0 41 1

Nested Loop Anti Join (cost=3.90..159.47 rows=1 width=323) (actual time=0.101..1.094 rows=41 loops=1)

4. 0.059 0.732 ↓ 41.0 41 1

Nested Loop (cost=3.62..156.93 rows=1 width=323) (actual time=0.087..0.732 rows=41 loops=1)

5. 0.175 0.222 ↑ 1.2 41 1

Bitmap Heap Scan on houses h (cost=3.34..77.88 rows=50 width=286) (actual time=0.064..0.222 rows=41 loops=1)

  • Recheck Cond: (((license_guid)::text = '61ca530a-7034-4eb1-925b-c334d76c2520'::text) OR (((original_license_guid)::text = '61ca530a-7034-4eb1-925b-c334d76c2520'::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: 7
  • Heap Blocks: exact=26
6. 0.001 0.047 ↓ 0.0 0 1

BitmapOr (cost=3.34..3.34 rows=88 width=0) (actual time=0.046..0.047 rows=0 loops=1)

7. 0.027 0.027 ↑ 1.0 48 1

Bitmap Index Scan on fki_houses_license_guid (cost=0.00..1.66 rows=50 width=0) (actual time=0.026..0.027 rows=48 loops=1)

  • Index Cond: ((license_guid)::text = '61ca530a-7034-4eb1-925b-c334d76c2520'::text)
8. 0.019 0.019 ↓ 1.3 48 1

Bitmap Index Scan on fki_lcs_houses_original_license_guid (cost=0.00..1.66 rows=38 width=0) (actual time=0.019..0.019 rows=48 loops=1)

  • Index Cond: (((original_license_guid)::text = '61ca530a-7034-4eb1-925b-c334d76c2520'::text) AND (original_license_guid IS NOT NULL))
9. 0.451 0.451 ↑ 1.0 1 41

Index Scan using pk_fias_house_address on fias_house_address fiashousea1_ (cost=0.28..1.58 rows=1 width=37) (actual time=0.011..0.011 rows=1 loops=41)

  • Index Cond: ((fias_house_address_id)::text = (h.house_address_guid)::text)
  • Filter: ((region_root_guid)::text = 'df594e0e-a935-4664-9d26-0bae13f904fe'::text)
10. 0.287 0.287 ↓ 0.0 0 41

Index Scan using fki_houses_house_address_guid on houses h2 (cost=0.28..2.53 rows=1 width=87) (actual time=0.007..0.007 rows=0 loops=41)

  • 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: 1
11. 0.082 0.082 ↑ 1.0 1 41

Index Scan using pk_fias_house_address on fias_house_address fiashousea2_ (cost=0.28..1.55 rows=1 width=726) (actual time=0.002..0.002 rows=1 loops=41)

  • Index Cond: ((fias_house_address_id)::text = (fiashousea1_.fias_house_address_id)::text)
12. 0.205 0.205 ↑ 1.0 1 41

Index Scan using pk_root_houses on root_houses houseroote3_ (cost=0.28..1.28 rows=1 width=90) (actual time=0.005..0.005 rows=1 loops=41)

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

SubPlan (forNested Loop)

14. 0.000 0.984 ↑ 1.0 1 41

Limit (cost=38.75..39.00 rows=1 width=4) (actual time=0.023..0.024 rows=1 loops=41)

15.          

CTE group_rows

16. 0.123 0.697 ↑ 11.0 1 41

Recursive Union (cost=0.28..38.49 rows=11 width=45) (actual time=0.007..0.017 rows=1 loops=41)

17. 0.246 0.246 ↑ 1.0 1 41

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

  • Index Cond: ((house_id)::text = (h.house_id)::text)
18. 0.082 0.328 ↓ 0.0 0 41

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

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

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

20. 0.041 0.205 ↑ 1.0 1 41

Hash (cost=3.32..3.32 rows=1 width=45) (actual time=0.005..0.005 rows=1 loops=41)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
21. 0.164 0.164 ↑ 1.0 1 41

Index Scan using fki_houses_house_address_guid on houses hs_1 (cost=0.28..3.32 rows=1 width=45) (actual time=0.003..0.004 rows=1 loops=41)

  • 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: 0
22.          

Initplan (forLimit)

23. 0.082 0.533 ↑ 1.0 1 41

Aggregate (cost=0.25..0.26 rows=1 width=4) (actual time=0.012..0.013 rows=1 loops=41)

24. 0.451 0.451 ↑ 11.0 1 41

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

25. 0.943 0.943 ↑ 1.0 1 41

CTE Scan on group_rows group_rows_1 (cost=0.00..0.25 rows=1 width=4) (actual time=0.022..0.023 rows=1 loops=41)

  • Filter: (base_document_period_from = $6)