explain.depesz.com

PostgreSQL's explain analyze made readable

Result: zMJB : Query

Settings
# exclusive inclusive rows x rows loops node
1. 48.167 203,148.302 ↓ 3.8 27,309 1

Unique (cost=490,717.24..491,592.87 rows=7,148 width=4,940) (actual time=203,075.073..203,148.302 rows=27,309 loops=1)

  • Buffers: shared hit=1153367 dirtied=2, temp read=2059 written=2068
  • Functions: 197
  • Options: Inlining false, Optimization false, Expressions true, Deforming true
  • Timing: Generation 49.326 ms, Inlining 0.000 ms, Optimization 16.486 ms, Emission 226.614 ms, Total 292.425 ms
2. 178.459 203,100.135 ↓ 3.8 27,309 1

Sort (cost=490,717.24..490,735.11 rows=7,148 width=4,940) (actual time=203,075.070..203,100.135 rows=27,309 loops=1)

  • Sort Key: "*SELECT* 1".fact_acct_id, "*SELECT* 1".saldosa, ((0)::numeric), ((0)::numeric), "*SELECT* 1".saldof, "*SELECT* 1".documentno, "*SELECT* 1".record_id, "*SELECT* 1".c_period_id, "*SELECT* 1".name1, "*SELECT* 1".dateacct, "*SELECT* 1".amtacctdr, "*SELECT* 1".amtacctcr, "*SELECT* 1".saldos, "*SELECT* 1".description1, "*SELECT* 1".account_id, "*SELECT* 1".gr1, "*SELECT* 1".name9, "*SELECT* 1".gr2, "*SELECT* 1".name10, "*SELECT* 1".gr3, "*SELECT* 1".name11, "*SELECT* 1".gr4, "*SELECT* 1".name12, "*SELECT* 1".value0, "*SELECT* 1".name13, "*SELECT* 1".description2, "*SELECT* 1".c_bpartner_id, "*SELECT* 1".value1, "*SELECT* 1".name2, "*SELECT* 1".ad_table_id, "*SELECT* 1".tablename, "*SELECT* 1".name3, "*SELECT* 1".m_product_id, "*SELECT* 1".value2, "*SELECT* 1".name4, "*SELECT* 1".qty, "*SELECT* 1".c_project_id, "*SELECT* 1".value3, "*SELECT* 1".name5, "*SELECT* 1".c_projecttask_id, "*SELECT* 1".name6, "*SELECT* 1".user1_id, "*SELECT* 1".value4, "*SELECT* 1".name7, "*SELECT* 1".user2_id, "*SELECT* 1".value5, "*SELECT* 1".name8, "*SELECT* 1".empre99
  • Sort Method: external merge Disk: 10736kB
  • Buffers: shared hit=1153367 dirtied=2, temp read=2059 written=2068
3. 12.667 202,921.676 ↓ 3.8 27,309 1

Append (cost=983.59..472,486.15 rows=7,148 width=4,940) (actual time=265.593..202,921.676 rows=27,309 loops=1)

  • Buffers: shared hit=1153367 dirtied=2
4. 10.040 42,704.442 ↓ 1.1 7,847 1

Subquery Scan on *SELECT* 1 (cost=983.59..246,541.95 rows=7,042 width=1,328) (actual time=265.591..42,704.442 rows=7,847 loops=1)

  • Buffers: shared hit=339378 dirtied=2
5. 40,585.900 42,694.402 ↓ 1.1 7,847 1

Hash Left Join (cost=983.59..246,436.32 rows=7,042 width=1,272) (actual time=265.587..42,694.402 rows=7,847 loops=1)

  • Hash Cond: (f.c_projecttask_id = cpt.c_projecttask_id)
  • Buffers: shared hit=339378 dirtied=2
6. 8.302 1,009.865 ↓ 1.1 7,847 1

Hash Left Join (cost=979.36..173,810.59 rows=7,042 width=505) (actual time=259.203..1,009.865 rows=7,847 loops=1)

  • Hash Cond: (f.c_project_id = cp.c_project_id)
  • Buffers: shared hit=83453 dirtied=2
7. 16.917 997.980 ↓ 1.1 7,847 1

Nested Loop Left Join (cost=817.24..173,629.99 rows=7,042 width=488) (actual time=255.609..997.980 rows=7,847 loops=1)

  • Buffers: shared hit=83356 dirtied=2
8. 7.132 957.522 ↓ 1.1 7,847 1

Hash Left Join (cost=816.96..171,477.14 rows=7,042 width=449) (actual time=255.603..957.522 rows=7,847 loops=1)

  • Hash Cond: (f.user2_id = ce2.c_elementvalue_id)
  • Buffers: shared hit=70510 dirtied=2
9. 7.460 947.848 ↓ 1.1 7,847 1

Hash Left Join (cost=549.14..171,190.84 rows=7,042 width=415) (actual time=253.044..947.848 rows=7,847 loops=1)

  • Hash Cond: (f.user1_id = ce1.c_elementvalue_id)
  • Buffers: shared hit=70379 dirtied=2
10. 11.576 937.589 ↓ 1.1 7,847 1

Hash Left Join (cost=281.32..170,904.53 rows=7,042 width=381) (actual time=250.233..937.589 rows=7,847 loops=1)

  • Hash Cond: (f.ad_table_id = ad.ad_table_id)
  • Buffers: shared hit=70248 dirtied=2
11. 19.479 925.333 ↓ 1.1 7,847 1

Nested Loop Left Join (cost=195.70..170,800.36 rows=7,042 width=348) (actual time=249.544..925.333 rows=7,847 loops=1)

  • Buffers: shared hit=70191 dirtied=2
12. 9.854 874.466 ↓ 1.1 7,847 1

Hash Left Join (cost=195.41..168,278.93 rows=7,042 width=307) (actual time=249.526..874.466 rows=7,847 loops=1)

  • Hash Cond: (f.c_period_id = per.c_period_id)
  • Buffers: shared hit=54150 dirtied=2
13. 5.865 864.283 ↓ 1.1 7,847 1

Nested Loop (cost=156.81..168,221.77 rows=7,042 width=299) (actual time=249.189..864.283 rows=7,847 loops=1)

  • Buffers: shared hit=54133 dirtied=2
14. 245.720 245.720 ↑ 1.0 1 1

Seq Scan on ad_client cl (cost=0.00..1.07 rows=1 width=114) (actual time=245.716..245.720 rows=1 loops=1)

  • Filter: (ad_client_id = '1000001'::numeric)
  • Rows Removed by Filter: 5
  • Buffers: shared hit=1
15. 27.280 612.698 ↓ 1.1 7,847 1

Hash Join (cost=156.81..168,150.27 rows=7,042 width=207) (actual time=3.466..612.698 rows=7,847 loops=1)

  • Hash Cond: (f.account_id = ce.c_elementvalue_id)
  • Buffers: shared hit=54132 dirtied=2
16. 584.832 584.832 ↑ 8.6 7,847 1

Index Scan using fact_acct_table_record on fact_acct f (cost=0.43..167,815.91 rows=67,756 width=169) (actual time=2.864..584.832 rows=7,847 loops=1)

  • Index Cond: (ad_client_id = '1000001'::numeric)
  • Filter: ((isactive = 'Y'::bpchar) AND (trunc((dateacct)::timestamp with time zone) < to_date('2019-01-01'::text, 'YYYY-MM-DD'::text)))
  • Rows Removed by Filter: 202766
  • Buffers: shared hit=54091 dirtied=2
17. 0.175 0.586 ↓ 1.0 633 1

Hash (cost=148.48..148.48 rows=632 width=44) (actual time=0.585..0.586 rows=633 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 59kB
  • Buffers: shared hit=41
18. 0.265 0.411 ↓ 1.0 633 1

Bitmap Heap Scan on c_elementvalue ce (cost=9.58..148.48 rows=632 width=44) (actual time=0.163..0.411 rows=633 loops=1)

  • Recheck Cond: (c_element_id = '1000001'::numeric)
  • Heap Blocks: exact=20
  • Buffers: shared hit=41
19. 0.146 0.146 ↓ 1.8 1,153 1

Bitmap Index Scan on c_elementvalue_value (cost=0.00..9.42 rows=632 width=0) (actual time=0.146..0.146 rows=1,153 loops=1)

  • Index Cond: (c_element_id = '1000001'::numeric)
  • Buffers: shared hit=8
20. 0.156 0.329 ↑ 1.2 770 1

Hash (cost=26.60..26.60 rows=960 width=14) (actual time=0.329..0.329 rows=770 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 44kB
  • Buffers: shared hit=17
21. 0.173 0.173 ↑ 1.2 770 1

Seq Scan on c_period per (cost=0.00..26.60 rows=960 width=14) (actual time=0.013..0.173 rows=770 loops=1)

  • Buffers: shared hit=17
22. 31.388 31.388 ↑ 1.0 1 7,847

Index Scan using c_bpartner_pkey on c_bpartner bp (cost=0.29..0.36 rows=1 width=47) (actual time=0.004..0.004 rows=1 loops=7,847)

  • Index Cond: (c_bpartner_id = f.c_bpartner_id)
  • Buffers: shared hit=16041
23. 0.276 0.680 ↓ 1.0 1,275 1

Hash (cost=69.72..69.72 rows=1,272 width=38) (actual time=0.680..0.680 rows=1,275 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 106kB
  • Buffers: shared hit=57
24. 0.404 0.404 ↓ 1.0 1,275 1

Seq Scan on ad_table ad (cost=0.00..69.72 rows=1,272 width=38) (actual time=0.007..0.404 rows=1,275 loops=1)

  • Buffers: shared hit=57
25. 1.387 2.799 ↑ 1.0 6,081 1

Hash (cost=191.81..191.81 rows=6,081 width=40) (actual time=2.799..2.799 rows=6,081 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 505kB
  • Buffers: shared hit=131
26. 1.412 1.412 ↑ 1.0 6,081 1

Seq Scan on c_elementvalue ce1 (cost=0.00..191.81 rows=6,081 width=40) (actual time=0.005..1.412 rows=6,081 loops=1)

  • Buffers: shared hit=131
27. 1.333 2.542 ↑ 1.0 6,081 1

Hash (cost=191.81..191.81 rows=6,081 width=40) (actual time=2.542..2.542 rows=6,081 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 505kB
  • Buffers: shared hit=131
28. 1.209 1.209 ↑ 1.0 6,081 1

Seq Scan on c_elementvalue ce2 (cost=0.00..191.81 rows=6,081 width=40) (actual time=0.005..1.209 rows=6,081 loops=1)

  • Buffers: shared hit=131
29. 23.541 23.541 ↑ 1.0 1 7,847

Index Scan using m_product_pkey on m_product mp (cost=0.28..0.31 rows=1 width=45) (actual time=0.003..0.003 rows=1 loops=7,847)

  • Index Cond: (m_product_id = f.m_product_id)
  • Buffers: shared hit=12846
30. 0.685 3.583 ↑ 1.0 2,762 1

Hash (cost=125.94..125.94 rows=2,894 width=23) (actual time=3.583..3.583 rows=2,762 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 186kB
  • Buffers: shared hit=97
31. 2.898 2.898 ↑ 1.0 2,762 1

Seq Scan on c_project cp (cost=0.00..125.94 rows=2,894 width=23) (actual time=2.041..2.898 rows=2,762 loops=1)

  • Buffers: shared hit=97
32. 0.024 0.057 ↑ 1.0 99 1

Hash (cost=2.99..2.99 rows=99 width=23) (actual time=0.057..0.057 rows=99 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 14kB
  • Buffers: shared hit=2
33. 0.033 0.033 ↑ 1.0 99 1

Seq Scan on c_projecttask cpt (cost=0.00..2.99 rows=99 width=23) (actual time=0.011..0.033 rows=99 loops=1)

  • Buffers: shared hit=2
34.          

SubPlan (for Hash Left Join)

35. 109.858 109.858 ↑ 1.0 1 7,847

Index Scan using c_elementvalue_description on c_elementvalue ev (cost=0.28..2.51 rows=1 width=25) (actual time=0.010..0.014 rows=1 loops=7,847)

  • Index Cond: ((description)::text = '1'::text)
  • Filter: ((c_element_id = '1000001'::numeric) AND ("substring"((value)::text, 1, 2) = "substring"((ce.value)::text, 1, 2)))
  • Rows Removed by Filter: 7
  • Buffers: shared hit=23541
36. 141.246 141.246 ↑ 1.0 1 7,847

Index Scan using c_elementvalue_description on c_elementvalue ev_1 (cost=0.28..2.51 rows=1 width=25) (actual time=0.011..0.018 rows=1 loops=7,847)

  • Index Cond: ((description)::text = '2'::text)
  • Filter: ((c_element_id = '1000001'::numeric) AND ("substring"((value)::text, 1, 4) = "substring"((ce.value)::text, 1, 4)))
  • Rows Removed by Filter: 16
  • Buffers: shared hit=54929
37. 266.798 266.798 ↑ 1.0 1 7,847

Index Scan using c_elementvalue_description on c_elementvalue ev_2 (cost=0.28..2.51 rows=1 width=25) (actual time=0.010..0.034 rows=1 loops=7,847)

  • Index Cond: ((description)::text = '3'::text)
  • Filter: ((c_element_id = '1000001'::numeric) AND ("substring"((value)::text, 1, 7) = "substring"((ce.value)::text, 1, 7)))
  • Rows Removed by Filter: 42
  • Buffers: shared hit=47082
38. 580.678 580.678 ↑ 1.0 1 7,847

Index Scan using c_elementvalue_description on c_elementvalue ev_3 (cost=0.28..2.51 rows=1 width=25) (actual time=0.041..0.074 rows=1 loops=7,847)

  • Index Cond: ((description)::text = '4'::text)
  • Filter: ((c_element_id = '1000001'::numeric) AND ("substring"((value)::text, 1, 10) = "substring"((ce.value)::text, 1, 10)))
  • Rows Removed by Filter: 100
  • Buffers: shared hit=94164
39. 22.840 160,204.567 ↓ 183.6 19,462 1

Subquery Scan on *SELECT* 2 (cost=2.12..225,908.46 rows=106 width=1,272) (actual time=12.208..160,204.567 rows=19,462 loops=1)

  • Buffers: shared hit=813989
40. 149,538.581 160,181.727 ↓ 183.6 19,462 1

Nested Loop Left Join (cost=2.12..225,907.14 rows=106 width=1,244) (actual time=12.204..160,181.727 rows=19,462 loops=1)

  • Join Filter: (f_1.c_projecttask_id = cpt_1.c_projecttask_id)
  • Rows Removed by Join Filter: 1926738
  • Buffers: shared hit=813989
41. 33.914 7,782.232 ↓ 183.6 19,462 1

Nested Loop Left Join (cost=2.12..224,653.90 rows=106 width=505) (actual time=4.733..7,782.232 rows=19,462 loops=1)

  • Buffers: shared hit=181378
42. 27.754 7,748.318 ↓ 183.6 19,462 1

Nested Loop Left Join (cost=1.84..224,610.08 rows=106 width=488) (actual time=4.712..7,748.318 rows=19,462 loops=1)

  • Buffers: shared hit=180919
43. 2,272.831 7,662.178 ↓ 183.6 19,462 1

Nested Loop Left Join (cost=1.56..224,537.88 rows=106 width=449) (actual time=4.703..7,662.178 rows=19,462 loops=1)

  • Join Filter: (f_1.c_period_id = per_1.c_period_id)
  • Rows Removed by Join Filter: 13973716
  • Buffers: shared hit=156028
44. 41.599 4,591.405 ↓ 183.6 19,462 1

Nested Loop Left Join (cost=1.56..222,982.48 rows=106 width=441) (actual time=2.078..4,591.405 rows=19,462 loops=1)

  • Buffers: shared hit=156015
45. 29.988 4,471.958 ↓ 183.6 19,462 1

Nested Loop Left Join (cost=1.28..222,827.97 rows=106 width=400) (actual time=1.482..4,471.958 rows=19,462 loops=1)

  • Buffers: shared hit=112533
46. 41.827 4,441.970 ↓ 183.6 19,462 1

Nested Loop Left Join (cost=0.99..222,778.72 rows=106 width=366) (actual time=1.474..4,441.970 rows=19,462 loops=1)

  • Buffers: shared hit=112533
47. 13.711 4,400.143 ↓ 183.6 19,462 1

Nested Loop (cost=0.71..222,729.48 rows=106 width=332) (actual time=1.467..4,400.143 rows=19,462 loops=1)

  • Buffers: shared hit=112533
48. 0.049 0.049 ↑ 1.0 1 1

Seq Scan on ad_client cl_1 (cost=0.00..1.07 rows=1 width=114) (actual time=0.045..0.049 rows=1 loops=1)

  • Filter: (ad_client_id = '1000001'::numeric)
  • Rows Removed by Filter: 5
  • Buffers: shared hit=1
49. 2,337.458 4,386.383 ↓ 183.6 19,462 1

Nested Loop Left Join (cost=0.71..222,727.34 rows=106 width=240) (actual time=1.417..4,386.383 rows=19,462 loops=1)

  • Join Filter: (f_1.ad_table_id = ad_1.ad_table_id)
  • Rows Removed by Join Filter: 13513863
  • Buffers: shared hit=112532
50. 78.841 1,250.983 ↓ 183.6 19,462 1

Nested Loop (cost=0.71..220,631.96 rows=106 width=207) (actual time=1.234..1,250.983 rows=19,462 loops=1)

  • Buffers: shared hit=112477
51. 1,055.370 1,055.370 ↓ 19.2 19,462 1

Index Scan using fact_acct_table_record on fact_acct f_1 (cost=0.43..220,157.42 rows=1,016 width=169) (actual time=1.213..1,055.370 rows=19,462 loops=1)

  • Index Cond: (ad_client_id = '1000001'::numeric)
  • Filter: ((isactive = 'Y'::bpchar) AND (trunc((dateacct)::timestamp with time zone) >= to_date('2019-01-01'::text, 'YYYY-MM-DD'::text)) AND (trunc((dateacct)::timestamp with time zone) <= to_date('2019-01-31'::text, 'YYYY-MM-DD'::text)))
  • Rows Removed by Filter: 191151
  • Buffers: shared hit=54091
52. 116.772 116.772 ↑ 1.0 1 19,462

Index Scan using c_elementvalue_pkey on c_elementvalue ce_1 (cost=0.28..0.47 rows=1 width=44) (actual time=0.006..0.006 rows=1 loops=19,462)

  • Index Cond: (c_elementvalue_id = f_1.account_id)
  • Filter: (c_element_id = '1000001'::numeric)
  • Buffers: shared hit=58386
53. 797.516 797.942 ↑ 1.8 695 19,462

Materialize (cost=0.00..76.08 rows=1,272 width=38) (actual time=0.000..0.041 rows=695 loops=19,462)

  • Buffers: shared hit=55
54. 0.426 0.426 ↑ 1.0 1,239 1

Seq Scan on ad_table ad_1 (cost=0.00..69.72 rows=1,272 width=38) (actual time=0.011..0.426 rows=1,239 loops=1)

  • Buffers: shared hit=55
55. 0.000 0.000 ↓ 0.0 0 19,462

Index Scan using c_elementvalue_pkey on c_elementvalue ce1_1 (cost=0.28..0.46 rows=1 width=40) (actual time=0.000..0.000 rows=0 loops=19,462)

  • Index Cond: (c_elementvalue_id = f_1.user1_id)
56. 0.000 0.000 ↓ 0.0 0 19,462

Index Scan using c_elementvalue_pkey on c_elementvalue ce2_1 (cost=0.28..0.46 rows=1 width=40) (actual time=0.000..0.000 rows=0 loops=19,462)

  • Index Cond: (c_elementvalue_id = f_1.user2_id)
57. 77.848 77.848 ↑ 1.0 1 19,462

Index Scan using c_bpartner_pkey on c_bpartner bp_1 (cost=0.29..1.46 rows=1 width=47) (actual time=0.004..0.004 rows=1 loops=19,462)

  • Index Cond: (c_bpartner_id = f_1.c_bpartner_id)
  • Buffers: shared hit=43482
58. 797.764 797.942 ↑ 1.3 719 19,462

Materialize (cost=0.00..31.40 rows=960 width=14) (actual time=0.000..0.041 rows=719 loops=19,462)

  • Buffers: shared hit=13
59. 0.178 0.178 ↑ 1.3 719 1

Seq Scan on c_period per_1 (cost=0.00..26.60 rows=960 width=14) (actual time=0.015..0.178 rows=719 loops=1)

  • Buffers: shared hit=13
60. 58.386 58.386 ↓ 0.0 0 19,462

Index Scan using m_product_pkey on m_product mp_1 (cost=0.28..0.68 rows=1 width=45) (actual time=0.003..0.003 rows=0 loops=19,462)

  • Index Cond: (m_product_id = f_1.m_product_id)
  • Buffers: shared hit=24891
61. 0.000 0.000 ↓ 0.0 0 19,462

Index Scan using c_project_pkey on c_project cp_1 (cost=0.28..0.41 rows=1 width=23) (actual time=0.000..0.000 rows=0 loops=19,462)

  • Index Cond: (c_project_id = f_1.c_project_id)
  • Buffers: shared hit=459
62. 116.536 116.772 ↑ 1.0 99 19,462

Materialize (cost=0.00..3.49 rows=99 width=23) (actual time=0.000..0.006 rows=99 loops=19,462)

  • Buffers: shared hit=2
63. 0.236 0.236 ↑ 1.0 99 1

Seq Scan on c_projecttask cpt_1 (cost=0.00..2.99 rows=99 width=23) (actual time=0.213..0.236 rows=99 loops=1)

  • Buffers: shared hit=2
64.          

SubPlan (for Nested Loop Left Join)

65. 291.930 291.930 ↑ 1.0 1 19,462

Index Scan using c_elementvalue_description on c_elementvalue ev_4 (cost=0.28..2.51 rows=1 width=25) (actual time=0.011..0.015 rows=1 loops=19,462)

  • Index Cond: ((description)::text = '1'::text)
  • Filter: ((c_element_id = '1000001'::numeric) AND ("substring"((value)::text, 1, 2) = "substring"((ce_1.value)::text, 1, 2)))
  • Rows Removed by Filter: 7
  • Buffers: shared hit=58386
66. 350.316 350.316 ↑ 1.0 1 19,462

Index Scan using c_elementvalue_description on c_elementvalue ev_5 (cost=0.28..2.51 rows=1 width=25) (actual time=0.011..0.018 rows=1 loops=19,462)

  • Index Cond: ((description)::text = '2'::text)
  • Filter: ((c_element_id = '1000001'::numeric) AND ("substring"((value)::text, 1, 4) = "substring"((ce_1.value)::text, 1, 4)))
  • Rows Removed by Filter: 16
  • Buffers: shared hit=136234
67. 642.246 642.246 ↑ 1.0 1 19,462

Index Scan using c_elementvalue_description on c_elementvalue ev_6 (cost=0.28..2.51 rows=1 width=25) (actual time=0.013..0.033 rows=1 loops=19,462)

  • Index Cond: ((description)::text = '3'::text)
  • Filter: ((c_element_id = '1000001'::numeric) AND ("substring"((value)::text, 1, 7) = "substring"((ce_1.value)::text, 1, 7)))
  • Rows Removed by Filter: 42
  • Buffers: shared hit=116772
68. 1,459.650 1,459.650 ↑ 1.0 1 19,462

Index Scan using c_elementvalue_description on c_elementvalue ev_7 (cost=0.28..2.51 rows=1 width=25) (actual time=0.030..0.075 rows=1 loops=19,462)

  • Index Cond: ((description)::text = '4'::text)
  • Filter: ((c_element_id = '1000001'::numeric) AND ("substring"((value)::text, 1, 10) = "substring"((ce_1.value)::text, 1, 10)))
  • Rows Removed by Filter: 100
  • Buffers: shared hit=233544
Planning time : 13.711 ms