explain.depesz.com

PostgreSQL's explain analyze made readable

Result: YMn

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 74,165.317 ↓ 0.0 0 1

Limit (cost=50.40..13,876.29 rows=25 width=596) (actual time=74,165.317..74,165.317 rows=0 loops=1)

2.          

CTE r

3. 0.003 0.098 ↑ 511.0 1 1

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

4. 0.037 0.037 ↑ 1.0 1 1

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

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

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

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

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

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

Hash (cost=0.20..0.20 rows=10 width=16) (actual time=0.005..0.005 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.001 74,165.317 ↓ 0.0 0 1

Unique (cost=0.42..156,761,800.87 rows=283,457 width=596) (actual time=74,165.317..74,165.317 rows=0 loops=1)

10. 333.840 74,165.316 ↓ 0.0 0 1

Nested Loop (cost=0.42..156,761,092.23 rows=283,457 width=596) (actual time=74,165.316..74,165.316 rows=0 loops=1)

  • Join Filter: (rls."ZONE" = r."ID")
  • Rows Removed by Join Filter: 708312
11. 5,163.007 73,831.476 ↓ 6.2 708,312 1

Nested Loop Left Join (cost=0.42..1,865,189.57 rows=115,153 width=548) (actual time=1.287..73,831.476 rows=708,312 loops=1)

  • Join Filter: ((tc."IS_ACTIVE" = 1) AND (tp."ID" = rls."TYPE_POINT"))
  • Rows Removed by Join Filter: 53123339
12. 23,589.206 67,251.845 ↓ 6.2 708,312 1

Nested Loop Left Join (cost=0.42..1,714,048.32 rows=115,153 width=520) (actual time=1.208..67,251.845 rows=708,312 loops=1)

  • Join Filter: (adr."ID" = pom."ADRESS")
  • Rows Removed by Join Filter: 325823520
13. 21,843.997 33,037.959 ↓ 6.2 708,312 1

Nested Loop Left Join (cost=0.42..921,194.24 rows=115,153 width=438) (actual time=0.678..33,037.959 rows=708,312 loops=1)

  • Join Filter: (pom."ID" = rls."POMESHENIE")
  • Rows Removed by Join Filter: 325823520
14. 467.522 1,277.594 ↓ 6.2 708,312 1

Nested Loop (cost=0.42..126,621.64 rows=115,153 width=438) (actual time=0.195..1,277.594 rows=708,312 loops=1)

  • Join Filter: (rls."TYPE_SCHET" = tc."ID")
  • Rows Removed by Join Filter: 745936
15. 810.072 810.072 ↓ 1.0 716,048 1

Index Scan using "REF_LIC_SCHET_pkey" on "REF_LIC_SCHET" rls (cost=0.42..116,141.15 rows=698,625 width=200) (actual time=0.125..810.072 rows=716,048 loops=1)

16. 0.000 0.000 ↓ 2.0 2 716,048

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

17. 0.053 0.053 ↓ 5.0 5 1

Seq Scan on "REF_TYPE_COUNTERS" tc (cost=0.00..1.11 rows=1 width=270) (actual time=0.044..0.053 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. 9,916.035 9,916.368 ↑ 1.0 460 708,312

Materialize (cost=0.00..18.05 rows=460 width=32) (actual time=0.000..0.014 rows=460 loops=708,312)

19. 0.333 0.333 ↑ 1.0 460 1

Seq Scan on "REF_POMESCHENIE" pom (cost=0.00..15.75 rows=460 width=32) (actual time=0.031..0.333 rows=460 loops=1)

  • Filter: ("IS_ACTIVE" = 1)
20. 10,624.371 10,624.680 ↑ 1.0 460 708,312

Materialize (cost=0.00..17.05 rows=460 width=114) (actual time=0.000..0.015 rows=460 loops=708,312)

21. 0.309 0.309 ↑ 1.0 460 1

Seq Scan on "REG_ADDRESS" adr (cost=0.00..14.75 rows=460 width=114) (actual time=0.049..0.309 rows=460 loops=1)

  • Filter: ("IS_ACTIVE" = 1)
22. 1,416.579 1,416.624 ↑ 1.0 75 708,312

Materialize (cost=0.00..3.13 rows=75 width=62) (actual time=0.000..0.002 rows=75 loops=708,312)

23. 0.045 0.045 ↑ 1.0 75 1

Seq Scan on "REF_TYPE_POINT" tp (cost=0.00..2.75 rows=75 width=62) (actual time=0.025..0.045 rows=75 loops=1)

24. 0.000 0.000 ↑ 511.0 1 708,312

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

25.          

SubPlan (for Nested Loop)

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 : 5.348 ms
Execution time : 74,166.015 ms