explain.depesz.com

PostgreSQL's explain analyze made readable

Result: FK2C

Settings
# exclusive inclusive rows x rows loops node
1. 0.016 374,120.857 ↓ 1.8 11 1

Limit (cost=566,008.51..566,008.65 rows=6 width=525) (actual time=374,120.827..374,120.857 rows=11 loops=1)

2. 0.023 374,120.841 ↓ 1.8 11 1

Subquery Scan on b (cost=566,008.51..566,008.65 rows=6 width=525) (actual time=374,120.827..374,120.841 rows=11 loops=1)

3. 0.128 374,120.818 ↓ 1.8 11 1

Sort (cost=566,008.51..566,008.53 rows=6 width=429) (actual time=374,120.816..374,120.818 rows=11 loops=1)

  • Sort Key: "*SELECT* 1".fokonyv, "*SELECT* 1".ei_fokonyv
  • Sort Method: quicksort Memory: 266kB
4. 0.338 374,120.690 ↓ 10.7 64 1

HashAggregate (cost=566,008.32..566,008.38 rows=6 width=429) (actual time=374,120.668..374,120.690 rows=64 loops=1)

  • Group Key: "*SELECT* 1".ktkkod, "*SELECT* 1".fokonyv, "*SELECT* 1".fokonyv_megnev, "*SELECT* 1".bkjel, "*SELECT* 1".ei_fokonyv, "*SELECT* 1".teljesites, "*SELECT* 1".telj_utolso_ho, "*SELECT* 1".modositott_ei, "*SELECT* 1".eredeti_ei, "*SELECT* 1".kontir_idk
5. 0.016 374,120.352 ↓ 10.7 64 1

Append (cost=3,073.58..566,008.17 rows=6 width=429) (actual time=167.454..374,120.352 rows=64 loops=1)

6. 0.014 890.365 ↓ 61.0 61 1

Subquery Scan on *SELECT* 1 (cost=3,073.58..3,226.75 rows=1 width=311) (actual time=167.453..890.365 rows=61 loops=1)

7. 656.635 890.351 ↓ 61.0 61 1

GroupAggregate (cost=3,073.58..3,226.74 rows=1 width=323) (actual time=167.452..890.351 rows=61 loops=1)

  • Group Key: kontir.ktkkod_id, (COALESCE(ktkkod.kod, ''::text)), tf.elokontkod, tf.eifok, tf.megnevezes, tf.bkjel, ef.elokontkod
8. 30.096 167.653 ↓ 27,473.0 27,473 1

Sort (cost=3,073.58..3,073.58 rows=1 width=182) (actual time=159.931..167.653 rows=27,473 loops=1)

  • Sort Key: kontir.ktkkod_id, (COALESCE(ktkkod.kod, ''::text)), tf.elokontkod, tf.eifok, tf.megnevezes, tf.bkjel, ef.elokontkod
  • Sort Method: quicksort Memory: 6174kB
9. 10.325 137.557 ↓ 27,473.0 27,473 1

Nested Loop Left Join (cost=77.66..3,073.57 rows=1 width=182) (actual time=54.486..137.557 rows=27,473 loops=1)

10. 4.758 99.759 ↓ 27,473.0 27,473 1

Nested Loop (cost=77.38..3,073.26 rows=1 width=150) (actual time=54.473..99.759 rows=27,473 loops=1)

11. 0.320 5.610 ↓ 1,077.0 1,077 1

Nested Loop (cost=76.95..1,048.83 rows=1 width=127) (actual time=0.748..5.610 rows=1,077 loops=1)

12. 2.583 3.136 ↓ 359.0 1,077 1

Bitmap Heap Scan on elokontszotar tf (cost=76.67..1,023.87 rows=3 width=120) (actual time=0.722..3.136 rows=1,077 loops=1)

  • Recheck Cond: ((elokontkod ~~ '05%'::text) OR (elokontkod ~~ '09%'::text))
  • Filter: (((tipus)::text = 'Telj.'::text) AND ((elokontkod ~~ '05%'::text) OR (elokontkod ~~ '09%'::text)) AND (public.lower(elokontkod) ~~ '05%'::text))
  • Rows Removed by Filter: 2541
  • Heap Blocks: exact=332
13. 0.003 0.553 ↓ 0.0 0 1

BitmapOr (cost=76.67..76.67 rows=3,209 width=0) (actual time=0.552..0.553 rows=0 loops=1)

14. 0.322 0.322 ↓ 1.1 1,893 1

Bitmap Index Scan on elokontszotar_elokontkod (cost=0.00..41.60 rows=1,731 width=0) (actual time=0.322..0.322 rows=1,893 loops=1)

  • Index Cond: ((elokontkod >= '05'::text) AND (elokontkod < '06'::text))
15. 0.228 0.228 ↓ 1.2 1,725 1

Bitmap Index Scan on elokontszotar_elokontkod (cost=0.00..35.07 rows=1,478 width=0) (actual time=0.228..0.228 rows=1,725 loops=1)

  • Index Cond: ((elokontkod >= '09'::text) AND (elokontkod < '0:'::text))
16. 2.154 2.154 ↑ 1.0 1 1,077

Index Scan using elokontszotar_kontid_key on elokontszotar ef (cost=0.29..8.31 rows=1 width=11) (actual time=0.002..0.002 rows=1 loops=1,077)

  • Index Cond: (kontid = tf.eifok)
  • Filter: (public.lower(elokontkod) ~~ '05%'::text)
17. 89.391 89.391 ↑ 15.0 26 1,077

Index Scan using kontir_fokonyv on kontir (cost=0.43..2,020.52 rows=391 width=27) (actual time=0.047..0.083 rows=26 loops=1,077)

  • Index Cond: (fokonyv = tf.kontid)
  • Filter: ((datum >= '2018-01-01'::date) AND (datum <= '2018-12-31'::date) AND (NOT (konyvelve IS DISTINCT FROM true)) AND (intkod = ANY ('{409854,576701,732923,803944}'::bigint[])) AND (eikod = ANY ('{13,12,14,11}'::integer[])) AND (biznem <> ALL ('{0,1,91,92,93,94,95,96,97}'::integer[])))
  • Rows Removed by Filter: 48
18. 27.473 27.473 ↑ 1.0 1 27,473

Index Scan using ktkkod_pkey on ktkkod (cost=0.28..0.29 rows=1 width=12) (actual time=0.001..0.001 rows=1 loops=27,473)

  • Index Cond: (id = kontir.ktkkod_id)
19.          

SubPlan (for GroupAggregate)

20. 18.422 39.528 ↑ 1.0 1 61

Aggregate (cost=76.17..76.18 rows=1 width=32) (actual time=0.648..0.648 rows=1 loops=61)

21. 4.453 21.106 ↓ 28.0 28 61

Bitmap Heap Scan on kontir belso (cost=67.89..75.91 rows=1 width=11) (actual time=0.280..0.346 rows=28 loops=61)

  • Recheck Cond: ((fokonyv = tf.eifok) AND (ktkkod_id = kontir.ktkkod_id) AND (datum >= '2018-01-01'::date) AND (datum <= '2018-12-31'::date))
  • Filter: ((biznem = ANY ('{60,61}'::integer[])) AND (NOT (konyvelve IS DISTINCT FROM true)) AND (eikod = ANY ('{13,12,14,11}'::integer[])) AND (intkod = ANY ('{409854,576701,732923,803944}'::bigint[])))
  • Rows Removed by Filter: 22
  • Heap Blocks: exact=1926
22. 0.244 16.653 ↓ 0.0 0 61

BitmapAnd (cost=67.89..67.89 rows=2 width=0) (actual time=0.273..0.273 rows=0 loops=61)

23. 0.549 0.549 ↑ 31.1 51 61

Bitmap Index Scan on kontir_fokonyv (cost=0.00..32.31 rows=1,585 width=0) (actual time=0.009..0.009 rows=51 loops=61)

  • Index Cond: (fokonyv = tf.eifok)
24. 15.860 15.860 ↓ 1.9 2,274 61

Bitmap Index Scan on ix_kontir_ktkkod_datum (cost=0.00..35.33 rows=1,192 width=0) (actual time=0.260..0.260 rows=2,274 loops=61)

  • Index Cond: ((ktkkod_id = kontir.ktkkod_id) AND (datum >= '2018-01-01'::date) AND (datum <= '2018-12-31'::date))
25. 8.906 26.535 ↑ 1.0 1 61

Aggregate (cost=76.17..76.18 rows=1 width=32) (actual time=0.435..0.435 rows=1 loops=61)

26. 2.562 17.629 ↓ 14.0 14 61

Bitmap Heap Scan on kontir belso_1 (cost=67.89..75.91 rows=1 width=11) (actual time=0.252..0.289 rows=14 loops=61)

  • Recheck Cond: ((fokonyv = tf.eifok) AND (ktkkod_id = kontir.ktkkod_id) AND (datum >= '2018-01-01'::date) AND (datum <= '2018-12-31'::date))
  • Filter: ((NOT (konyvelve IS DISTINCT FROM true)) AND (biznem = 61) AND (eikod = ANY ('{13,12,14,11}'::integer[])) AND (intkod = ANY ('{409854,576701,732923,803944}'::bigint[])))
  • Rows Removed by Filter: 37
  • Heap Blocks: exact=1926
27. 0.244 15.067 ↓ 0.0 0 61

BitmapAnd (cost=67.89..67.89 rows=2 width=0) (actual time=0.247..0.247 rows=0 loops=61)

28. 0.488 0.488 ↑ 31.1 51 61

Bitmap Index Scan on kontir_fokonyv (cost=0.00..32.31 rows=1,585 width=0) (actual time=0.008..0.008 rows=51 loops=61)

  • Index Cond: (fokonyv = tf.eifok)
29. 14.335 14.335 ↓ 1.9 2,274 61

Bitmap Index Scan on ix_kontir_ktkkod_datum (cost=0.00..35.33 rows=1,192 width=0) (actual time=0.235..0.235 rows=2,274 loops=61)

  • Index Cond: ((ktkkod_id = kontir.ktkkod_id) AND (datum >= '2018-01-01'::date) AND (datum <= '2018-12-31'::date))
30. 0.003 373,229.971 ↑ 1.7 3 1

Subquery Scan on *SELECT* 2 (cost=562,777.34..562,781.42 rows=5 width=311) (actual time=373,229.843..373,229.971 rows=3 loops=1)

31. 0.277 373,229.968 ↑ 1.7 3 1

GroupAggregate (cost=562,777.34..562,781.37 rows=5 width=319) (actual time=373,229.842..373,229.968 rows=3 loops=1)

  • Group Key: kontir_1.ktkkod_id, (COALESCE(ktkkod_1.kod, ''::text)), ef_1.elokontkod, ef_1.megnevezes, ef_1.bkjel
32. 0.044 373,229.691 ↓ 1.2 6 1

Sort (cost=562,777.34..562,777.35 rows=5 width=171) (actual time=373,229.689..373,229.691 rows=6 loops=1)

  • Sort Key: kontir_1.ktkkod_id, (COALESCE(ktkkod_1.kod, ''::text)), ef_1.elokontkod, ef_1.megnevezes, ef_1.bkjel
  • Sort Method: quicksort Memory: 25kB
33. 0.038 373,229.647 ↓ 1.2 6 1

Nested Loop Left Join (cost=23,952.74..562,777.28 rows=5 width=171) (actual time=124,999.270..373,229.647 rows=6 loops=1)

34. 42.755 373,229.525 ↓ 1.2 6 1

Hash Join (cost=23,952.47..562,775.77 rows=5 width=139) (actual time=124,999.251..373,229.525 rows=6 loops=1)

  • Hash Cond: (kontir_1.fokonyv = ef_1.kontid)
  • Join Filter: (NOT (SubPlan 1))
  • Rows Removed by Join Filter: 1235
35. 215.626 266.731 ↓ 1.2 328,399 1

Bitmap Heap Scan on kontir kontir_1 (cost=22,912.54..60,965.23 rows=268,345 width=27) (actual time=53.705..266.731 rows=328,399 loops=1)

  • Recheck Cond: ((datum >= '2018-01-01'::date) AND (datum <= '2018-12-31'::date))
  • Filter: ((NOT (konyvelve IS DISTINCT FROM true)) AND (intkod = ANY ('{409854,576701,732923,803944}'::bigint[])) AND (eikod = ANY ('{13,12,14,11}'::integer[])) AND (biznem <> ALL ('{0,1,91,92,93,94,95,96,97}'::integer[])))
  • Rows Removed by Filter: 85846
  • Heap Blocks: exact=14070
36. 51.105 51.105 ↓ 1.0 414,245 1

Bitmap Index Scan on kontir_userid_datum (cost=0.00..22,845.46 rows=412,508 width=0) (actual time=51.105..51.105 rows=414,245 loops=1)

  • Index Cond: ((datum >= '2018-01-01'::date) AND (datum <= '2018-12-31'::date))
37. 0.102 2.021 ↓ 412.0 412 1

Hash (cost=1,039.91..1,039.91 rows=1 width=116) (actual time=2.021..2.021 rows=412 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 50kB
38. 1.487 1.919 ↓ 412.0 412 1

Bitmap Heap Scan on elokontszotar ef_1 (cost=76.67..1,039.91 rows=1 width=116) (actual time=0.486..1.919 rows=412 loops=1)

  • Recheck Cond: ((elokontkod ~~ '05%'::text) OR (elokontkod ~~ '09%'::text))
  • Filter: (((tipus)::text = 'Ei.'::text) AND ((elokontkod ~~ '05%'::text) OR (elokontkod ~~ '09%'::text)) AND (public.lower(elokontkod) ~~ '05%'::text) AND (public.lower(elokontkod) ~~ '05%'::text))
  • Rows Removed by Filter: 3206
  • Heap Blocks: exact=332
39. 0.000 0.432 ↓ 0.0 0 1

BitmapOr (cost=76.67..76.67 rows=3,209 width=0) (actual time=0.432..0.432 rows=0 loops=1)

40. 0.233 0.233 ↓ 1.1 1,893 1

Bitmap Index Scan on elokontszotar_elokontkod (cost=0.00..41.60 rows=1,731 width=0) (actual time=0.233..0.233 rows=1,893 loops=1)

  • Index Cond: ((elokontkod >= '05'::text) AND (elokontkod < '06'::text))
41. 0.200 0.200 ↓ 1.2 1,725 1

Bitmap Index Scan on elokontszotar_elokontkod (cost=0.00..35.07 rows=1,478 width=0) (actual time=0.199..0.200 rows=1,725 loops=1)

  • Index Cond: ((elokontkod >= '09'::text) AND (elokontkod < '0:'::text))
42.          

SubPlan (for Hash Join)

43. 193.596 372,918.018 ↑ 1.0 1 1,241

Hash Semi Join (cost=54,842.06..56,216.65 rows=1 width=4) (actual time=300.498..300.498 rows=1 loops=1,241)

  • Hash Cond: (t1.kontid = kb.fokonyv)
44. 4,147.422 4,147.422 ↓ 33.6 1,881 1,241

Seq Scan on elokontszotar t1 (cost=0.00..1,374.43 rows=56 width=8) (actual time=0.073..3.342 rows=1,881 loops=1,241)

  • Filter: ((eifok IS NOT NULL) AND ((tipus)::text = 'Telj.'::text) AND ((elokontkod ~~ '05%'::text) OR (elokontkod ~~ '09%'::text)))
  • Rows Removed by Filter: 26151
45. 7.446 368,577.000 ↑ 155.0 4 1,241

Hash (cost=54,834.31..54,834.31 rows=620 width=4) (actual time=297.000..297.000 rows=4 loops=1,241)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
46. 430.627 368,569.554 ↑ 155.0 4 1,241

Unique (cost=54,820.12..54,828.11 rows=620 width=4) (actual time=296.270..296.994 rows=4 loops=1,241)

47. 1,384.956 368,138.927 ↓ 3.7 5,894 1,241

Sort (cost=54,820.12..54,824.11 rows=1,598 width=4) (actual time=296.268..296.647 rows=5,894 loops=1,241)

  • Sort Key: kb.fokonyv
  • Sort Method: quicksort Memory: 103kB
48. 366,753.971 366,753.971 ↓ 3.7 5,894 1,241

Seq Scan on kontir kb (cost=0.00..54,735.09 rows=1,598 width=4) (actual time=18.975..295.531 rows=5,894 loops=1,241)

  • Filter: ((datum >= '2018-01-01'::date) AND (datum <= '2018-12-31'::date) AND (NOT (konyvelve IS DISTINCT FROM true)) AND (COALESCE(ktkkod_id, '0'::bigint) = COALESCE(kontir_1.ktkkod_id, '0'::bigint)) AND (eikod = ANY ('{13,12,14,11}'::integer[])) AND (intkod = ANY ('{409854,576701,732923,803944}'::bigint[])))
  • Rows Removed by Filter: 1083009
49. 0.084 0.084 ↑ 1.0 1 6

Index Scan using ktkkod_pkey on ktkkod ktkkod_1 (cost=0.28..0.29 rows=1 width=12) (actual time=0.013..0.014 rows=1 loops=6)

  • Index Cond: (id = kontir_1.ktkkod_id)
Planning time : 10.004 ms
Execution time : 374,122.396 ms