explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Leur

Settings
# exclusive inclusive rows x rows loops node
1. 0.525 1,103.879 ↑ 799.0 241 1

HashAggregate (cost=199,328,098.01..199,330,023.65 rows=192,564 width=106) (actual time=1,103.563..1,103.879 rows=241 loops=1)

  • Group Key: r.hrszam, r.kk_ktp, e.wzmgwb, (COALESCE((SubPlan 11), '0'::double precision)), (CASE WHEN ((array_to_string((SubPlan 12), ','::text))::text <> ''::text) THEN (array_to_string((SubPlan 13), ','::text))::character varying ELSE (array_to_string((SubPlan 14), ','::text))::character varying END), e.sbnnam, r.ba1020
2.          

CTE eu

3. 0.012 0.012 ↓ 0.0 0 1

Index Scan using ewz95vsm_idx1 on ewz95vsm (cost=0.42..4.44 rows=1 width=85) (actual time=0.012..0.012 rows=0 loops=1)

  • Index Cond: (wzfnr IS NULL)
4.          

CTE fg

5. 149.464 193.562 ↑ 776.4 248 1

Bitmap Heap Scan on forgalom (cost=14,384.99..279,954.97 rows=192,552 width=16) (actual time=49.252..193.562 rows=248 loops=1)

  • Recheck Cond: (kk_fjc = 'PBE'::bpchar)
  • Filter: ((fdat)::date >= '2019-10-08'::date)
  • Rows Removed by Filter: 607518
  • Heap Blocks: exact=24852
6. 44.098 44.098 ↓ 1.1 607,766 1

Bitmap Index Scan on forgalom_idx2 (cost=0.00..14,336.85 rows=577,656 width=0) (actual time=44.098..44.098 rows=607,766 loops=1)

  • Index Cond: (kk_fjc = 'PBE'::bpchar)
7. 0.035 1,103.354 ↑ 761.1 253 1

Append (cost=0.88..199,044,768.73 rows=192,564 width=106) (actual time=46.706..1,103.354 rows=253 loops=1)

8. 0.405 33.534 ↓ 0.0 0 1

Nested Loop Left Join (cost=0.88..33,647.69 rows=11 width=83) (actual time=33.534..33.534 rows=0 loops=1)

  • Filter: ((e.wzmgwb)::double precision <> (SubPlan 15))
  • Rows Removed by Filter: 239
9. 30.500 30.500 ↓ 119.5 239 1

Index Scan using ewz95vsm_idx2 on ewz95vsm e (cost=0.43..21,739.13 rows=2 width=33) (actual time=6.539..30.500 rows=239 loops=1)

  • Index Cond: (wzdtwz >= ((('1'::text || to_char('2019-10-08 00:00:00'::timestamp without time zone, 'YYMMDD'::text)))::integer)::numeric)
  • Filter: ((upper((sbnnam)::text) ~~ '%BENCZE%'::text) OR (upper((sbnnam)::text) ~~ '%BUS%'::text) OR (upper((sbnnam)::text) ~~ '%FITALA%'::text) OR (upper((sbnnam)::text) ~~ '%FODOR%'::text) OR (upper((sbnnam)::text) ~~ '%HEGEDUS%'::text) OR (upper((sbnnam)::text) ~~ '%HORVATH%'::text) OR (upper((sbnnam)::text) ~~ '%KONCZ%'::text) OR (upper((sbnnam)::text) ~~ '%KOZSELKA%'::text) OR (upper((sbnnam)::text) ~~ '%MOLNAR%'::text) OR (upper((sbnnam)::text) ~~ '%SOMOGYI%'::text) OR (upper((sbnnam)::text) ~~ '%SZABO%'::text) OR (upper((sbnnam)::text) ~~ '%VAJDA%'::text) OR (upper((sbnnam)::text) ~~ '%VARGA%'::text) OR (upper((sbnnam)::text) ~~ '%VASTAG%'::text))
10. 1.673 1.673 ↑ 6.0 1 239

Index Scan using ku_rend2 on ku_rend r (cost=0.45..24.68 rows=6 width=30) (actual time=0.007..0.007 rows=1 loops=239)

  • Index Cond: (hrszam = ((((e.wzbstn)::text || '/'::text) || lpad((e.wzbstp)::text, 3, ' '::text)))::character(10))
11.          

SubPlan (forNested Loop Left Join)

12. 0.000 0.000 ↓ 0.0 0

Aggregate (cost=41.79..41.80 rows=1 width=8) (never executed)

13. 0.000 0.000 ↓ 0.0 0

Index Scan using forgalom4 on forgalom forgalom_7 (cost=0.56..41.77 rows=10 width=8) (never executed)

  • Index Cond: ((rechiv = r.recnum) AND (kk_fjc = 'PBE'::bpchar))
14. 0.000 0.000 ↓ 0.0 0

Unique (cost=41.99..42.04 rows=10 width=32) (never executed)

15. 0.000 0.000 ↓ 0.0 0

Sort (cost=41.99..42.01 rows=10 width=32) (never executed)

  • Sort Key: (btrim((forgalom_8.tpoz_c)::text))
16. 0.000 0.000 ↓ 0.0 0

Index Scan using forgalom4 on forgalom forgalom_8 (cost=0.56..41.82 rows=10 width=32) (never executed)

  • Index Cond: ((rechiv = r.recnum) AND (kk_fjc = 'PBE'::bpchar))
17. 0.000 0.000 ↓ 0.0 0

Unique (cost=41.99..42.04 rows=10 width=32) (never executed)

18. 0.000 0.000 ↓ 0.0 0

Sort (cost=41.99..42.01 rows=10 width=32) (never executed)

  • Sort Key: (btrim((forgalom_9.tpoz_c)::text))
19. 0.000 0.000 ↓ 0.0 0

Index Scan using forgalom4 on forgalom forgalom_9 (cost=0.56..41.82 rows=10 width=32) (never executed)

  • Index Cond: ((rechiv = r.recnum) AND (kk_fjc = 'PBE'::bpchar))
20. 0.000 0.000 ↓ 0.0 0

Unique (cost=906.09..906.60 rows=58 width=32) (never executed)

21. 0.000 0.000 ↓ 0.0 0

Sort (cost=906.09..906.35 rows=102 width=32) (never executed)

  • Sort Key: ((transz_iroda_2.raklapszam)::character varying)
22. 0.000 0.000 ↓ 0.0 0

Seq Scan on transz_iroda transz_iroda_2 (cost=0.00..902.69 rows=102 width=32) (never executed)

  • Filter: ((((bestnr)::text || '/'::text) || (poz)::text) = (r.hrszam)::text)
23. 0.239 0.956 ↑ 1.0 1 239

Aggregate (cost=41.79..41.80 rows=1 width=8) (actual time=0.004..0.004 rows=1 loops=239)

24. 0.717 0.717 ↑ 10.0 1 239

Index Scan using forgalom4 on forgalom forgalom_10 (cost=0.56..41.77 rows=10 width=8) (actual time=0.003..0.003 rows=1 loops=239)

  • Index Cond: ((rechiv = r.recnum) AND (kk_fjc = 'PBE'::bpchar))
25. 0.102 60.745 ↓ 5.0 5 1

Nested Loop Left Join (cost=1.44..22,850.40 rows=1 width=83) (actual time=13.170..60.745 rows=5 loops=1)

  • Filter: (f.recnum IS NULL)
  • Rows Removed by Filter: 246
26. 0.150 30.371 ↓ 21.7 239 1

Nested Loop Left Join (cost=0.88..21,788.62 rows=11 width=51) (actual time=6.260..30.371 rows=239 loops=1)

27. 28.548 28.548 ↓ 119.5 239 1

Index Scan using ewz95vsm_idx2 on ewz95vsm e_1 (cost=0.43..21,739.13 rows=2 width=33) (actual time=6.236..28.548 rows=239 loops=1)

  • Index Cond: (wzdtwz >= ((('1'::text || to_char('2019-10-08 00:00:00'::timestamp without time zone, 'YYMMDD'::text)))::integer)::numeric)
  • Filter: ((upper((sbnnam)::text) ~~ '%BENCZE%'::text) OR (upper((sbnnam)::text) ~~ '%BUS%'::text) OR (upper((sbnnam)::text) ~~ '%FITALA%'::text) OR (upper((sbnnam)::text) ~~ '%FODOR%'::text) OR (upper((sbnnam)::text) ~~ '%HEGEDUS%'::text) OR (upper((sbnnam)::text) ~~ '%HORVATH%'::text) OR (upper((sbnnam)::text) ~~ '%KONCZ%'::text) OR (upper((sbnnam)::text) ~~ '%KOZSELKA%'::text) OR (upper((sbnnam)::text) ~~ '%MOLNAR%'::text) OR (upper((sbnnam)::text) ~~ '%SOMOGYI%'::text) OR (upper((sbnnam)::text) ~~ '%SZABO%'::text) OR (upper((sbnnam)::text) ~~ '%VAJDA%'::text) OR (upper((sbnnam)::text) ~~ '%VARGA%'::text) OR (upper((sbnnam)::text) ~~ '%VASTAG%'::text))
28. 1.673 1.673 ↑ 6.0 1 239

Index Scan using ku_rend2 on ku_rend r_1 (cost=0.45..24.68 rows=6 width=30) (actual time=0.007..0.007 rows=1 loops=239)

  • Index Cond: (hrszam = ((((e_1.wzbstn)::text || '/'::text) || lpad((e_1.wzbstp)::text, 3, ' '::text)))::character(10))
29. 0.717 0.717 ↑ 10.0 1 239

Index Scan using forgalom4 on forgalom f (cost=0.56..2.56 rows=10 width=16) (actual time=0.003..0.003 rows=1 loops=239)

  • Index Cond: ((rechiv = r_1.recnum) AND (kk_fjc = 'PBE'::bpchar))
30.          

SubPlan (forNested Loop Left Join)

31. 0.010 0.020 ↑ 1.0 1 5

Aggregate (cost=41.79..41.80 rows=1 width=8) (actual time=0.004..0.004 rows=1 loops=5)

32. 0.010 0.010 ↓ 0.0 0 5

Index Scan using forgalom4 on forgalom forgalom_4 (cost=0.56..41.77 rows=10 width=8) (actual time=0.002..0.002 rows=0 loops=5)

  • Index Cond: ((rechiv = r_1.recnum) AND (kk_fjc = 'PBE'::bpchar))
33. 0.005 0.040 ↓ 0.0 0 5

Unique (cost=41.99..42.04 rows=10 width=32) (actual time=0.008..0.008 rows=0 loops=5)

34. 0.025 0.035 ↓ 0.0 0 5

Sort (cost=41.99..42.01 rows=10 width=32) (actual time=0.007..0.007 rows=0 loops=5)

  • Sort Key: (btrim((forgalom_5.tpoz_c)::text))
  • Sort Method: quicksort Memory: 25kB
35. 0.010 0.010 ↓ 0.0 0 5

Index Scan using forgalom4 on forgalom forgalom_5 (cost=0.56..41.82 rows=10 width=32) (actual time=0.002..0.002 rows=0 loops=5)

  • Index Cond: ((rechiv = r_1.recnum) AND (kk_fjc = 'PBE'::bpchar))
36. 0.000 0.000 ↓ 0.0 0

Unique (cost=41.99..42.04 rows=10 width=32) (never executed)

37. 0.000 0.000 ↓ 0.0 0

Sort (cost=41.99..42.01 rows=10 width=32) (never executed)

  • Sort Key: (btrim((forgalom_6.tpoz_c)::text))
38. 0.000 0.000 ↓ 0.0 0

Index Scan using forgalom4 on forgalom forgalom_6 (cost=0.56..41.82 rows=10 width=32) (never executed)

  • Index Cond: ((rechiv = r_1.recnum) AND (kk_fjc = 'PBE'::bpchar))
39. 0.000 29.495 ↓ 0.0 0 5

Unique (cost=906.09..906.60 rows=58 width=32) (actual time=5.899..5.899 rows=0 loops=5)

40. 0.020 29.495 ↓ 0.0 0 5

Sort (cost=906.09..906.35 rows=102 width=32) (actual time=5.899..5.899 rows=0 loops=5)

  • Sort Key: ((transz_iroda_1.raklapszam)::character varying)
  • Sort Method: quicksort Memory: 25kB
41. 29.475 29.475 ↓ 0.0 0 5

Seq Scan on transz_iroda transz_iroda_1 (cost=0.00..902.69 rows=102 width=32) (actual time=5.895..5.895 rows=0 loops=5)

  • Filter: ((((bestnr)::text || '/'::text) || (poz)::text) = (r_1.hrszam)::text)
  • Rows Removed by Filter: 20459
42. 0.527 1,009.040 ↑ 776.4 248 1

Hash Left Join (cost=169,696.24..198,986,345.01 rows=192,552 width=97) (actual time=861.362..1,009.040 rows=248 loops=1)

  • Hash Cond: ((r_2.hrszam)::text = (((eu.wzbstn)::text || '/'::text) || lpad((eu.wzbstp)::text, 3, ' '::text)))
43. 3.840 1,005.771 ↑ 776.4 248 1

Hash Left Join (cost=169,696.10..176,194.73 rows=192,552 width=30) (actual time=861.224..1,005.771 rows=248 loops=1)

  • Hash Cond: (fg.rechiv = r_2.recnum)
44. 193.627 193.627 ↑ 776.4 248 1

CTE Scan on fg (cost=0.00..3,851.04 rows=192,552 width=8) (actual time=49.256..193.627 rows=248 loops=1)

45. 217.667 808.304 ↑ 1.0 713,742 1

Hash (cost=160,657.71..160,657.71 rows=723,071 width=30) (actual time=808.304..808.304 rows=713,742 loops=1)

  • Buckets: 1048576 Batches: 1 Memory Usage: 53323kB
46. 590.637 590.637 ↑ 1.0 713,742 1

Seq Scan on ku_rend r_2 (cost=0.00..160,657.71 rows=723,071 width=30) (actual time=0.023..590.637 rows=713,742 loops=1)

47. 0.000 0.014 ↓ 0.0 0 1

Hash (cost=0.13..0.13 rows=1 width=61) (actual time=0.014..0.014 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
48. 0.014 0.014 ↓ 0.0 0 1

CTE Scan on eu (cost=0.00..0.13 rows=1 width=61) (actual time=0.014..0.014 rows=0 loops=1)

  • Filter: ((upper((sbnnam)::text) ~~ '%BENCZE%'::text) OR (upper((sbnnam)::text) ~~ '%BUS%'::text) OR (upper((sbnnam)::text) ~~ '%FITALA%'::text) OR (upper((sbnnam)::text) ~~ '%FODOR%'::text) OR (upper((sbnnam)::text) ~~ '%HEGEDUS%'::text) OR (upper((sbnnam)::text) ~~ '%HORVATH%'::text) OR (upper((sbnnam)::text) ~~ '%KONCZ%'::text) OR (upper((sbnnam)::text) ~~ '%KOZSELKA%'::text) OR (upper((sbnnam)::text) ~~ '%MOLNAR%'::text) OR (upper((sbnnam)::text) ~~ '%SOMOGYI%'::text) OR (upper((sbnnam)::text) ~~ '%SZABO%'::text) OR (upper((sbnnam)::text) ~~ '%VAJDA%'::text) OR (upper((sbnnam)::text) ~~ '%VARGA%'::text) OR (upper((sbnnam)::text) ~~ '%VASTAG%'::text))
49.          

SubPlan (forHash Left Join)

50. 0.000 0.744 ↑ 1.0 1 248

Aggregate (cost=41.79..41.80 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=248)

51. 0.744 0.744 ↑ 10.0 1 248

Index Scan using forgalom4 on forgalom forgalom_1 (cost=0.56..41.77 rows=10 width=8) (actual time=0.003..0.003 rows=1 loops=248)

  • Index Cond: ((rechiv = r_2.recnum) AND (kk_fjc = 'PBE'::bpchar))
52. 0.248 0.992 ↑ 10.0 1 248

Unique (cost=41.99..42.04 rows=10 width=32) (actual time=0.004..0.004 rows=1 loops=248)

53. 0.248 0.744 ↑ 10.0 1 248

Sort (cost=41.99..42.01 rows=10 width=32) (actual time=0.003..0.003 rows=1 loops=248)

  • Sort Key: (btrim((forgalom_2.tpoz_c)::text))
  • Sort Method: quicksort Memory: 25kB
54. 0.496 0.496 ↑ 10.0 1 248

Index Scan using forgalom4 on forgalom forgalom_2 (cost=0.56..41.82 rows=10 width=32) (actual time=0.002..0.002 rows=1 loops=248)

  • Index Cond: ((rechiv = r_2.recnum) AND (kk_fjc = 'PBE'::bpchar))
55. 0.248 0.992 ↑ 10.0 1 248

Unique (cost=41.99..42.04 rows=10 width=32) (actual time=0.003..0.004 rows=1 loops=248)

56. 0.248 0.744 ↑ 10.0 1 248

Sort (cost=41.99..42.01 rows=10 width=32) (actual time=0.003..0.003 rows=1 loops=248)

  • Sort Key: (btrim((forgalom_3.tpoz_c)::text))
  • Sort Method: quicksort Memory: 25kB
57. 0.496 0.496 ↑ 10.0 1 248

Index Scan using forgalom4 on forgalom forgalom_3 (cost=0.56..41.82 rows=10 width=32) (actual time=0.002..0.002 rows=1 loops=248)

  • Index Cond: ((rechiv = r_2.recnum) AND (kk_fjc = 'PBE'::bpchar))
58. 0.000 0.000 ↓ 0.0 0

Unique (cost=906.09..906.60 rows=58 width=32) (never executed)

59. 0.000 0.000 ↓ 0.0 0

Sort (cost=906.09..906.35 rows=102 width=32) (never executed)

  • Sort Key: ((transz_iroda.raklapszam)::character varying)
60. 0.000 0.000 ↓ 0.0 0

Seq Scan on transz_iroda (cost=0.00..902.69 rows=102 width=32) (never executed)

  • Filter: ((((bestnr)::text || '/'::text) || (poz)::text) = (r_2.hrszam)::text)