explain.depesz.com

PostgreSQL's explain analyze made readable

Result: SMH8

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 27.557 13,009.031 ↑ 154.0 3,754 1

Merge Left Join (cost=656,495.90..947,295.58 rows=578,190 width=825) (actual time=9,971.721..13,009.031 rows=3,754 loops=1)

  • Output: (sum(CASE WHEN ((b_1.invdate >= '2018-07-31'::date) AND (b_1.invdate <= '2018-09-01'::date)) THEN b_1.total ELSE 0::numeric END)), (sum(CASE WHEN ((b_1.invdate >= '2018-07-31'::date) AND (b_1.invdate <= '2018-09-01'::date)) THEN CASE WHEN (a.tota (...)
  • Merge Cond: (((CASE WHEN (upper(regexp_replace((c.short_name)::text, '[^a-zA-Z]'::text, ''::text, 'g'::text)) ~~ 'NDS%'::text) THEN 10 ELSE b_1.cmpy_no END) = b.cmpy_no) AND ((COALESCE(NULLIF((b_1.altbt_code)::text, ''::text), (b_1.bt_code)::text)) = (b. (...)
2. 2,899.634 12,957.216 ↑ 154.0 3,754 1

GroupAggregate (cost=656,495.47..897,889.80 rows=578,190 width=45) (actual time=9,971.685..12,957.216 rows=3,754 loops=1)

  • Output: sum(CASE WHEN ((b_1.invdate >= '2018-07-31'::date) AND (b_1.invdate <= '2018-09-01'::date)) THEN b_1.total ELSE 0::numeric END), sum(CASE WHEN ((b_1.invdate >= '2018-07-31'::date) AND (b_1.invdate <= '2018-09-01'::date)) THEN CASE WHEN (a.t (...)
  • Group Key: (CASE WHEN (upper(regexp_replace((c.short_name)::text, '[^a-zA-Z]'::text, ''::text, 'g'::text)) ~~ 'NDS%'::text) THEN 10 ELSE b_1.cmpy_no END), (COALESCE(NULLIF((b_1.altbt_code)::text, ''::text), (b_1.bt_code)::text))
3. 3,350.923 10,057.582 ↑ 1.0 563,265 1

Sort (cost=656,495.47..657,940.95 rows=578,190 width=45) (actual time=9,971.353..10,057.582 rows=563,265 loops=1)

  • Output: (CASE WHEN (upper(regexp_replace((c.short_name)::text, '[^a-zA-Z]'::text, ''::text, 'g'::text)) ~~ 'NDS%'::text) THEN 10 ELSE b_1.cmpy_no END), (COALESCE(NULLIF((b_1.altbt_code)::text, ''::text), (b_1.bt_code)::text)), b_1.invdate, b_ (...)
  • Sort Key: (CASE WHEN (upper(regexp_replace((c.short_name)::text, '[^a-zA-Z]'::text, ''::text, 'g'::text)) ~~ 'NDS%'::text) THEN 10 ELSE b_1.cmpy_no END), (COALESCE(NULLIF((b_1.altbt_code)::text, ''::text), (b_1.bt_code)::text))
  • Sort Method: external sort Disk: 26264kB
4. 633.359 6,706.659 ↑ 1.0 563,265 1

Hash Left Join (cost=534,220.68..601,159.27 rows=578,190 width=45) (actual time=4,886.892..6,706.659 rows=563,265 loops=1)

  • Output: CASE WHEN (upper(regexp_replace((c.short_name)::text, '[^a-zA-Z]'::text, ''::text, 'g'::text)) ~~ 'NDS%'::text) THEN 10 ELSE b_1.cmpy_no END, COALESCE(NULLIF((b_1.altbt_code)::text, ''::text), (b_1.bt_code)::text), b_1.invdate, (...)
  • Hash Cond: (b_1.cmpy_no = c.no)
5. 747.902 6,073.251 ↑ 1.0 563,265 1

Merge Right Join (cost=534,214.97..587,421.55 rows=578,190 width=40) (actual time=4,886.805..6,073.251 rows=563,265 loops=1)

  • Output: b_1.cmpy_no, b_1.altbt_code, b_1.bt_code, b_1.invdate, b_1.total, b_1.vendpay, b_1.acrudamt, b_1.fcramt, a.total
  • Merge Cond: ((a.cmpy_no = b_1.cmpy_no) AND ((a.inv_code)::text = (b_1.code)::text))
6. 364.672 364.672 ↑ 1.0 792,138 1

Index Scan using invchzpay_inv_code_key on trk.invchzpay a (cost=0.42..44,648.30 rows=792,138 width=18) (actual time=0.019..364.672 rows=792,138 loops=1)

  • Output: a.no, a.cmpy_no, a.inv_code, a.olp_code, a.tl_code, a.ch_code, a.chassis_eqo_code, a.provider_eqo_code, a.vn_code, a.city_no, a.start_date, a.stop_date, a.daily_rate, a.tax_rate, a.total, a.adate, a.atime, a.alogin, (...)
7. 4,281.011 4,960.677 ↑ 1.0 563,265 1

Sort (cost=534,214.55..535,660.02 rows=578,190 width=44) (actual time=4,881.878..4,960.677 rows=563,265 loops=1)

  • Output: b_1.cmpy_no, b_1.altbt_code, b_1.bt_code, b_1.invdate, b_1.total, b_1.vendpay, b_1.acrudamt, b_1.fcramt, b_1.code
  • Sort Key: b_1.cmpy_no, b_1.code
  • Sort Method: external sort Disk: 34104kB
8. 524.680 679.666 ↑ 1.0 563,265 1

Bitmap Heap Scan on trk.invoice b_1 (cost=104,419.68..478,878.34 rows=578,190 width=44) (actual time=165.368..679.666 rows=563,265 loops=1)

  • Output: b_1.cmpy_no, b_1.altbt_code, b_1.bt_code, b_1.invdate, b_1.total, b_1.vendpay, b_1.acrudamt, b_1.fcramt, b_1.code
  • Recheck Cond: ((b_1.invdate >= '2018-07-31'::date) AND (b_1.invdate <= '2019-07-31'::date))
  • Filter: ((b_1.pdate IS NOT NULL) AND ((b_1.type)::text <> 'S'::text))
  • Rows Removed by Filter: 8405
  • Heap Blocks: exact=49631
9. 154.986 154.986 ↑ 1.0 571,670 1

Bitmap Index Scan on invoice_cmpy_no_invdate_key (cost=0.00..104,275.14 rows=593,034 width=0) (actual time=154.986..154.986 rows=571,670 loops=1)

  • Index Cond: ((b_1.invdate >= '2018-07-31'::date) AND (b_1.invdate <= '2019-07-31'::date))
10. 0.007 0.049 ↑ 1.3 60 1

Hash (cost=4.76..4.76 rows=76 width=9) (actual time=0.049..0.049 rows=60 loops=1)

  • Output: c.short_name, c.no
  • Buckets: 1024 Batches: 1 Memory Usage: 3kB
11. 0.042 0.042 ↑ 1.3 60 1

Seq Scan on com.company c (cost=0.00..4.76 rows=76 width=9) (actual time=0.005..0.042 rows=60 loops=1)

  • Output: c.short_name, c.no
12. 24.258 24.258 ↑ 11.1 29,765 1

Index Scan using billto_cmpy_no_code_key on com.billto b (cost=0.42..38,034.18 rows=331,239 width=31) (actual time=0.018..24.258 rows=29,765 loops=1)

  • Output: b.code, b.name, b.addr1, b.addr2, b.city, b.st_code, b.zip, b.contact, b.rem, b.cty_no, b.adate, b.atime, b.alogin, b.udate, b.utime, b.ulogin, b.doccodes, b.prtinv, b.sumrefer, b.sumdate, b.verify, b.status, b.btt_type, b.fuelschg, b.fuelf (...)
Planning time : 1.430 ms
Execution time : 13,024.397 ms