explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Gjxz

Settings
# exclusive inclusive rows x rows loops node
1. 1,389.250 50,876.783 ↓ 15,117.0 513,978 1

Hash Left Join (cost=2,080,386.05..8,150,757.70 rows=34 width=172) (actual time=11,877.510..50,876.783 rows=513,978 loops=1)

  • Hash Cond: ((sm.shippingid)::text = (sii.shipment_id)::text)
2. 780.609 49,486.845 ↓ 15,116.5 513,961 1

Nested Loop Left Join (cost=2,080,348.31..8,150,718.68 rows=34 width=149) (actual time=11,876.711..49,486.845 rows=513,961 loops=1)

3. 915.123 36,371.172 ↓ 15,116.5 513,961 1

Nested Loop Left Join (cost=2,080,347.75..8,150,558.53 rows=34 width=141) (actual time=11,876.682..36,371.172 rows=513,961 loops=1)

4. 13,854.578 25,690.790 ↓ 15,116.5 513,961 1

Bitmap Heap Scan on shipmentmaster sm (cost=2,080,347.18..8,150,266.30 rows=34 width=102) (actual time=11,876.641..25,690.790 rows=513,961 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: 8,037,566
  • 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: 3,110,427
  • Heap Blocks: exact=121,130 lossy=1,567,231
5. 0.003 11,836.212 ↓ 0.0 0 1

BitmapOr (cost=2,080,347.18..2,080,347.18 rows=3,570,098 width=0) (actual time=11,836.212..11,836.212 rows=0 loops=1)

6. 10,535.304 10,535.304 ↓ 0.0 0 1

Bitmap Index Scan on shipmentmaster_lastmodifieddate (cost=0.00..1,838,057.35 rows=1 width=0) (actual time=10,535.304..10,535.304 rows=0 loops=1)

  • Index Cond: (((status)::text = 'Delivered'::text) AND (lastmodifieddate >= ('now'::cstring)::date) AND (lastmodifieddate < (('now'::cstring)::date + '1 day'::interval)))
7. 1,300.905 1,300.905 ↓ 1.2 4,265,528 1

Bitmap Index Scan on shipmentmaster_status_lostdate (cost=0.00..242,289.81 rows=3,570,098 width=0) (actual time=1,300.905..1,300.905 rows=4,265,528 loops=1)

  • Index Cond: ((status)::text = ANY ('{Shipped,ReadyToShip,shipped}'::text[]))
8. 9,765.259 9,765.259 ↑ 1.0 1 513,961

Index Scan using deliveryboyrunsheet_idx on deliveryboyrunsheet drs (cost=0.56..8.58 rows=1 width=53) (actual time=0.019..0.019 rows=1 loops=513,961)

  • Index Cond: ((shippingid)::text = (sm.shippingid)::text)
  • Filter: ((status)::text <> '1001'::text)
  • Rows Removed by Filter: 0
9. 12,335.064 12,335.064 ↑ 1.0 1 513,961

Index Only Scan using shipmentmasterinformation_pdd_shippingid on shipmentmasterinformation smi (cost=0.56..4.70 rows=1 width=22) (actual time=0.024..0.024 rows=1 loops=513,961)

  • Index Cond: (shippingid = (sm.shippingid)::text)
  • Heap Fetches: 9,964
10. 0.363 0.688 ↑ 1.0 1,055 1

Hash (cost=24.55..24.55 rows=1,055 width=23) (actual time=0.688..0.688 rows=1,055 loops=1)

  • Buckets: 2,048 Batches: 1 Memory Usage: 73kB
11. 0.325 0.325 ↑ 1.0 1,055 1

Seq Scan on shipment_impact_incident_map sii (cost=0.00..24.55 rows=1,055 width=23) (actual time=0.006..0.325 rows=1,055 loops=1)

Planning time : 1.261 ms
Execution time : 51,020.836 ms