explain.depesz.com

PostgreSQL's explain analyze made readable

Result: VvTS

Settings
# exclusive inclusive rows x rows loops node
1. 3,570.850 760,142.167 ↓ 20,688.0 1,034,398 1

Nested Loop Left Join (cost=2,084,061.19..8,474,259.33 rows=50 width=143) (actual time=554,022.692..760,142.167 rows=1,034,398 loops=1)

2. 39,200.894 593,136.433 ↓ 20,688.0 1,034,398 1

Bitmap Heap Scan on shipmentmaster sm (cost=2,084,060.63..8,473,828.95 rows=50 width=104) (actual time=554,022.606..593,136.433 rows=1,034,398 loops=1)

  • Recheck Cond: ((((status)::text = 'Delivered'::text) AND (lastmodifieddate >= ('now'::cstring)::date) AND (lastmodifieddate < (('now'::cstring)::date + '1 day'::interval)) AND ((COALESCE(notificationtype, ''::character varying))::text <> 'RTO'::text) AND (NOT COALESCE(isreversepick, false)) AND (NOT COALESCE(isrecnotified, false)) AND (parentmps IS NULL)) OR ((status)::text = ANY ('{Shipped,ReadyToShip,shipped}'::text[])))
  • Rows Removed by Index Recheck: 18,255,866
  • Filter: ((NOT COALESCE(isreversepick, false)) AND (NOT COALESCE(isrecnotified, false)) AND (parentmps IS NULL) AND ((COALESCE(notificationtype, ''::character varying))::text <> 'RTO'::text) AND (currenthubid = finaldestinationid) AND ((COALESCE(notificationstatus, ''::character varying))::text = ANY ('{"",ReAttempt,Reattempt}'::text[])) AND (createddate > (('now'::cstring)::date - '50 days'::interval)) AND (lastmodifieddate < (('now'::cstring)::date + '1 day'::interval)) AND (((lastmodifieddate >= ('now'::cstring)::date) AND ((status)::text = 'Delivered'::text)) OR (((status)::text = ANY ('{Shipped,ReadyToShip,shipped}'::text[])) AND ((shipmentstatus)::text <> ALL ('{Investigate,HVTExp}'::text[])))))
  • Rows Removed by Filter: 2,613,898
  • Heap Blocks: exact=152,402 lossy=2,596,134
3. 0.002 553,935.539 ↓ 0.0 0 1

BitmapOr (cost=2,084,060.63..2,084,060.63 rows=4,032,338 width=0) (actual time=553,935.539..553,935.539 rows=0 loops=1)

4. 544,207.333 544,207.333 ↑ 4.0 110,212 1

Bitmap Index Scan on shipmentmaster_lastmodifieddate (cost=0.00..1,837,285.30 rows=444,973 width=0) (actual time=544,207.333..544,207.333 rows=110,212 loops=1)

  • Index Cond: (((status)::text = 'Delivered'::text) AND (lastmodifieddate >= ('now'::cstring)::date) AND (lastmodifieddate < (('now'::cstring)::date + '1 day'::interval)))
5. 9,728.204 9,728.204 ↓ 2.8 9,900,528 1

Bitmap Index Scan on shipmentmaster_status_lostdate (cost=0.00..246,775.30 rows=3,587,365 width=0) (actual time=9,728.204..9,728.204 rows=9,900,528 loops=1)

  • Index Cond: ((status)::text = ANY ('{Shipped,ReadyToShip,shipped}'::text[]))
6. 163,434.884 163,434.884 ↑ 1.0 1 1,034,398

Index Scan using deliveryboyrunsheet_idx on deliveryboyrunsheet drs (cost=0.56..8.58 rows=1 width=53) (actual time=0.149..0.158 rows=1 loops=1,034,398)

  • Index Cond: ((shippingid)::text = (sm.shippingid)::text)
  • Filter: ((status)::text <> '1001'::text)
  • Rows Removed by Filter: 0
Planning time : 31.985 ms
Execution time : 760,478.732 ms