explain.depesz.com

PostgreSQL's explain analyze made readable

Result: fWeM

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 38.356 217,505.458 ↑ 1.0 10 1

Hash Right Join (cost=2,501,202.71..2,501,208.22 rows=10 width=174) (actual time=195,706.235..217,505.458 rows=10 loops=1)

  • Hash Cond: (((zu."ID_TIPI")::text = za."ID_TIPI") AND (zu.rok = za.rok))
2.          

CTE zasiedlenie

3. 0.035 0.035 ↑ 1.0 10 1

Values Scan on "*VALUES*" (cost=0.00..0.13 rows=10 width=40) (actual time=0.009..0.035 rows=10 loops=1)

4.          

CTE zuzycie

5. 20,252.038 217,369.148 ↓ 195.5 39,104 1

GroupAggregate (cost=2,501,181.42..2,501,202.23 rows=200 width=204) (actual time=195,672.258..217,369.148 rows=39,104 loops=1)

  • Group Key: v_memorial_v2_ostatnie_4_lata."ID_TIPI", (date_part('year'::text, (v_memorial_v2_ostatnie_4_lata.miesiac)::timestamp without time zone))
6. 9,057.358 197,117.110 ↓ 4,156.7 1,766,584 1

Sort (cost=2,501,181.42..2,501,182.48 rows=425 width=714) (actual time=195,671.861..197,117.110 rows=1,766,584 loops=1)

  • Sort Key: v_memorial_v2_ostatnie_4_lata."ID_TIPI", (date_part('year'::text, (v_memorial_v2_ostatnie_4_lata.miesiac)::timestamp without time zone))
  • Sort Method: quicksort Memory: 304667kB
7. 3,066.857 188,059.752 ↓ 4,156.7 1,766,584 1

Subquery Scan on v_memorial_v2_ostatnie_4_lata (cost=2,499,244.78..2,501,162.86 rows=425 width=714) (actual time=151,509.667..188,059.752 rows=1,766,584 loops=1)

8. 184,992.895 184,992.895 ↓ 4,156.7 1,766,584 1

CTE Scan on wynik (cost=2,499,244.78..2,501,156.49 rows=425 width=10,496) (actual time=151,509.658..184,992.895 rows=1,766,584 loops=1)

  • Filter: ((rodzaj_migawki)::text = 'DYSTRYBUCJA'::text)
  • Rows Removed by Filter: 1534682
9.          

CTE mapowanie_mpk

10. 0.011 0.011 ↑ 1.0 2 1

Values Scan on "*VALUES*_1" (cost=0.00..0.03 rows=2 width=42) (actual time=0.008..0.011 rows=2 loops=1)

11.          

CTE om

12. 0.004 0.019 ↑ 1.0 1 1

Limit (cost=0.43..0.46 rows=1 width=4) (actual time=0.016..0.019 rows=1 loops=1)

13. 0.015 0.015 ↑ 1,593,819.0 1 1

Index Only Scan Backward using t_memorial_v2_data_migawki_idx on t_memorial_v2 (cost=0.43..53,817.25 rows=1,593,819 width=4) (actual time=0.014..0.015 rows=1 loops=1)

  • Heap Fetches: 1
14.          

CTE om1

15. 0.014 691.477 ↑ 1.0 1 1

Limit (cost=0.43..0.64 rows=1 width=12) (actual time=691.469..691.477 rows=1 loops=1)

16. 176.842 691.463 ↑ 531,273.0 1 1

Nested Loop (cost=0.43..113,585.48 rows=531,273 width=12) (actual time=691.462..691.463 rows=1 loops=1)

  • Join Filter: (d.data_migawki < (date_trunc('year'::text, (om.data_migawki)::timestamp with time zone) - '1 day'::interval))
  • Rows Removed by Join Filter: 67482
17. 379.655 379.655 ↑ 23.6 67,483 1

Index Only Scan Backward using t_memorial_v2_data_migawki_idx on t_memorial_v2 d (cost=0.43..53,817.25 rows=1,593,819 width=4) (actual time=0.580..379.655 rows=67,483 loops=1)

  • Heap Fetches: 67483
18. 134.966 134.966 ↑ 1.0 1 67,483

CTE Scan on om (cost=0.00..0.02 rows=1 width=4) (actual time=0.001..0.002 rows=1 loops=67,483)

19.          

CTE memorial_v2

20. 259.929 1,134.543 ↓ 1.1 134,518 1

Nested Loop (cost=838.34..69,885.63 rows=124,955 width=27) (actual time=28.110..1,134.543 rows=134,518 loops=1)

21. 0.006 0.021 ↑ 1.0 1 1

Nested Loop (cost=0.00..0.05 rows=1 width=8) (actual time=0.007..0.021 rows=1 loops=1)

22. 0.003 0.003 ↑ 1.0 1 1

CTE Scan on om om_1 (cost=0.00..0.02 rows=1 width=4) (actual time=0.001..0.003 rows=1 loops=1)

23. 0.012 0.012 ↑ 1.0 1 1

CTE Scan on om1 (cost=0.00..0.02 rows=1 width=4) (actual time=0.001..0.012 rows=1 loops=1)

24. 862.315 874.593 ↓ 1.1 134,518 1

Bitmap Heap Scan on t_memorial_v2 d_1 (cost=838.34..68,636.03 rows=124,955 width=27) (actual time=28.084..874.593 rows=134,518 loops=1)

  • Recheck Cond: ((data_migawki = om_1.data_migawki) OR (data_migawki = om1.data_migawki))
  • Rows Removed by Index Recheck: 133791
  • Heap Blocks: lossy=11699
25. 0.018 12.278 ↓ 0.0 0 1

BitmapOr (cost=838.34..838.34 rows=138,846 width=0) (actual time=12.277..12.278 rows=0 loops=1)

26. 11.879 11.879 ↓ 1.3 93,440 1

Bitmap Index Scan on t_memorial_v2_rodzaj_migawki_data_migawki_idx (cost=0.00..20.72 rows=69,423 width=0) (actual time=11.879..11.879 rows=93,440 loops=1)

  • Index Cond: (data_migawki = om_1.data_migawki)
27. 0.381 0.381 ↓ 1.7 117,760 1

Bitmap Index Scan on t_memorial_v2_rodzaj_migawki_data_migawki_idx (cost=0.00..20.72 rows=69,423 width=0) (actual time=0.381..0.381 rows=117,760 loops=1)

  • Index Cond: (data_migawki = om1.data_migawki)
28.          

CTE wynik

29. 13,310.795 168,705.833 ↓ 38.9 3,301,266 1

WindowAgg (cost=2,422,135.99..2,429,358.02 rows=84,965 width=1,089) (actual time=151,509.643..168,705.833 rows=3,301,266 loops=1)

30. 19,599.620 155,395.038 ↓ 38.9 3,301,266 1

Sort (cost=2,422,135.99..2,422,348.40 rows=84,965 width=553) (actual time=150,811.957..155,395.038 rows=3,301,266 loops=1)

  • Sort Key: (date_part('year'::text, (dane.miesiac)::timestamp without time zone)), (date_part('quarter'::text, (dane.miesiac)::timestamp without time zone))
  • Sort Method: external merge Disk: 1416184kB
31. 12,657.879 135,795.418 ↓ 38.9 3,301,266 1

WindowAgg (cost=2,412,418.30..2,415,179.66 rows=84,965 width=553) (actual time=118,561.904..135,795.418 rows=3,301,266 loops=1)

32. 23,596.521 123,137.539 ↓ 38.9 3,301,266 1

Sort (cost=2,412,418.30..2,412,630.71 rows=84,965 width=521) (actual time=118,553.593..123,137.539 rows=3,301,266 loops=1)

  • Sort Key: m."IDLICZNIK", dane.miesiac
  • Sort Method: external merge Disk: 1395360kB
33. 7,679.530 99,541.018 ↓ 38.9 3,301,266 1

Hash Left Join (cost=2,390,570.84..2,405,461.97 rows=84,965 width=521) (actual time=74,227.644..99,541.018 rows=3,301,266 loops=1)

  • Hash Cond: (m."IDPPE" = (mapowanie_mpk."IDPPE")::numeric)
34. 11,863.442 91,861.441 ↓ 38.9 3,301,266 1

Merge Join (cost=2,390,570.77..2,404,187.34 rows=84,965 width=467) (actual time=74,189.669..91,861.441 rows=3,301,266 loops=1)

  • Merge Cond: (((CASE WHEN ((d_2.rodzaj_migawki)::text = 'DYSTRYBUCJA'::text) THEN (d_2.id)::numeric ELSE d_2.dystrybucja_id END) = ((m.id)::numeric)) AND (d_2.data_migawki = m.data_migawki))
35. 9,809.612 63,895.864 ↓ 822.8 3,301,266 1

Sort (cost=2,223,973.81..2,223,983.84 rows=4,012 width=136) (actual time=61,280.330..63,895.864 rows=3,301,266 loops=1)

  • Sort Key: (CASE WHEN ((d_2.rodzaj_migawki)::text = 'DYSTRYBUCJA'::text) THEN (d_2.id)::numeric ELSE d_2.dystrybucja_id END), d_2.data_migawki
  • Sort Method: quicksort Memory: 499945kB
36. 7,152.120 54,086.252 ↓ 822.8 3,301,266 1

Nested Loop (cost=0.56..2,223,733.69 rows=4,012 width=136) (actual time=755.254..54,086.252 rows=3,301,266 loops=1)

  • Join Filter: CASE WHEN (d_2.data_migawki <> om1_1.data_migawki) THEN true ELSE ((dane.miesiac >= om1_1.termin_od) AND (dane.miesiac <= om1_1.termin_do)) END
  • Rows Removed by Join Filter: 2413296
37. 691.477 691.477 ↑ 1.0 1 1

CTE Scan on om1 om1_1 (cost=0.00..0.02 rows=1 width=12) (actual time=691.475..691.477 rows=1 loops=1)

38. 8,872.302 46,242.655 ↓ 712.2 5,714,562 1

Nested Loop (cost=0.56..2,223,593.25 rows=8,024 width=136) (actual time=62.819..46,242.655 rows=5,714,562 loops=1)

39. 1,454.047 1,454.047 ↓ 1.1 134,518 1

CTE Scan on memorial_v2 d_2 (cost=0.00..2,499.10 rows=124,955 width=102) (actual time=28.133..1,454.047 rows=134,518 loops=1)

40. 35,916.306 35,916.306 ↑ 1.2 42 134,518

Index Scan using t_memorial_v2_dane_f_kay_ix1 on t_memorial_v2_dane dane (cost=0.56..17.27 rows=51 width=51) (actual time=0.176..0.267 rows=42 loops=134,518)

  • Index Cond: ((data_migawki = d_2.data_migawki) AND ((rodzaj_migawki)::text = (d_2.rodzaj_migawki)::text) AND (memorial_v2_id = d_2.id))
41. 11,295.325 16,102.135 ↓ 4.8 4,081,852 1

Sort (cost=166,596.96..168,714.75 rows=847,115 width=347) (actual time=12,909.254..16,102.135 rows=4,081,852 loops=1)

  • Sort Key: ((m.id)::numeric), m.data_migawki
  • Sort Method: quicksort Memory: 463768kB
42. 4,799.977 4,806.810 ↓ 1.0 852,641 1

Bitmap Heap Scan on t_memorial_v2 m (cost=246.00..83,189.18 rows=847,115 width=347) (actual time=15.514..4,806.810 rows=852,641 loops=1)

  • Recheck Cond: ((rodzaj_migawki)::text = 'DYSTRYBUCJA'::text)
  • Rows Removed by Index Recheck: 162614
  • Heap Blocks: lossy=48179
43. 6.833 6.833 ↑ 2.9 482,560 1

Bitmap Index Scan on t_memorial_v2_rodzaj_migawki_data_migawki_idx (cost=0.00..34.22 rows=1,378,254 width=0) (actual time=6.832..6.833 rows=482,560 loops=1)

  • Index Cond: ((rodzaj_migawki)::text = 'DYSTRYBUCJA'::text)
44. 0.023 0.047 ↑ 1.0 2 1

Hash (cost=0.04..0.04 rows=2 width=42) (actual time=0.047..0.047 rows=2 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
45. 0.024 0.024 ↑ 1.0 2 1

CTE Scan on mapowanie_mpk (cost=0.00..0.04 rows=2 width=42) (actual time=0.017..0.024 rows=2 loops=1)

46. 217,467.008 217,467.008 ↓ 195.5 39,104 1

CTE Scan on zuzycie zu (cost=0.00..4.00 rows=200 width=196) (actual time=195,672.271..217,467.008 rows=39,104 loops=1)

47. 0.029 0.094 ↑ 1.0 10 1

Hash (cost=0.20..0.20 rows=10 width=40) (actual time=0.094..0.094 rows=10 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
48. 0.065 0.065 ↑ 1.0 10 1

CTE Scan on zasiedlenie za (cost=0.00..0.20 rows=10 width=40) (actual time=0.018..0.065 rows=10 loops=1)

Planning time : 4.362 ms
Execution time : 219,089.035 ms