explain.depesz.com

PostgreSQL's explain analyze made readable

Result: gOII

Settings
# exclusive inclusive rows x rows loops node
1. 1.804 37,588.459 ↑ 8.0 519 1

Sort (cost=7,685,439.27..7,685,449.63 rows=4,146 width=350) (actual time=37,588.393..37,588.459 rows=519 loops=1)

  • Sort Key: ((SubPlan 1)), b.name, rvofb_ventas_general.dateinvoiced
  • Sort Method: quicksort Memory: 161kB
  • Buffers: shared hit=2672817, temp read=599 written=599
2. 2.077 37,586.655 ↑ 8.0 519 1

Hash Left Join (cost=7,675,060.91..7,685,190.15 rows=4,146 width=350) (actual time=37,262.671..37,586.655 rows=519 loops=1)

  • Hash Cond: (rvofb_ventas_general.salesrep_id = b.ad_user_id)
  • Filter: (NOT (hashed SubPlan 2))
  • Buffers: shared hit=2672817, temp read=599 written=599
3. 0.436 37,573.479 ↑ 16.0 519 1

Hash Left Join (cost=7,674,789.02..7,677,838.98 rows=8,291 width=301) (actual time=37,259.287..37,573.479 rows=519 loops=1)

  • Hash Cond: ((max(rvofb_ventas_general.c_uom_id)) = d.c_uom_id)
  • Filter: (NOT (hashed SubPlan 4))
  • Buffers: shared hit=2672166, temp read=599 written=599
4. 0.822 37,573.008 ↑ 31.9 519 1

Hash Left Join (cost=7,674,771.20..7,677,714.41 rows=16,582 width=263) (actual time=37,259.238..37,573.008 rows=519 loops=1)

  • Hash Cond: (rvofb_ventas_general.m_product_id = c.m_product_id)
  • Filter: (NOT (hashed SubPlan 3))
  • Buffers: shared hit=2672164, temp read=599 written=599
5. 279.319 37,568.904 ↑ 63.9 519 1

GroupAggregate (cost=7,674,463.64..7,676,536.33 rows=33,163 width=439) (actual time=37,255.920..37,568.904 rows=519 loops=1)

  • Group Key: rvofb_ventas_general.m_product_id, rvofb_ventas_general.salesrep_id, rvofb_ventas_general.value, rvofb_ventas_general.name, rvofb_ventas_general.dateinvoiced, rvofb_v
  • Filter: ((max(rvofb_ventas_general.ad_client_id) = ANY ('{0,1000000}'::numeric[])) AND (trunc((rvofb_ventas_general.dateinvoiced)::timestamp with time zone) >= to_timestamp('201
  • Rows Removed by Filter: 38476
  • Buffers: shared hit=2671998, temp read=599 written=599
6. 298.018 37,289.585 ↓ 1.2 39,192 1

Sort (cost=7,674,463.64..7,674,546.55 rows=33,163 width=166) (actual time=37,255.160..37,289.585 rows=39,192 loops=1)

  • Sort Key: rvofb_ventas_general.m_product_id, rvofb_ventas_general.salesrep_id, rvofb_ventas_general.value, rvofb_ventas_general.name, rvofb_ventas_general.dateinvoiced, rv
  • Sort Method: external merge Disk: 4784kB
  • Buffers: shared hit=2671998, temp read=599 written=599
7. 15.920 36,991.567 ↓ 1.2 39,192 1

Subquery Scan on rvofb_ventas_general (cost=6,655.14..7,669,250.55 rows=33,163 width=166) (actual time=112.430..36,991.567 rows=39,192 loops=1)

  • Buffers: shared hit=2671998
8. 996.729 36,975.647 ↓ 1.2 39,192 1

Hash Join (cost=6,655.14..7,668,918.92 rows=33,163 width=500) (actual time=112.428..36,975.647 rows=39,192 loops=1)

  • Hash Cond: (cil.c_tax_id = ct.c_tax_id)
  • Buffers: shared hit=2671998
9. 39.413 256.784 ↓ 1.2 39,192 1

Hash Left Join (cost=6,653.80..9,964.22 rows=33,163 width=122) (actual time=109.084..256.784 rows=39,192 loops=1)

  • Hash Cond: (cil.m_product_id = mp.m_product_id)
  • Buffers: shared hit=5639
10. 89.300 210.327 ↓ 1.2 39,192 1

Hash Join (cost=6,282.71..9,224.98 rows=33,163 width=61) (actual time=102.012..210.327 rows=39,192 loops=1)

  • Hash Cond: (cil.c_invoice_id = ci.c_invoice_id)
  • Buffers: shared hit=5473
11. 19.062 19.062 ↑ 1.0 65,937 1

Seq Scan on c_invoiceline cil (cost=0.00..2,363.37 rows=65,937 width=27) (actual time=0.004..19.062 rows=65,937 loops=1)

  • Buffers: shared hit=1704
12. 8.603 101.965 ↓ 1.0 17,781 1

Hash (cost=6,063.19..6,063.19 rows=17,562 width=46) (actual time=101.965..101.965 rows=17,781 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 1716kB
  • Buffers: shared hit=3769
13. 12.671 93.362 ↓ 1.0 17,781 1

Hash Left Join (cost=2,542.93..6,063.19 rows=17,562 width=46) (actual time=27.871..93.362 rows=17,781 loops=1)

  • Hash Cond: (ci.c_order_id = co.c_order_id)
  • Buffers: shared hit=3769
14. 8.259 55.867 ↓ 1.0 17,781 1

Hash Join (cost=355.30..3,714.21 rows=17,562 width=46) (actual time=2.997..55.867 rows=17,781 loops=1)

  • Hash Cond: (ci.c_doctype_id = cdt.c_doctype_id)
  • Buffers: shared hit=2224
15. 13.723 47.513 ↓ 1.0 17,781 1

Hash Join (cost=348.20..3,465.62 rows=17,562 width=48) (actual time=2.897..47.513 rows=17,781 loops=1)

  • Hash Cond: (ci.c_bpartner_id = cbp.c_bpartner_id)
  • Buffers: shared hit=2220
16. 30.920 30.920 ↓ 1.0 17,781 1

Seq Scan on c_invoice ci (cost=0.00..2,875.95 rows=17,562 width=54) (actual time=0.010..30.920 rows=17,781 loops=1)

  • Filter: ((c_doctype_id <> '1000051'::numeric) AND (c_doctype_id <> '1000079'::numeric) AND (c_doctype_id <> '1000050'
  • Rows Removed by Filter: 17120
  • Buffers: shared hit=2003
17. 1.605 2.870 ↑ 1.0 5,831 1

Hash (cost=275.31..275.31 rows=5,831 width=6) (actual time=2.869..2.870 rows=5,831 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 287kB
  • Buffers: shared hit=217
18. 1.265 1.265 ↑ 1.0 5,831 1

Seq Scan on c_bpartner cbp (cost=0.00..275.31 rows=5,831 width=6) (actual time=0.004..1.265 rows=5,831 loops=1)

  • Buffers: shared hit=217
19. 0.062 0.095 ↑ 1.0 138 1

Hash (cost=5.38..5.38 rows=138 width=10) (actual time=0.095..0.095 rows=138 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 14kB
  • Buffers: shared hit=4
20. 0.033 0.033 ↑ 1.0 138 1

Seq Scan on c_doctype cdt (cost=0.00..5.38 rows=138 width=10) (actual time=0.005..0.033 rows=138 loops=1)

  • Buffers: shared hit=4
21. 8.751 24.824 ↑ 1.0 28,561 1

Hash (cost=1,830.61..1,830.61 rows=28,561 width=12) (actual time=24.823..24.824 rows=28,561 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 1539kB
  • Buffers: shared hit=1545
22. 16.073 16.073 ↑ 1.0 28,561 1

Seq Scan on c_order co (cost=0.00..1,830.61 rows=28,561 width=12) (actual time=0.007..16.073 rows=28,561 loops=1)

  • Buffers: shared hit=1545
23. 2.285 7.044 ↑ 1.0 5,631 1

Hash (cost=300.70..300.70 rows=5,631 width=67) (actual time=7.044..7.044 rows=5,631 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 561kB
  • Buffers: shared hit=166
24. 3.801 4.759 ↑ 1.0 5,631 1

Hash Left Join (cost=1.97..300.70 rows=5,631 width=67) (actual time=0.051..4.759 rows=5,631 loops=1)

  • Hash Cond: (mp.c_uom_id = cu.c_uom_id)
  • Buffers: shared hit=166
25. 0.932 0.932 ↑ 1.0 5,631 1

Seq Scan on m_product mp (cost=0.00..221.31 rows=5,631 width=56) (actual time=0.013..0.932 rows=5,631 loops=1)

  • Buffers: shared hit=165
26. 0.013 0.026 ↑ 1.0 43 1

Hash (cost=1.43..1.43 rows=43 width=16) (actual time=0.026..0.026 rows=43 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
  • Buffers: shared hit=1
27. 0.013 0.013 ↑ 1.0 43 1

Seq Scan on c_uom cu (cost=0.00..1.43 rows=43 width=16) (actual time=0.006..0.013 rows=43 loops=1)

  • Buffers: shared hit=1
28. 0.009 0.030 ↑ 1.0 15 1

Hash (cost=1.15..1.15 rows=15 width=48) (actual time=0.030..0.030 rows=15 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=1
29. 0.021 0.021 ↑ 1.0 15 1

Seq Scan on c_tax ct (cost=0.00..1.15 rows=15 width=48) (actual time=0.014..0.021 rows=15 loops=1)

  • Buffers: shared hit=1
30.          

SubPlan (for Hash Join)

31. 8.400 1,255.800 ↑ 1.0 1 1,400

Aggregate (cost=114.69..114.70 rows=1 width=32) (actual time=0.897..0.897 rows=1 loops=1,400)

  • Buffers: shared hit=95200
32. 1,247.400 1,247.400 ↑ 1.0 1 1,400

Seq Scan on m_cost mc (cost=0.00..114.69 rows=1 width=5) (actual time=0.791..0.891 rows=1 loops=1,400)

  • Filter: (mp.m_product_id = m_product_id)
  • Rows Removed by Filter: 3679
  • Buffers: shared hit=95200
33. 188.960 34,466.304 ↑ 1.0 1 37,792

Aggregate (cost=114.69..114.70 rows=1 width=32) (actual time=0.912..0.912 rows=1 loops=37,792)

  • Buffers: shared hit=2569856
34. 34,277.344 34,277.344 ↑ 1.0 1 37,792

Seq Scan on m_cost mc_1 (cost=0.00..114.69 rows=1 width=5) (actual time=0.805..0.907 rows=1 loops=37,792)

  • Filter: (mp.m_product_id = m_product_id)
  • Rows Removed by Filter: 3679
  • Buffers: shared hit=2569856
35. 1.632 3.277 ↑ 1.0 5,631 1

Hash (cost=221.31..221.31 rows=5,631 width=19) (actual time=3.277..3.277 rows=5,631 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 352kB
  • Buffers: shared hit=165
36. 1.645 1.645 ↑ 1.0 5,631 1

Seq Scan on m_product c (cost=0.00..221.31 rows=5,631 width=19) (actual time=0.019..1.645 rows=5,631 loops=1)

  • Buffers: shared hit=165
37.          

SubPlan (for Hash Left Join)

38. 0.005 0.005 ↓ 0.0 0 1

Index Scan using ad_private_access_pkey on ad_private_access ad_private_access_1 (cost=0.15..15.85 rows=1 width=16) (actual time=0.005..0.005 rows=0 loops=1)

  • Index Cond: (ad_table_id = '208'::numeric)
  • Filter: ((ad_user_id <> '1000307'::numeric) AND (isactive = 'Y'::bpchar))
  • Buffers: shared hit=1
39. 0.014 0.033 ↑ 1.0 43 1

Hash (cost=1.43..1.43 rows=43 width=54) (actual time=0.033..0.033 rows=43 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
  • Buffers: shared hit=1
40. 0.019 0.019 ↑ 1.0 43 1

Seq Scan on c_uom d (cost=0.00..1.43 rows=43 width=54) (actual time=0.009..0.019 rows=43 loops=1)

  • Buffers: shared hit=1
41.          

SubPlan (for Hash Left Join)

42. 0.002 0.002 ↓ 0.0 0 1

Index Scan using ad_private_access_pkey on ad_private_access ad_private_access_2 (cost=0.15..15.85 rows=1 width=16) (actual time=0.002..0.002 rows=0 loops=1)

  • Index Cond: (ad_table_id = '146'::numeric)
  • Filter: ((ad_user_id <> '1000307'::numeric) AND (isactive = 'Y'::bpchar))
  • Buffers: shared hit=1
43. 1.794 3.311 ↑ 1.0 5,557 1

Hash (cost=186.57..186.57 rows=5,557 width=23) (actual time=3.311..3.311 rows=5,557 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 369kB
  • Buffers: shared hit=131
44. 1.517 1.517 ↑ 1.0 5,557 1

Seq Scan on ad_user b (cost=0.00..186.57 rows=5,557 width=23) (actual time=0.008..1.517 rows=5,557 loops=1)

  • Buffers: shared hit=131
45.          

SubPlan (for Hash Left Join)

46. 7.785 7.785 ↑ 1.0 1 519

Seq Scan on ad_org (cost=0.00..1.68 rows=1 width=32) (actual time=0.013..0.015 rows=1 loops=519)

  • Filter: ((max(rvofb_ventas_general.ad_org_id)) = ad_org_id)
  • Rows Removed by Filter: 53
  • Buffers: shared hit=519
47. 0.003 0.003 ↓ 0.0 0 1

Index Scan using ad_private_access_pkey on ad_private_access (cost=0.15..15.85 rows=1 width=16) (actual time=0.003..0.003 rows=0 loops=1)

  • Index Cond: (ad_table_id = '114'::numeric)
  • Filter: ((ad_user_id <> '1000307'::numeric) AND (isactive = 'Y'::bpchar))
  • Buffers: shared hit=1
Planning time : 5.730 ms
Execution time : 37,589.619 ms