explain.depesz.com

PostgreSQL's explain analyze made readable

Result: VXxa

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 774.610 ↓ 0.0 0 1

Limit (cost=50.40..14,433.69 rows=25 width=596) (actual time=774.610..774.610 rows=0 loops=1)

2.          

CTE r

3. 0.002 0.043 ↑ 511.0 1 1

Recursive Union (cost=0.00..49.98 rows=511 width=16) (actual time=0.012..0.043 rows=1 loops=1)

4. 0.015 0.015 ↑ 1.0 1 1

Seq Scan on "REF_ZONES" (cost=0.00..2.79 rows=1 width=16) (actual time=0.010..0.015 rows=1 loops=1)

  • Filter: (("ID" = '666ed72f-c9c4-4f1b-a7ed-e194786cc46b'::uuid) AND ("IS_ACTIVE" = 1))
  • Rows Removed by Filter: 52
5. 0.011 0.026 ↓ 0.0 0 1

Hash Join (cost=0.33..3.70 rows=51 width=16) (actual time=0.026..0.026 rows=0 loops=1)

  • Hash Cond: ("REF_ZONES_1"."PARENT" = r_1."ID")
6. 0.011 0.011 ↑ 1.0 53 1

Seq Scan on "REF_ZONES" "REF_ZONES_1" (cost=0.00..2.66 rows=53 width=32) (actual time=0.006..0.011 rows=53 loops=1)

  • Filter: ("IS_ACTIVE" = 1)
7. 0.003 0.004 ↑ 10.0 1 1

Hash (cost=0.20..0.20 rows=10 width=16) (actual time=0.004..0.004 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
8. 0.001 0.001 ↑ 10.0 1 1

WorkTable Scan on r r_1 (cost=0.00..0.20 rows=10 width=16) (actual time=0.001..0.001 rows=1 loops=1)

9. 0.000 774.610 ↓ 0.0 0 1

Unique (cost=0.42..31,901,560.64 rows=55,449 width=596) (actual time=774.610..774.610 rows=0 loops=1)

10. 0.001 774.610 ↓ 0.0 0 1

Nested Loop Left Join (cost=0.42..31,901,422.02 rows=55,449 width=596) (actual time=774.610..774.610 rows=0 loops=1)

  • Join Filter: ((tc."IS_ACTIVE" = 1) AND (tp."ID" = rls."TYPE_POINT"))
11. 0.000 774.609 ↓ 0.0 0 1

Nested Loop Left Join (cost=0.42..1,787,367.15 rows=55,449 width=504) (actual time=774.609..774.609 rows=0 loops=1)

  • Join Filter: (adr."ID" = pom."ADRESS")
12. 0.000 774.609 ↓ 0.0 0 1

Nested Loop Left Join (cost=0.42..1,405,584.43 rows=55,449 width=422) (actual time=774.609..774.609 rows=0 loops=1)

  • Join Filter: (pom."ID" = rls."POMESHENIE")
13. 167.747 774.609 ↓ 0.0 0 1

Nested Loop Semi Join (cost=0.42..1,022,969.43 rows=55,449 width=422) (actual time=774.609..774.609 rows=0 loops=1)

  • Join Filter: (rls."ZONE" = r."ID")
  • Rows Removed by Join Filter: 708094
14. 275.599 606.862 ↓ 6.2 708,094 1

Nested Loop (cost=0.42..128,364.02 rows=115,107 width=438) (actual time=0.111..606.862 rows=708,094 loops=1)

  • Join Filter: (rls."TYPE_SCHET" = tc."ID")
  • Rows Removed by Join Filter: 745936
15. 331.263 331.263 ↓ 1.0 715,830 1

Index Scan using "REF_LIC_SCHET_pkey" on "REF_LIC_SCHET" rls (cost=0.42..117,887.71 rows=698,346 width=200) (actual time=0.073..331.263 rows=715,830 loops=1)

  • Filter: ("IS_ACTIVE" = 1)
  • Rows Removed by Filter: 218
16. 0.000 0.000 ↓ 2.0 2 715,830

Materialize (cost=0.00..1.12 rows=1 width=270) (actual time=0.000..0.000 rows=2 loops=715,830)

17. 0.028 0.028 ↓ 5.0 5 1

Seq Scan on "REF_TYPE_COUNTERS" tc (cost=0.00..1.11 rows=1 width=270) (actual time=0.026..0.028 rows=5 loops=1)

  • Filter: (("IS_ACTIVE" = 1) AND (("ICON_NAME")::text = ANY ('{w-hot,w-cold,gaz,colorimetr,electro}'::text[])))
  • Rows Removed by Filter: 1
18. 0.000 0.000 ↑ 511.0 1 708,094

CTE Scan on r (cost=0.00..10.22 rows=511 width=16) (actual time=0.000..0.000 rows=1 loops=708,094)

19. 0.000 0.000 ↓ 0.0 0

Materialize (cost=0.00..18.05 rows=460 width=32) (never executed)

20. 0.000 0.000 ↓ 0.0 0

Seq Scan on "REF_POMESCHENIE" pom (cost=0.00..15.75 rows=460 width=32) (never executed)

  • Filter: ("IS_ACTIVE" = 1)
21. 0.000 0.000 ↓ 0.0 0

Materialize (cost=0.00..17.05 rows=460 width=114) (never executed)

22. 0.000 0.000 ↓ 0.0 0

Seq Scan on "REG_ADDRESS" adr (cost=0.00..14.75 rows=460 width=114) (never executed)

  • Filter: ("IS_ACTIVE" = 1)
23. 0.000 0.000 ↓ 0.0 0

Materialize (cost=0.00..3.13 rows=75 width=62) (never executed)

24. 0.000 0.000 ↓ 0.0 0

Seq Scan on "REF_TYPE_POINT" tp (cost=0.00..2.75 rows=75 width=62) (never executed)

25.          

SubPlan (for Nested Loop Left Join)

26. 0.000 0.000 ↓ 0.0 0

Limit (cost=270.92..270.92 rows=1 width=36) (never executed)

27. 0.000 0.000 ↓ 0.0 0

Sort (cost=270.92..270.97 rows=23 width=36) (never executed)

  • Sort Key: rdm1."DATEON" DESC
28. 0.000 0.000 ↓ 0.0 0

Append (cost=4.17..270.80 rows=23 width=36) (never executed)

  • Subplans Removed: 4
29. 0.000 0.000 ↓ 0.0 0

Index Scan using "REG_DATA_MONTHS_2019_LIC_SCHET_idx" on "REG_DATA_MONTHS_2019" rdm1 (cost=0.43..74.38 rows=17 width=36) (never executed)

  • Index Cond: ("LIC_SCHET" = rls."ID")
  • Filter: ("DATEON" >= date_trunc('month'::text, (now() - '1 year'::interval)))
30. 0.000 0.000 ↓ 0.0 0

Seq Scan on "REG_DATA_MONTHS_2020" rdm1_1 (cost=0.00..1.43 rows=1 width=36) (never executed)

  • Filter: (("LIC_SCHET" = rls."ID") AND ("DATEON" >= date_trunc('month'::text, (now() - '1 year'::interval))))
31. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on "REG_DATA_MONTHS_2021" rdm1_2 (cost=4.17..11.32 rows=1 width=36) (never executed)

  • Recheck Cond: ("LIC_SCHET" = rls."ID")
  • Filter: ("DATEON" >= date_trunc('month'::text, (now() - '1 year'::interval)))
32. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on "REG_DATA_MONTHS_2021_LIC_SCHET_idx" (cost=0.00..4.17 rows=3 width=0) (never executed)

  • Index Cond: ("LIC_SCHET" = rls."ID")
33. 0.000 0.000 ↓ 0.0 0

Limit (cost=270.86..270.86 rows=1 width=36) (never executed)

34. 0.000 0.000 ↓ 0.0 0

Sort (cost=270.86..270.92 rows=23 width=36) (never executed)

  • Sort Key: rdm2."DATEON" DESC
35. 0.000 0.000 ↓ 0.0 0

Append (cost=4.17..270.74 rows=23 width=36) (never executed)

  • Subplans Removed: 4
36. 0.000 0.000 ↓ 0.0 0

Index Scan using "REG_DATA_MONTHS_2019_LIC_SCHET_idx" on "REG_DATA_MONTHS_2019" rdm2 (cost=0.43..74.33 rows=17 width=36) (never executed)

  • Index Cond: ("LIC_SCHET" = rls."ID")
  • Filter: ("DATEON" >= date_trunc('month'::text, (now() - '1 year'::interval)))
37. 0.000 0.000 ↓ 0.0 0

Seq Scan on "REG_DATA_MONTHS_2020" rdm2_1 (cost=0.00..1.43 rows=1 width=36) (never executed)

  • Filter: (("LIC_SCHET" = rls."ID") AND ("DATEON" >= date_trunc('month'::text, (now() - '1 year'::interval))))
38. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on "REG_DATA_MONTHS_2021" rdm2_2 (cost=4.17..11.31 rows=1 width=36) (never executed)

  • Recheck Cond: ("LIC_SCHET" = rls."ID")
  • Filter: ("DATEON" >= date_trunc('month'::text, (now() - '1 year'::interval)))
39. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on "REG_DATA_MONTHS_2021_LIC_SCHET_idx" (cost=0.00..4.17 rows=3 width=0) (never executed)

  • Index Cond: ("LIC_SCHET" = rls."ID")
Planning time : 2.067 ms
Execution time : 774.870 ms