explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 2fo4

Settings
# exclusive inclusive rows x rows loops node
1. 0.133 7,210.216 ↓ 50.2 301 1

Limit (cost=31,154.27..36,890.31 rows=6 width=313) (actual time=5,514.469..7,210.216 rows=301 loops=1)

  • Buffers: shared hit=212269 read=27454
2. 2.372 7,210.083 ↓ 50.2 301 1

Result (cost=31,154.27..36,890.31 rows=6 width=313) (actual time=5,514.467..7,210.083 rows=301 loops=1)

  • Buffers: shared hit=212269 read=27454
3. 18.037 5,507.362 ↓ 50.2 301 1

Sort (cost=31,154.27..31,154.28 rows=6 width=256) (actual time=5,507.181..5,507.362 rows=301 loops=1)

  • Sort Key: order0_.dateordered DESC, order0_.documentno DESC
  • Sort Method: top-N heapsort Memory: 146kB
  • Buffers: shared hit=204673 read=23162
4. 12.997 5,489.325 ↓ 1,332.8 7,997 1

Nested Loop (cost=9,713.41..31,154.19 rows=6 width=256) (actual time=4,282.036..5,489.325 rows=7,997 loops=1)

  • Buffers: shared hit=204670 read=23162
5. 9.964 4,724.610 ↓ 1,332.8 7,997 1

Nested Loop (cost=9,712.84..31,102.65 rows=6 width=164) (actual time=4,280.755..4,724.610 rows=7,997 loops=1)

  • Buffers: shared hit=172782 read=15027
6. 11.278 4,578.697 ↓ 1,332.8 7,997 1

Nested Loop (cost=9,712.28..31,051.17 rows=6 width=148) (actual time=4,280.676..4,578.697 rows=7,997 loops=1)

  • Buffers: shared hit=133929 read=13894
7. 232.448 4,511.440 ↓ 156.8 7,997 1

Bitmap Heap Scan on c_order order0_ (cost=9,712.00..30,735.71 rows=51 width=143) (actual time=4,280.622..4,511.440 rows=7,997 loops=1)

  • Recheck Cond: (upper((documentno)::text) ~~ '%214.102/002%'::text)
  • Rows Removed by Index Recheck: 2198
  • Filter: ((cancelledorder_id IS NULL) AND (em_obpos_applications_id IS NOT NULL) AND ((docstatus)::text <> 'CJ'::text) AND ((docstatus)::text <> 'CA'::text) AND (em_obpos_islayaway = 'N'::bpchar) AND (em_obpos_isdeleted = 'N'::bpchar) AND ((ad_client_id)::text = '9404134F8B374DEE92E79CD87B834E19'::text) AND ((ad_org_id)::text = 'C459182BF30B46FEAF1C91DE123CDB99'::text))
  • Rows Removed by Filter: 920
  • Heap Blocks: exact=10762
  • Buffers: shared hit=109938 read=13894
8. 4,278.992 4,278.992 ↓ 2.1 11,126 1

Bitmap Index Scan on em_obpos_order_docno_trgm (cost=0.00..9,711.99 rows=5,331 width=0) (actual time=4,278.991..4,278.992 rows=11,126 loops=1)

  • Index Cond: (upper((documentno)::text) ~~ '%214.102/002%'::text)
  • Buffers: shared hit=109701 read=3369
9. 55.979 55.979 ↑ 1.0 1 7,997

Index Scan using c_doctype_key on c_doctype documentty2_ (cost=0.28..6.18 rows=1 width=37) (actual time=0.007..0.007 rows=1 loops=7,997)

  • Index Cond: ((c_doctype_id)::text = (order0_.c_doctype_id)::text)
  • Filter: (((docsubtypeso)::text <> 'OB'::text) AND (isreturn = 'N'::bpchar))
  • Buffers: shared hit=23991
10. 135.949 135.949 ↑ 1.0 1 7,997

Index Scan using c_bpartner_key on c_bpartner businesspa1_ (cost=0.56..8.58 rows=1 width=49) (actual time=0.017..0.017 rows=1 loops=7,997)

  • Index Cond: ((c_bpartner_id)::text = (order0_.c_bpartner_id)::text)
  • Buffers: shared hit=38853 read=1133
11. 751.718 751.718 ↑ 1.0 1 7,997

Index Scan using c_order_key on c_order order_comp4_ (cost=0.56..8.58 rows=1 width=39) (actual time=0.094..0.094 rows=1 loops=7,997)

  • Index Cond: ((c_order_id)::text = (order0_.c_order_id)::text)
  • Buffers: shared hit=31888 read=8135
12.          

SubPlan (for Result)

13. 2.408 319.060 ↑ 1.0 1 301

Aggregate (cost=863.43..863.45 rows=1 width=32) (actual time=1.060..1.060 rows=1 loops=301)

  • Buffers: shared hit=682 read=846
14. 316.652 316.652 ↑ 105.5 2 301

Index Scan using c_orderline_order on c_orderline ol (cost=0.57..860.26 rows=211 width=8) (actual time=1.024..1.052 rows=2 loops=301)

  • Index Cond: ((c_order_id)::text = (order_comp4_.c_order_id)::text)
  • Filter: (c_order_discount_id IS NULL)
  • Buffers: shared hit=682 read=846
15. 2.709 1,365.637 ↑ 1.0 1 301

Aggregate (cost=47.11..47.13 rows=1 width=32) (actual time=4.536..4.537 rows=1 loops=301)

  • Buffers: shared hit=3879 read=3446
16. 0.925 1,362.928 ↑ 1.0 1 301

Nested Loop (cost=2.27..47.10 rows=1 width=6) (actual time=3.719..4.528 rows=1 loops=301)

  • Buffers: shared hit=3879 read=3446
17. 1.253 799.155 ↑ 1.0 1 301

Nested Loop (cost=1.70..46.13 rows=1 width=39) (actual time=2.255..2.655 rows=1 loops=301)

  • Buffers: shared hit=2695 read=2485
18. 0.903 131.236 ↑ 1.0 1 301

Nested Loop (cost=1.13..45.37 rows=1 width=39) (actual time=0.375..0.436 rows=1 loops=301)

  • Buffers: shared hit=1551 read=1484
19. 49.364 49.364 ↑ 1.0 1 301

Index Scan using fin_paym_sch_ord_idx on fin_payment_schedule fps (cost=0.56..8.58 rows=1 width=33) (actual time=0.163..0.164 rows=1 loops=301)

  • Index Cond: ((c_order_id)::text = (order_comp4_.c_order_id)::text)
  • Buffers: shared hit=815 read=696
20. 80.969 80.969 ↑ 8.0 1 301

Index Scan using fin_pay_scheduledetail_or on fin_payment_scheduledetail fpsd (cost=0.57..36.71 rows=8 width=72) (actual time=0.209..0.269 rows=1 loops=301)

  • Index Cond: ((fin_payment_schedule_order)::text = (fps.fin_payment_schedule_id)::text)
  • Buffers: shared hit=736 read=788
21. 666.666 666.666 ↑ 1.0 1 429

Index Scan using fin_payment_detail_key on fin_payment_detail fpd (cost=0.57..0.76 rows=1 width=66) (actual time=1.554..1.554 rows=1 loops=429)

  • Index Cond: ((fin_payment_detail_id)::text = (fpsd.fin_payment_detail_id)::text)
  • Buffers: shared hit=1144 read=1001
22. 562.848 562.848 ↑ 1.0 1 429

Index Scan using fin_payment_key on fin_payment fp (cost=0.57..0.97 rows=1 width=33) (actual time=1.312..1.312 rows=1 loops=429)

  • Index Cond: ((fin_payment_id)::text = (fpd.fin_payment_id)::text)
  • Filter: ((status)::text <> ALL ('{RPVOID,RPAP}'::text[]))
  • Buffers: shared hit=1184 read=961
23. 0.903 15.652 ↑ 1.0 1 301

Aggregate (cost=45.39..45.41 rows=1 width=32) (actual time=0.052..0.052 rows=1 loops=301)

  • Buffers: shared hit=3035
24. 0.903 14.749 ↑ 1.0 1 301

Nested Loop (cost=1.13..45.39 rows=1 width=6) (actual time=0.047..0.049 rows=1 loops=301)

  • Buffers: shared hit=3035
25. 6.923 6.923 ↑ 1.0 1 301

Index Scan using fin_paym_sch_ord_idx on fin_payment_schedule fin_paymen7_ (cost=0.56..8.58 rows=1 width=33) (actual time=0.023..0.023 rows=1 loops=301)

  • Index Cond: ((c_order_id)::text = (order0_.c_order_id)::text)
  • Buffers: shared hit=1511
26. 6.923 6.923 ↑ 8.0 1 301

Index Scan using fin_pay_scheduledetail_or on fin_payment_scheduledetail fin_paymen6_ (cost=0.57..36.73 rows=8 width=39) (actual time=0.022..0.023 rows=1 loops=301)

  • Index Cond: ((fin_payment_schedule_order)::text = (fin_paymen7_.fin_payment_schedule_id)::text)
  • Filter: ((fin_payment_detail_id IS NOT NULL) AND (iscanceled = 'N'::bpchar))
  • Buffers: shared hit=1524
Planning time : 10.884 ms
Execution time : 7,213.439 ms