explain.depesz.com

PostgreSQL's explain analyze made readable

Result: H7SH : Query

Settings
# exclusive inclusive rows x rows loops node
1. 42.958 206,658.235 ↓ 3.8 27,309 1

Unique (cost=483,063.36..483,849.64 rows=7,148 width=3,534) (actual time=206,597.098..206,658.235 rows=27,309 loops=1)

  • Buffers: shared hit=1,115,083 dirtied=3, temp read=1,981 written=1,990
  • Functions: 160
  • Options: Inlining false, Optimization false, Expressions true, Deforming true
  • Timing: Generation 37.971 ms, Inlining 0.000 ms, Optimization 16.263 ms, Emission 213.198 ms, Total 267.431 ms"Execution Time: 206,700.869 ms
2. 163.887 206,615.277 ↓ 3.8 27,309 1

Sort (cost=483,063.36..483,081.23 rows=7,148 width=3,534) (actual time=206,597.096..206,615.277 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".qty, "*SELECT* 1".c_project_id, "*SELECT* 1".c_projecttask_id, "*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: 10,296kB
  • Buffers: shared hit=1,115,083 dirtied=3, temp read=1,981 written=1,990
3. 14.188 206,451.390 ↓ 3.8 27,309 1

Append (cost=816.96..469,867.07 rows=7,148 width=3,534) (actual time=250.740..206,451.390 rows=27,309 loops=1)

  • Buffers: shared hit=1,115,083 dirtied=3
4. 10.179 43,877.221 ↓ 1.1 7,847 1

Subquery Scan on *SELECT* 1 (cost=816.96..244,191.75 rows=7,042 width=1,255) (actual time=250.738..43,877.221 rows=7,847 loops=1)

  • Buffers: shared hit=326,438 dirtied=3
5. 41,788.363 43,867.042 ↓ 1.1 7,847 1

Hash Left Join (cost=816.96..244,086.12 rows=7,042 width=1,199) (actual time=250.734..43,867.042 rows=7,847 loops=1)

  • Hash Cond: (f.user2_id = ce2.c_elementvalue_id)
  • Buffers: shared hit=326,438 dirtied=3
6. 8.387 953.737 ↓ 1.1 7,847 1

Hash Left Join (cost=549.14..171,196.80 rows=7,042 width=415) (actual time=238.476..953.737 rows=7,847 loops=1)

  • Hash Cond: (f.user1_id = ce1.c_elementvalue_id)
  • Buffers: shared hit=70,384 dirtied=3
7. 12.565 942.368 ↓ 1.1 7,847 1

Hash Left Join (cost=281.32..170,910.49 rows=7,042 width=381) (actual time=235.479..942.368 rows=7,847 loops=1)

  • Hash Cond: (f.ad_table_id = ad.ad_table_id)
  • Buffers: shared hit=70,253 dirtied=3
8. 23.247 929.052 ↓ 1.1 7,847 1

Nested Loop Left Join (cost=195.70..170,806.32 rows=7,042 width=348) (actual time=234.716..929.052 rows=7,847 loops=1)

  • Buffers: shared hit=70,196 dirtied=3
9. 10.015 874.417 ↓ 1.1 7,847 1

Hash Left Join (cost=195.41..168,284.90 rows=7,042 width=307) (actual time=233.264..874.417 rows=7,847 loops=1)

  • Hash Cond: (f.c_period_id = per.c_period_id)
  • Buffers: shared hit=54,155 dirtied=3
10. 5.898 864.042 ↓ 1.1 7,847 1

Nested Loop (cost=156.81..168,227.74 rows=7,042 width=299) (actual time=232.894..864.042 rows=7,847 loops=1)

  • Buffers: shared hit=54,138 dirtied=3
11. 231.692 231.692 ↑ 1.0 1 1

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

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

Hash Join (cost=156.81..168,156.25 rows=7,042 width=207) (actual time=1.197..626.452 rows=7,847 loops=1)

  • Hash Cond: (f.account_id = ce.c_elementvalue_id)
  • Buffers: shared hit=54,137 dirtied=3
13. 595.912 595.912 ↑ 8.6 7,847 1

Index Scan using fact_acct_table_record on fact_acct f (cost=0.43..167,821.88 rows=67,758 width=169) (actual time=0.118..595.912 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: 202,791
  • Buffers: shared hit=54,096 dirtied=3
14. 0.182 0.553 ↓ 1.0 633 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 59kB
  • Buffers: shared hit=41
15. 0.236 0.371 ↓ 1.0 633 1

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

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

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

  • Index Cond: (c_element_id = '1000001'::numeric)
  • Buffers: shared hit=8
17. 0.169 0.360 ↑ 1.2 770 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 44kB
  • Buffers: shared hit=17
18. 0.191 0.191 ↑ 1.2 770 1

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

  • Buffers: shared hit=17
19. 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=16,041
20. 0.314 0.751 ↓ 1.0 1,275 1

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

  • Buckets: 2,048 Batches: 1 Memory Usage: 106kB
  • Buffers: shared hit=57
21. 0.437 0.437 ↓ 1.0 1,275 1

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

  • Buffers: shared hit=57
22. 1.559 2.982 ↑ 1.0 6,081 1

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

  • Buckets: 8,192 Batches: 1 Memory Usage: 505kB
  • Buffers: shared hit=131
23. 1.423 1.423 ↑ 1.0 6,081 1

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

  • Buffers: shared hit=131
24. 1.514 2.821 ↑ 1.0 6,081 1

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

  • Buckets: 8,192 Batches: 1 Memory Usage: 505kB
  • Buffers: shared hit=131
25. 1.307 1.307 ↑ 1.0 6,081 1

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

  • Buffers: shared hit=131
26.          

SubPlan (for Hash Left Join)

27. 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=23,541
28. 149.093 149.093 ↑ 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.012..0.019 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=54,929
29. 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=47,082
30. 596.372 596.372 ↑ 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.076 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=94,164
31. 25.534 162,559.981 ↓ 183.6 19,462 1

Subquery Scan on *SELECT* 2 (cost=1.56..225,639.57 rows=106 width=1,199) (actual time=11.327..162,559.981 rows=19,462 loops=1)

  • Buffers: shared hit=788,645
32. 154,004.340 162,534.447 ↓ 183.6 19,462 1

Nested Loop Left Join (cost=1.56..225,638.25 rows=106 width=1,171) (actual time=11.323..162,534.447 rows=19,462 loops=1)

  • Join Filter: (f_1.c_period_id = per_1.c_period_id)
  • Rows Removed by Join Filter: 13,973,716
  • Buffers: shared hit=788,645
33. 50.204 4,812.865 ↓ 183.6 19,462 1

Nested Loop Left Join (cost=1.56..222,990.25 rows=106 width=441) (actual time=4.305..4,812.865 rows=19,462 loops=1)

  • Buffers: shared hit=156,023
34. 30.760 4,684.813 ↓ 183.6 19,462 1

Nested Loop Left Join (cost=1.28..222,835.73 rows=106 width=400) (actual time=4.288..4,684.813 rows=19,462 loops=1)

  • Buffers: shared hit=112,541
35. 40.837 4,654.053 ↓ 183.6 19,462 1

Nested Loop Left Join (cost=0.99..222,786.49 rows=106 width=366) (actual time=4.282..4,654.053 rows=19,462 loops=1)

  • Buffers: shared hit=112,541
36. 11.483 4,613.216 ↓ 183.6 19,462 1

Nested Loop (cost=0.71..222,737.25 rows=106 width=332) (actual time=4.276..4,613.216 rows=19,462 loops=1)

  • Buffers: shared hit=112,541
37. 0.047 0.047 ↑ 1.0 1 1

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

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

Nested Loop Left Join (cost=0.71..222,735.11 rows=106 width=240) (actual time=4.228..4,601.686 rows=19,462 loops=1)

  • Join Filter: (f_1.ad_table_id = ad_1.ad_table_id)
  • Rows Removed by Join Filter: 13,513,863
  • Buffers: shared hit=112,540
39. 82.845 1,349.470 ↓ 183.6 19,462 1

Nested Loop (cost=0.71..220,639.73 rows=106 width=207) (actual time=4.036..1,349.470 rows=19,462 loops=1)

  • Buffers: shared hit=112,485
40. 1,130.391 1,130.391 ↓ 19.2 19,462 1

Index Scan using fact_acct_table_record on fact_acct f_1 (cost=0.43..220,165.19 rows=1,016 width=169) (actual time=1.224..1,130.391 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: 191,176
  • Buffers: shared hit=54,099
41. 136.234 136.234 ↑ 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.007..0.007 rows=1 loops=19,462)

  • Index Cond: (c_elementvalue_id = f_1.account_id)
  • Filter: (c_element_id = '1000001'::numeric)
  • Buffers: shared hit=58,386
42. 914.270 914.714 ↑ 1.8 695 19,462

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

  • Buffers: shared hit=55
43. 0.444 0.444 ↑ 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.015..0.444 rows=1,239 loops=1)

  • Buffers: shared hit=55
44. 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)
45. 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)
46. 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=43,482
47. 895.107 895.252 ↑ 1.3 719 19,462

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

  • Buffers: shared hit=13
48. 0.145 0.145 ↑ 1.3 719 1

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

  • Buffers: shared hit=13
49.          

SubPlan (for Nested Loop Left Join)

50. 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=58,386
51. 369.778 369.778 ↑ 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.019 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=136,234
52. 661.708 661.708 ↑ 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.034 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=116,772
53. 1,498.574 1,498.574 ↑ 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.031..0.077 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=233,544