explain.depesz.com

PostgreSQL's explain analyze made readable

Result: CLEo

Settings
# exclusive inclusive rows x rows loops node
1. 0.017 1,290,813.428 ↑ 32.2 5 1

Unique (cost=14,278,707,229.43..14,278,707,231.04 rows=161 width=92) (actual time=1,290,813.409..1,290,813.428 rows=5 loops=1)

2. 0.055 1,290,813.411 ↑ 4.2 38 1

Sort (cost=14,278,707,229.43..14,278,707,229.83 rows=161 width=92) (actual time=1,290,813.408..1,290,813.411 rows=38 loops=1)

  • Sort Key: rd.idpodmiot, rd.idrachunek, (to_char(al.datatransakcji, 'YYYY-MM-DD'::text))
  • Sort Method: quicksort Memory: 27kB
3. 260.190 1,290,813.356 ↑ 4.2 38 1

Merge Right Join (cost=14,278,623,931.34..14,278,707,223.53 rows=161 width=92) (actual time=1,290,812.345..1,290,813.356 rows=38 loops=1)

  • Merge Cond: (a.id = al.id)
4. 1,373.715 1,290,547.062 ↑ 1.3 1,382,939 1

Unique (cost=14,278,622,710.18..14,278,682,863.62 rows=1,850,875 width=164) (actual time=1,288,798.255..1,290,547.062 rows=1,382,939 loops=1)

5. 3,457.306 1,289,173.347 ↑ 1.3 1,382,986 1

Sort (cost=14,278,622,710.18..14,278,627,337.37 rows=1,850,875 width=164) (actual time=1,288,798.251..1,289,173.347 rows=1,382,986 loops=1)

  • Sort Key: a.id, sg.idsimpleexchange, mt.idtypinstrumentu, urd.idtyprozliczania, pl.idprzelew, pp.idstanprzelwu, ((((COALESCE(sp.wartosc, ''::character varying))::text || '/'::text) || ''::text)), (CASE WHEN ((length(btrim((COALESCE(pp.nrreferencyjny, ''::character varying))::text)) = 0) AND (pp.idstanprzelwu <> ALL ('{4,5}'::integer[]))) THEN 1 ELSE 0 END), (COALESCE(zk.nrzlecenia, (a.idzlecenieklienta)::character varying)), (CASE WHEN ((SubPlan 1) < 2) THEN 1 ELSE 0 END), ((SubPlan 2)), ((SubPlan 3))
  • Sort Method: external merge Disk: 75376kB
6. 13,177.855 1,285,716.041 ↑ 1.3 1,383,036 1

Hash Left Join (cost=43,685.06..14,278,345,445.16 rows=1,850,875 width=164) (actual time=1,659.599..1,285,716.041 rows=1,383,036 loops=1)

  • Hash Cond: (pp.idstanprzelwu = sp.id)
7. 928.765 15,358.440 ↑ 1.3 1,383,036 1

Hash Left Join (cost=43,684.13..352,228.03 rows=1,850,875 width=53) (actual time=1,657.699..15,358.440 rows=1,383,036 loops=1)

  • Hash Cond: (pl.idprzelew = pp.id)
8. 1,742.399 14,079.016 ↑ 1.3 1,383,036 1

Hash Left Join (cost=31,495.67..335,180.95 rows=1,850,875 width=33) (actual time=1,305.721..14,079.016 rows=1,383,036 loops=1)

  • Hash Cond: ((a.idrachunek = ur.idrachunek) AND (a.idgielda = urd.idgielda))
9. 1,299.576 12,174.036 ↓ 1.0 1,382,989 1

Hash Left Join (cost=27,806.83..178,470.80 rows=1,379,616 width=37) (actual time=1,142.801..12,174.036 rows=1,382,989 loops=1)

  • Hash Cond: (a.id = pl.idalokacja)
10. 1,231.681 10,030.275 ↓ 1.0 1,382,989 1

Hash Left Join (cost=7,664.29..154,706.76 rows=1,379,616 width=33) (actual time=297.931..10,030.275 rows=1,382,989 loops=1)

  • Hash Cond: (a.idgielda = sg.id)
11. 1,031.820 8,798.545 ↓ 1.0 1,382,989 1

Hash Left Join (cost=7,662.44..150,756.86 rows=1,379,616 width=29) (actual time=297.872..8,798.545 rows=1,382,989 loops=1)

  • Hash Cond: (a.idzlecenieklienta = zk.idzlecenieklienta)
12. 5,095.684 7,756.133 ↓ 1.0 1,382,989 1

Merge Left Join (cost=7,252.10..131,376.79 rows=1,379,616 width=20) (actual time=287.235..7,756.133 rows=1,382,989 loops=1)

  • Merge Cond: (a.idpapier = id.idinstrument)
  • Join Filter: (a.idgielda = id.idgielda)
  • Rows Removed by Join Filter: 2098015
13. 1,803.787 1,803.787 ↓ 1.0 1,382,989 1

Index Scan using idx_tran_alokacja_idpapier on alokacja a (cost=0.43..112,695.27 rows=1,379,616 width=20) (actual time=0.023..1,803.787 rows=1,382,989 loops=1)

14. 588.273 856.662 ↓ 2,150.9 3,497,350 1

Sort (cost=7,251.67..7,255.74 rows=1,626 width=12) (actual time=287.202..856.662 rows=3,497,350 loops=1)

  • Sort Key: id.idinstrument
  • Sort Method: quicksort Memory: 1913kB
15. 17.877 268.389 ↓ 15.0 24,425 1

Gather (cost=1,023.60..7,164.95 rows=1,626 width=12) (actual time=2.310..268.389 rows=24,425 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
16. 10.786 250.512 ↓ 12.0 8,142 3

Hash Left Join (cost=23.60..6,002.35 rows=678 width=12) (actual time=1.386..250.512 rows=8,142 loops=3)

  • Hash Cond: (id.idtyppapieru = mt.idtyppapieru)
17. 238.843 238.843 ↓ 12.0 8,142 3

Parallel Seq Scan on instrumentdane id (cost=0.00..5,967.88 rows=678 width=12) (actual time=0.135..238.843 rows=8,142 loops=3)

  • Filter: (to_char(data_do, 'yyyy-MM-dd'::text) = '3000-01-01'::text)
  • Rows Removed by Filter: 100064
18. 0.306 0.883 ↑ 1.0 497 3

Hash (cost=17.39..17.39 rows=497 width=8) (actual time=0.883..0.883 rows=497 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 28kB
19. 0.577 0.577 ↑ 1.0 497 3

Seq Scan on mapatypow mt (cost=0.00..17.39 rows=497 width=8) (actual time=0.034..0.577 rows=497 loops=3)

  • Filter: (idkontekst = 2)
  • Rows Removed by Filter: 510
20. 3.090 10.592 ↑ 1.0 8,384 1

Hash (cost=305.05..305.05 rows=8,423 width=13) (actual time=10.592..10.592 rows=8,384 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 521kB
21. 7.502 7.502 ↑ 1.0 8,384 1

Seq Scan on zlecenieklienta zk (cost=0.00..305.05 rows=8,423 width=13) (actual time=0.011..7.502 rows=8,384 loops=1)

  • Filter: (datazamkniecia IS NULL)
  • Rows Removed by Filter: 1639
22. 0.019 0.049 ↑ 1.0 47 1

Hash (cost=1.27..1.27 rows=47 width=8) (actual time=0.049..0.049 rows=47 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
23. 0.030 0.030 ↑ 1.0 47 1

Seq Scan on gielda sg (cost=0.00..1.27 rows=47 width=8) (actual time=0.007..0.030 rows=47 loops=1)

24. 50.962 844.185 ↓ 1.1 122,132 1

Hash (cost=18,698.07..18,698.07 rows=115,557 width=8) (actual time=844.185..844.185 rows=122,132 loops=1)

  • Buckets: 131072 Batches: 1 Memory Usage: 5795kB
25. 36.042 793.223 ↓ 1.1 122,133 1

Subquery Scan on pl (cost=16,386.93..18,698.07 rows=115,557 width=8) (actual time=687.975..793.223 rows=122,133 loops=1)

26. 276.929 757.181 ↓ 1.1 122,133 1

HashAggregate (cost=16,386.93..17,542.50 rows=115,557 width=8) (actual time=687.973..757.181 rows=122,133 loops=1)

  • Group Key: spp.idalokacja
27. 246.618 480.252 ↑ 1.0 326,746 1

Hash Join (cost=9,181.11..14,745.57 rows=328,273 width=8) (actual time=173.338..480.252 rows=326,746 loops=1)

  • Hash Cond: (spp.idprzelewposttransakcyjny = pp_1.id)
28. 61.586 61.586 ↑ 1.0 328,236 1

Seq Scan on skladowaprzelewu spp (cost=0.00..4,702.73 rows=328,273 width=8) (actual time=0.008..61.586 rows=328,236 loops=1)

29. 88.877 172.048 ↑ 1.0 255,196 1

Hash (cost=5,984.41..5,984.41 rows=255,736 width=4) (actual time=172.048..172.048 rows=255,196 loops=1)

  • Buckets: 262144 Batches: 1 Memory Usage: 11020kB
30. 83.171 83.171 ↑ 1.0 255,196 1

Index Only Scan using przelewposttransakcyjny_pkey on przelewposttransakcyjny pp_1 (cost=0.42..5,984.41 rows=255,736 width=4) (actual time=0.030..83.171 rows=255,196 loops=1)

  • Heap Fetches: 30465
31. 26.477 162.581 ↑ 1.0 48,803 1

Hash (cost=2,956.43..2,956.43 rows=48,827 width=12) (actual time=162.581..162.581 rows=48,803 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 2609kB
32. 34.170 136.104 ↑ 1.0 48,803 1

Hash Join (cost=1,305.05..2,956.43 rows=48,827 width=12) (actual time=37.383..136.104 rows=48,803 loops=1)

  • Hash Cond: (urd.idumowarachunku = ur.id)
33. 64.915 64.915 ↑ 1.0 48,803 1

Seq Scan on umowarachunkudane urd (cost=0.00..1,523.20 rows=48,827 width=12) (actual time=0.027..64.915 rows=48,803 loops=1)

  • Filter: ((CURRENT_TIMESTAMP < okres_do) AND (CURRENT_TIMESTAMP < okres_do))
  • Rows Removed by Filter: 5365
34. 18.929 37.019 ↑ 1.0 50,001 1

Hash (cost=678.69..678.69 rows=50,109 width=8) (actual time=37.019..37.019 rows=50,001 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 2466kB
35. 18.090 18.090 ↑ 1.0 50,001 1

Seq Scan on umowarachunku ur (cost=0.00..678.69 rows=50,109 width=8) (actual time=0.022..18.090 rows=50,001 loops=1)

36. 128.806 350.659 ↑ 1.0 255,196 1

Hash (cost=8,991.76..8,991.76 rows=255,736 width=24) (actual time=350.659..350.659 rows=255,196 loops=1)

  • Buckets: 262144 Batches: 1 Memory Usage: 16284kB
37. 221.853 221.853 ↑ 1.0 255,196 1

Seq Scan on przelewposttransakcyjny pp (cost=0.00..8,991.76 rows=255,736 width=24) (actual time=0.012..221.853 rows=255,196 loops=1)

38. 0.009 0.022 ↑ 1.0 6 1

Hash (cost=0.86..0.86 rows=6 width=7) (actual time=0.022..0.022 rows=6 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
39. 0.013 0.013 ↑ 1.0 6 1

Seq Scan on stanprzelewu sp (cost=0.00..0.86 rows=6 width=7) (actual time=0.010..0.013 rows=6 loops=1)

40.          

SubPlan (forHash Left Join)

41. 8,298.216 275,224.164 ↑ 200.0 1 1,383,036

HashAggregate (cost=2,435.26..2,437.26 rows=200 width=12) (actual time=0.198..0.199 rows=1 loops=1,383,036)

  • Group Key: a.id
42. 34,575.900 266,925.948 ↑ 694.0 1 1,383,036

HashAggregate (cost=2,416.17..2,424.85 rows=694 width=48) (actual time=0.190..0.193 rows=1 loops=1,383,036)

  • Group Key: ta.idalokacja, btrim((COALESCE(tz.tradeaccount, ''::character varying))::text)
43. 65,135.063 232,350.048 ↑ 34.7 20 1,383,036

Nested Loop Left Join (cost=0.87..2,412.70 rows=694 width=36) (actual time=0.018..0.168 rows=20 loops=1,383,036)

44. 27,660.720 27,660.720 ↑ 34.7 20 1,383,036

Index Scan using idx_transakcjealokacje_idalokacja on transakcjealokacje ta (cost=0.44..26.93 rows=694 width=8) (actual time=0.009..0.020 rows=20 loops=1,383,036)

  • Index Cond: (idalokacja = a.id)
45. 139,554.265 139,554.265 ↑ 1.0 1 27,910,853

Index Scan using transakcjazrodlowa_pkey on transakcjazrodlowa tz (cost=0.43..3.44 rows=1 width=10) (actual time=0.005..0.005 rows=1 loops=27,910,853)

  • Index Cond: (id = ta.idtransakcja)
46. 35,958.936 236,499.156 ↑ 1.0 1 1,383,036

Aggregate (cost=2,414.44..2,414.45 rows=1 width=32) (actual time=0.171..0.171 rows=1 loops=1,383,036)

47. 38,857.379 200,540.220 ↑ 34.7 20 1,383,036

Nested Loop Left Join (cost=0.87..2,410.97 rows=694 width=6) (actual time=0.014..0.145 rows=20 loops=1,383,036)

48. 22,128.576 22,128.576 ↑ 34.7 20 1,383,036

Index Scan using idx_transakcjealokacje_idalokacja on transakcjealokacje ta_1 (cost=0.44..26.93 rows=694 width=4) (actual time=0.007..0.016 rows=20 loops=1,383,036)

  • Index Cond: (idalokacja = a.id)
49. 139,554.265 139,554.265 ↑ 1.0 1 27,910,853

Index Scan using transakcjazrodlowa_pkey on transakcjazrodlowa tz_1 (cost=0.43..3.44 rows=1 width=10) (actual time=0.005..0.005 rows=1 loops=27,910,853)

  • Index Cond: (id = ta_1.idtransakcja)
50. 15,213.396 745,456.404 ↑ 1.0 1 1,383,036

Aggregate (cost=2,862.44..2,862.45 rows=1 width=4) (actual time=0.539..0.539 rows=1 loops=1,383,036)

  • Filter: (min(rd_1.idrachuneknadrzedny) = max(rd_1.idrachuneknadrzedny))
  • Rows Removed by Filter: 0
51. 77,505.969 730,243.008 ↑ 30.6 18 1,383,036

Nested Loop (cost=1.45..2,858.31 rows=551 width=4) (actual time=0.038..0.528 rows=18 loops=1,383,036)

52. 51,054.570 414,910.800 ↑ 12.2 57 1,383,036

Nested Loop (cost=1.16..2,631.46 rows=694 width=8) (actual time=0.028..0.300 rows=57 loops=1,383,036)

53. 36,091.307 196,391.112 ↑ 34.7 20 1,383,036

Nested Loop (cost=0.87..2,410.97 rows=694 width=4) (actual time=0.013..0.142 rows=20 loops=1,383,036)

54. 20,745.540 20,745.540 ↑ 34.7 20 1,383,036

Index Scan using idx_transakcjealokacje_idalokacja on transakcjealokacje ta_2 (cost=0.44..26.93 rows=694 width=4) (actual time=0.006..0.015 rows=20 loops=1,383,036)

  • Index Cond: (idalokacja = a.id)
55. 139,554.265 139,554.265 ↑ 1.0 1 27,910,853

Index Scan using transakcjazrodlowa_pkey on transakcjazrodlowa tz_2 (cost=0.43..3.44 rows=1 width=8) (actual time=0.005..0.005 rows=1 loops=27,910,853)

  • Index Cond: (id = ta_2.idtransakcja)
56. 167,465.118 167,465.118 ↓ 3.0 3 27,910,853

Index Scan using idx_kart_rachunekdane_idrach on rachunekdane rd_1 (cost=0.29..0.31 rows=1 width=12) (actual time=0.004..0.006 rows=3 loops=27,910,853)

  • Index Cond: (idrachunek = tz_2.idrachunek)
57. 237,826.239 237,826.239 ↓ 0.0 0 79,275,413

Index Only Scan using rlastidrachdidx on rachunek_last rl_1 (cost=0.29..0.32 rows=1 width=4) (actual time=0.003..0.003 rows=0 loops=79,275,413)

  • Index Cond: (idrachunekdane = rd_1.id)
  • Heap Fetches: 24913256
58. 0.129 6.104 ↑ 3.2 38 1

Sort (cost=1,221.16..1,221.46 rows=120 width=20) (actual time=6.098..6.104 rows=38 loops=1)

  • Sort Key: al.id
  • Sort Method: quicksort Memory: 27kB
59. 0.034 5.975 ↑ 3.2 38 1

Nested Loop (cost=1.01..1,217.01 rows=120 width=20) (actual time=0.598..5.975 rows=38 loops=1)

60. 0.667 5.509 ↑ 1.4 108 1

Nested Loop (cost=0.72..1,162.10 rows=151 width=24) (actual time=0.552..5.509 rows=108 loops=1)

61. 0.978 0.978 ↓ 1.5 644 1

Index Scan using idx_tran_alokacja_datatransakcji_date on alokacja al (cost=0.43..237.90 rows=440 width=16) (actual time=0.181..0.978 rows=644 loops=1)

  • Index Cond: ((datatransakcji)::date = '2019-01-03'::date)
  • Filter: ((idstatusalokacji = 2) AND (idtypsztukowania = 1))
  • Rows Removed by Filter: 6
62. 3.864 3.864 ↓ 0.0 0 644

Index Scan using idx_kart_rachunekdane_idrach on rachunekdane rd (cost=0.29..2.09 rows=1 width=12) (actual time=0.006..0.006 rows=0 loops=644)

  • Index Cond: (idrachunek = al.idrachunek)
  • Filter: dkd
  • Rows Removed by Filter: 2
63. 0.432 0.432 ↓ 0.0 0 108

Index Only Scan using rlastidrachdidx on rachunek_last rl (cost=0.29..0.35 rows=1 width=4) (actual time=0.003..0.004 rows=0 loops=108)

  • Index Cond: (idrachunekdane = rd.id)
  • Heap Fetches: 36