explain.depesz.com

PostgreSQL's explain analyze made readable

Result: t1Q4

Settings
# exclusive inclusive rows x rows loops node
1. 3.942 125.658 ↑ 1.0 1,780 1

Unique (cost=135,458.82..135,641.27 rows=1,780 width=752) (actual time=120.479..125.658 rows=1,780 loops=1)

  • Buffers: shared hit=992
2.          

CTE p

3. 12.790 67.310 ↑ 1.0 1,780 1

GroupAggregate (cost=134,534.34..134,690.03 rows=1,780 width=265) (actual time=42.576..67.310 rows=1,780 loops=1)

  • Group Key: k_2.nzp_kvar
  • Buffers: shared hit=922
4.          

CTE period

5. 3.658 13.473 ↑ 1.0 1,780 1

HashAggregate (cost=1,044.72..1,062.52 rows=1,780 width=8) (actual time=11.911..13.473 rows=1,780 loops=1)

  • Group Key: k_1.nzp_kvar
  • Buffers: shared hit=319
6. 3.476 9.815 ↓ 1.0 1,781 1

Hash Right Join (cost=133.99..1,035.82 rows=1,780 width=8) (actual time=2.640..9.815 rows=1,781 loops=1)

  • Hash Cond: (prm1.nzp = k_1.nzp_kvar)
  • Buffers: shared hit=319
7. 3.768 4.141 ↓ 1.6 1,744 1

Bitmap Heap Scan on prm_1 prm1 (cost=60.94..947.96 rows=1,077 width=8) (actual time=0.427..4.141 rows=1,744 loops=1)

  • Recheck Cond: (nzp_prm = 4)
  • Filter: ((dat_s <= '2019-07-31'::date) AND (dat_po >= '2019-07-01'::date) AND (is_actual = 1))
  • Rows Removed by Filter: 1,534
  • Heap Blocks: exact=275
  • Buffers: shared hit=286
8. 0.373 0.373 ↓ 1.0 3,286 1

Bitmap Index Scan on p1n_ix3 (cost=0.00..60.67 rows=3,251 width=0) (actual time=0.372..0.373 rows=3,286 loops=1)

  • Index Cond: (nzp_prm = 4)
  • Buffers: shared hit=11
9. 1.121 2.198 ↑ 1.0 1,780 1

Hash (cost=50.80..50.80 rows=1,780 width=4) (actual time=2.198..2.198 rows=1,780 loops=1)

  • Buckets: 2,048 Batches: 1 Memory Usage: 79kB
  • Buffers: shared hit=33
10. 1.077 1.077 ↑ 1.0 1,780 1

Seq Scan on kvar k_1 (cost=0.00..50.80 rows=1,780 width=4) (actual time=0.008..1.077 rows=1,780 loops=1)

  • Buffers: shared hit=33
11. 7.592 54.520 ↓ 2.9 5,204 1

Merge Left Join (cost=133,471.82..133,502.91 rows=1,780 width=115) (actual time=42.550..54.520 rows=5,204 loops=1)

  • Merge Cond: (k_2.nzp_kvar = prm1_1.nzp)
  • Buffers: shared hit=922
12. 3.031 15.879 ↑ 1.0 1,780 1

Sort (cost=674.37..678.82 rows=1,780 width=106) (actual time=14.621..15.879 rows=1,780 loops=1)

  • Sort Key: k_2.nzp_kvar
  • Sort Method: quicksort Memory: 185kB
  • Buffers: shared hit=227
13. 2.362 12.848 ↑ 1.0 1,780 1

Hash Right Join (cost=375.77..578.27 rows=1,780 width=106) (actual time=8.647..12.848 rows=1,780 loops=1)

  • Hash Cond: (prm3.nzp = k_2.nzp_kvar)
  • Buffers: shared hit=227
14. 1.872 2.025 ↓ 1.7 1,778 1

Bitmap Heap Scan on prm_3 prm3 (cost=39.28..227.58 rows=1,033 width=30) (actual time=0.177..2.025 rows=1,778 loops=1)

  • Recheck Cond: (nzp_prm = 51)
  • Filter: ((dat_s <= '2019-07-11'::date) AND (dat_po >= '2019-07-11'::date) AND (is_actual = 1))
  • Rows Removed by Filter: 187
  • Heap Blocks: exact=117
  • Buffers: shared hit=125
15. 0.153 0.153 ↑ 1.0 1,965 1

Bitmap Index Scan on p3n_ix3 (cost=0.00..39.02 rows=1,965 width=0) (actual time=0.152..0.153 rows=1,965 loops=1)

  • Index Cond: (nzp_prm = 51)
  • Buffers: shared hit=8
16. 1.315 8.461 ↑ 1.0 1,780 1

Hash (cost=314.23..314.23 rows=1,780 width=80) (actual time=8.461..8.461 rows=1,780 loops=1)

  • Buckets: 2,048 Batches: 1 Memory Usage: 96kB
  • Buffers: shared hit=102
17. 1.172 7.146 ↑ 1.0 1,780 1

Hash Right Join (cost=230.12..314.23 rows=1,780 width=80) (actual time=5.835..7.146 rows=1,780 loops=1)

  • Hash Cond: (prm2.nzp = k_2.nzp_dom)
  • Buffers: shared hit=102
18. 0.159 0.178 ↓ 1.5 89 1

Bitmap Heap Scan on prm_2 prm2 (cost=5.22..79.42 rows=61 width=13) (actual time=0.032..0.178 rows=89 loops=1)

  • Recheck Cond: (nzp_prm = 1,671)
  • Filter: ((dat_s <= '2019-07-01'::date) AND (dat_po >= '2019-07-01'::date) AND (is_actual = 1))
  • Rows Removed by Filter: 34
  • Heap Blocks: exact=28
  • Buffers: shared hit=30
19. 0.019 0.019 ↑ 1.0 123 1

Bitmap Index Scan on p2n_ix3 (cost=0.00..5.21 rows=123 width=0) (actual time=0.019..0.019 rows=123 loops=1)

  • Index Cond: (nzp_prm = 1,671)
  • Buffers: shared hit=2
20. 1.250 5.796 ↑ 1.0 1,780 1

Hash (cost=202.65..202.65 rows=1,780 width=75) (actual time=5.795..5.796 rows=1,780 loops=1)

  • Buckets: 2,048 Batches: 1 Memory Usage: 99kB
  • Buffers: shared hit=72
21. 1.335 4.546 ↑ 1.0 1,780 1

Hash Right Join (cost=161.44..202.65 rows=1,780 width=75) (actual time=2.996..4.546 rows=1,780 loops=1)

  • Hash Cond: (prm2030.nzp = k_2.nzp_dom)
  • Buffers: shared hit=72
22. 0.213 0.884 ↓ 1.9 272 1

Hash Right Join (cost=88.39..106.19 rows=144 width=71) (actual time=0.661..0.884 rows=272 loops=1)

  • Hash Cond: (r.nzp_y = (prm2030.val_prm)::integer)
  • Buffers: shared hit=39
23. 0.023 0.039 ↑ 2.0 3 1

Bitmap Heap Scan on res_y r (cost=4.33..21.75 rows=6 width=71) (actual time=0.020..0.039 rows=3 loops=1)

  • Recheck Cond: (nzp_res = 3,019)
  • Heap Blocks: exact=3
  • Buffers: shared hit=5
24. 0.016 0.016 ↑ 2.0 3 1

Bitmap Index Scan on ry_idx_nzp_res (cost=0.00..4.33 rows=6 width=0) (actual time=0.015..0.016 rows=3 loops=1)

  • Index Cond: (nzp_res = 3,019)
  • Buffers: shared hit=2
25. 0.282 0.632 ↓ 1.9 272 1

Hash (cost=82.27..82.27 rows=144 width=9) (actual time=0.632..0.632 rows=272 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 19kB
  • Buffers: shared hit=34
26. 0.314 0.350 ↓ 1.9 272 1

Bitmap Heap Scan on prm_2 prm2030 (cost=6.49..82.27 rows=144 width=9) (actual time=0.048..0.350 rows=272 loops=1)

  • Recheck Cond: (nzp_prm = 2,030)
  • Filter: ((dat_s <= '2019-07-01'::date) AND (dat_po >= '2019-07-01'::date) AND (is_actual = 1))
  • Rows Removed by Filter: 17
  • Heap Blocks: exact=31
  • Buffers: shared hit=34
27. 0.036 0.036 ↑ 1.0 289 1

Bitmap Index Scan on p2n_ix3 (cost=0.00..6.45 rows=289 width=0) (actual time=0.035..0.036 rows=289 loops=1)

  • Index Cond: (nzp_prm = 2,030)
  • Buffers: shared hit=3
28. 1.216 2.327 ↑ 1.0 1,780 1

Hash (cost=50.80..50.80 rows=1,780 width=8) (actual time=2.327..2.327 rows=1,780 loops=1)

  • Buckets: 2,048 Batches: 1 Memory Usage: 86kB
  • Buffers: shared hit=33
29. 1.111 1.111 ↑ 1.0 1,780 1

Seq Scan on kvar k_2 (cost=0.00..50.80 rows=1,780 width=8) (actual time=0.006..1.111 rows=1,780 loops=1)

  • Buffers: shared hit=33
30. 7.310 31.049 ↓ 3.5 5,198 1

Sort (cost=132,797.45..132,801.15 rows=1,479 width=13) (actual time=27.922..31.049 rows=5,198 loops=1)

  • Sort Key: prm1_1.nzp
  • Sort Method: quicksort Memory: 436kB
  • Buffers: shared hit=695
31. 6.678 23.739 ↓ 3.5 5,198 1

Bitmap Heap Scan on prm_1 prm1_1 (cost=181.54..132,719.58 rows=1,479 width=13) (actual time=19.044..23.739 rows=5,198 loops=1)

  • Recheck Cond: ((nzp_prm = ANY ('{4,6,3,110,5,2005,2004}'::integer[])) AND (dat_s <= '2019-07-31'::date) AND (dat_po >= '2019-07-01'::date) AND (is_actual = 1))
  • Filter: CASE WHEN (nzp_prm = 4) THEN (alternatives: SubPlan 2 or hashed SubPlan 3) ELSE true END
  • Rows Removed by Filter: 1
  • Heap Blocks: exact=340
  • Buffers: shared hit=695
32. 0.574 0.574 ↓ 1.8 5,206 1

Bitmap Index Scan on test_index1 (cost=0.00..181.17 rows=2,958 width=0) (actual time=0.573..0.574 rows=5,206 loops=1)

  • Index Cond: ((nzp_prm = ANY ('{4,6,3,110,5,2005,2004}'::integer[])) AND (dat_s <= '2019-07-31'::date) AND (dat_po >= '2019-07-01'::date))
  • Buffers: shared hit=36
33.          

SubPlan (for Bitmap Heap Scan)

34. 0.000 0.000 ↓ 0.0 0

CTE Scan on period pp (cost=0.00..44.50 rows=1 width=0) (never executed)

  • Filter: ((nzp_kvar = prm1_1.nzp) AND (dat_s = prm1_1.dat_s))
35. 16.487 16.487 ↑ 1.0 1,780 1

CTE Scan on period pp_1 (cost=0.00..35.60 rows=1,780 width=8) (actual time=11.916..16.487 rows=1,780 loops=1)

  • Buffers: shared hit=319
36. 9.688 121.716 ↓ 1.1 1,977 1

Sort (cost=768.79..773.24 rows=1,780 width=752) (actual time=120.476..121.716 rows=1,977 loops=1)

  • Sort Key: k.nzp_kvar, k.num_ls, k.fio, a.area, k.nzp_dom, (btrim((t.town)::text)), (CASE WHEN (raj.type_adr = 1) THEN (CASE WHEN ((raj.rajon IS NOT NULL) AND (btrim((raj.rajon)::text) <> '-'::text)) THEN raj.rajon ELSE ''::character varying END)::t (...)
  • Sort Method: quicksort Memory: 1,110kB
  • Buffers: shared hit=992
37. 13.034 112.028 ↓ 1.1 1,977 1

Hash Left Join (cost=315.30..672.69 rows=1,780 width=752) (actual time=81.315..112.028 rows=1,977 loops=1)

  • Hash Cond: (k.typek = typek.nzp_y)
  • Buffers: shared hit=992
38. 2.642 98.970 ↓ 1.1 1,977 1

Hash Left Join (cost=295.97..457.31 rows=1,780 width=867) (actual time=81.235..98.970 rows=1,977 loops=1)

  • Hash Cond: (k.nzp_dom = prm_complex.nzp)
  • Buffers: shared hit=989
39. 2.903 96.317 ↓ 1.1 1,977 1

Hash Left Join (cost=258.18..401.28 rows=1,780 width=862) (actual time=81.207..96.317 rows=1,977 loops=1)

  • Hash Cond: (ul.nzp_raj = raj.nzp_raj)
  • Buffers: shared hit=987
40. 2.834 93.339 ↓ 1.1 1,977 1

Hash Left Join (cost=255.81..376.41 rows=1,780 width=506) (actual time=81.122..93.339 rows=1,977 loops=1)

  • Hash Cond: (k.nzp_area = a.nzp_area)
  • Buffers: shared hit=985
41. 2.667 89.929 ↓ 1.1 1,977 1

Hash Left Join (cost=241.54..337.67 rows=1,780 width=464) (actual time=80.443..89.929 rows=1,977 loops=1)

  • Hash Cond: (k.nzp_kvar = counters.nzp)
  • Buffers: shared hit=980
42. 2.637 86.028 ↑ 1.0 1,780 1

Hash Join (cost=202.25..274.52 rows=1,780 width=460) (actual time=79.200..86.028 rows=1,780 loops=1)

  • Hash Cond: (k.nzp_dom = d.nzp_dom)
  • Buffers: shared hit=966
43. 2.906 82.460 ↑ 1.0 1,780 1

Hash Join (cost=185.12..232.90 rows=1,780 width=417) (actual time=78.258..82.460 rows=1,780 loops=1)

  • Hash Cond: (p."Id" = k.nzp_kvar)
  • Buffers: shared hit=961
44. 1.331 76.440 ↑ 1.0 1,780 1

Hash Right Join (cost=112.07..135.38 rows=1,780 width=335) (actual time=75.131..76.440 rows=1,780 loops=1)

  • Hash Cond: (prm1ownershipresy.nzp_y = CASE WHEN (p."OwnershipId" <> ''::text) THEN (p."OwnershipId")::integer ELSE NULL::integer END)
  • Buffers: shared hit=928
45. 0.011 0.019 ↓ 1.2 7 1

Bitmap Heap Scan on res_y prm1ownershipresy (cost=4.33..21.75 rows=6 width=71) (actual time=0.014..0.019 rows=7 loops=1)

  • Recheck Cond: (nzp_res = 22)
  • Heap Blocks: exact=1
  • Buffers: shared hit=3
46. 0.008 0.008 ↓ 1.2 7 1

Bitmap Index Scan on ry_idx_nzp_res (cost=0.00..4.33 rows=6 width=0) (actual time=0.008..0.008 rows=7 loops=1)

  • Index Cond: (nzp_res = 22)
  • Buffers: shared hit=2
47. 1.501 75.090 ↑ 1.0 1,780 1

Hash (cost=85.49..85.49 rows=1,780 width=300) (actual time=75.089..75.090 rows=1,780 loops=1)

  • Buckets: 2,048 Batches: 1 Memory Usage: 174kB
  • Buffers: shared hit=925
48. 1.530 73.589 ↑ 1.0 1,780 1

Hash Right Join (cost=62.18..85.49 rows=1,780 width=300) (actual time=72.068..73.589 rows=1,780 loops=1)

  • Hash Cond: (comfresy.nzp_y = CASE WHEN (p."ComfortableId" <> ''::text) THEN (p."ComfortableId")::integer ELSE NULL::integer END)
  • Buffers: shared hit=925
49. 0.007 0.019 ↑ 3.0 2 1

Bitmap Heap Scan on res_y comfresy (cost=4.33..21.75 rows=6 width=71) (actual time=0.016..0.019 rows=2 loops=1)

  • Recheck Cond: (nzp_res = 1)
  • Heap Blocks: exact=1
  • Buffers: shared hit=3
50. 0.012 0.012 ↑ 3.0 2 1

Bitmap Index Scan on ry_idx_nzp_res (cost=0.00..4.33 rows=6 width=0) (actual time=0.012..0.012 rows=2 loops=1)

  • Index Cond: (nzp_res = 1)
  • Buffers: shared hit=2
51. 1.789 72.040 ↑ 1.0 1,780 1

Hash (cost=35.60..35.60 rows=1,780 width=265) (actual time=72.039..72.040 rows=1,780 loops=1)

  • Buckets: 2,048 Batches: 1 Memory Usage: 137kB
  • Buffers: shared hit=922
52. 70.251 70.251 ↑ 1.0 1,780 1

CTE Scan on p (cost=0.00..35.60 rows=1,780 width=265) (actual time=42.581..70.251 rows=1,780 loops=1)

  • Buffers: shared hit=922
53. 1.653 3.114 ↑ 1.0 1,780 1

Hash (cost=50.80..50.80 rows=1,780 width=86) (actual time=3.114..3.114 rows=1,780 loops=1)

  • Buckets: 2,048 Batches: 1 Memory Usage: 221kB
  • Buffers: shared hit=33
54. 1.461 1.461 ↑ 1.0 1,780 1

Seq Scan on kvar k (cost=0.00..50.80 rows=1,780 width=86) (actual time=0.009..1.461 rows=1,780 loops=1)

  • Buffers: shared hit=33
55. 0.237 0.931 ↑ 1.0 292 1

Hash (cost=13.49..13.49 rows=292 width=47) (actual time=0.930..0.931 rows=292 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 32kB
  • Buffers: shared hit=5
56. 0.404 0.694 ↑ 1.0 292 1

Hash Left Join (cost=2.55..13.49 rows=292 width=47) (actual time=0.129..0.694 rows=292 loops=1)

  • Hash Cond: (d.nzp_ul = ul.nzp_ul)
  • Buffers: shared hit=5
57. 0.185 0.185 ↑ 1.0 292 1

Seq Scan on dom d (cost=0.00..6.92 rows=292 width=20) (actual time=0.012..0.185 rows=292 loops=1)

  • Buffers: shared hit=4
58. 0.055 0.105 ↑ 1.0 69 1

Hash (cost=1.69..1.69 rows=69 width=31) (actual time=0.105..0.105 rows=69 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 13kB
  • Buffers: shared hit=1
59. 0.050 0.050 ↑ 1.0 69 1

Seq Scan on s_ulica ul (cost=0.00..1.69 rows=69 width=31) (actual time=0.005..0.050 rows=69 loops=1)

  • Buffers: shared hit=1
60. 0.536 1.234 ↑ 1.0 823 1

Hash (cost=28.92..28.92 rows=829 width=4) (actual time=1.233..1.234 rows=823 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 37kB
  • Buffers: shared hit=14
61. 0.698 0.698 ↑ 1.0 823 1

Seq Scan on counters_spis counters (cost=0.00..28.92 rows=829 width=4) (actual time=0.013..0.698 rows=823 loops=1)

  • Filter: ((dat_close IS NULL) AND (nzp_type = 3) AND (is_actual = 1))
  • Rows Removed by Filter: 172
  • Buffers: shared hit=14
62. 0.306 0.576 ↑ 1.0 412 1

Hash (cost=9.12..9.12 rows=412 width=46) (actual time=0.576..0.576 rows=412 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 41kB
  • Buffers: shared hit=5
63. 0.270 0.270 ↑ 1.0 412 1

Seq Scan on s_area a (cost=0.00..9.12 rows=412 width=46) (actual time=0.011..0.270 rows=412 loops=1)

  • Buffers: shared hit=5
64. 0.012 0.075 ↑ 1.0 8 1

Hash (cost=2.27..2.27 rows=8 width=360) (actual time=0.074..0.075 rows=8 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=2
65. 0.038 0.063 ↑ 1.0 8 1

Hash Left Join (cost=1.11..2.27 rows=8 width=360) (actual time=0.048..0.063 rows=8 loops=1)

  • Hash Cond: (raj.nzp_town = t.nzp_town)
  • Buffers: shared hit=2
66. 0.009 0.009 ↑ 1.0 8 1

Seq Scan on s_rajon raj (cost=0.00..1.08 rows=8 width=246) (actual time=0.005..0.009 rows=8 loops=1)

  • Buffers: shared hit=1
67. 0.008 0.016 ↑ 1.0 5 1

Hash (cost=1.05..1.05 rows=5 width=126) (actual time=0.015..0.016 rows=5 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=1
68. 0.008 0.008 ↑ 1.0 5 1

Seq Scan on s_town t (cost=0.00..1.05 rows=5 width=126) (actual time=0.004..0.008 rows=5 loops=1)

  • Buffers: shared hit=1
69. 0.001 0.011 ↓ 0.0 0 1

Hash (cost=37.71..37.71 rows=6 width=9) (actual time=0.011..0.011 rows=0 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 8kB
  • Buffers: shared hit=2
70. 0.002 0.010 ↓ 0.0 0 1

Bitmap Heap Scan on prm_2 prm_complex (cost=4.37..37.71 rows=6 width=9) (actual time=0.010..0.010 rows=0 loops=1)

  • Recheck Cond: (nzp_prm = 499)
  • Filter: (('2019-07-11'::date >= dat_s) AND ('2019-07-11'::date <= dat_po) AND (is_actual = 1))
  • Buffers: shared hit=2
71. 0.008 0.008 ↓ 0.0 0 1

Bitmap Index Scan on p2n_ix3 (cost=0.00..4.37 rows=12 width=0) (actual time=0.008..0.008 rows=0 loops=1)

  • Index Cond: (nzp_prm = 499)
  • Buffers: shared hit=2
72. 0.006 0.024 ↑ 1.7 3 1

Hash (cost=19.27..19.27 rows=5 width=68) (actual time=0.023..0.024 rows=3 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=3
73. 0.008 0.018 ↑ 1.7 3 1

Bitmap Heap Scan on res_y typek (cost=4.32..19.27 rows=5 width=68) (actual time=0.014..0.018 rows=3 loops=1)

  • Recheck Cond: (nzp_res = 9,999)
  • Heap Blocks: exact=1
  • Buffers: shared hit=3
74. 0.010 0.010 ↑ 1.7 3 1

Bitmap Index Scan on ry_idx_nzp_res (cost=0.00..4.32 rows=5 width=0) (actual time=0.009..0.010 rows=3 loops=1)

  • Index Cond: (nzp_res = 9,999)
  • Buffers: shared hit=2
Planning time : 9.789 ms
Execution time : 127.394 ms