explain.depesz.com

PostgreSQL's explain analyze made readable

Result: imIE

Settings
# exclusive inclusive rows x rows loops node
1. 2,409.793 74,305.613 ↓ 15,122.1 514,150 1

Hash Left Join (cost=2,080,386.05..8,150,819.61 rows=34 width=172) (actual time=11,340.734..74,305.613 rows=514,150 loops=1)

  • Hash Cond: ((sm.shippingid)::text = (sii.shipment_id)::text)
2. 879.413 48,244.197 ↓ 15,121.6 514,133 1

Nested Loop Left Join (cost=2,080,348.31..8,150,718.68 rows=34 width=149) (actual time=11,339.856..48,244.197 rows=514,133 loops=1)

3. 1,048.198 38,110.390 ↓ 15,121.6 514,133 1

Nested Loop Left Join (cost=2,080,347.75..8,150,558.53 rows=34 width=141) (actual time=11,339.822..38,110.390 rows=514,133 loops=1)

4. 15,488.451 26,779.532 ↓ 15,121.6 514,133 1

Bitmap Heap Scan on shipmentmaster sm (cost=2,080,347.18..8,150,266.30 rows=34 width=102) (actual time=11,339.777..26,779.532 rows=514,133 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,051,641
  • 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,114,302
  • Heap Blocks: exact=129,267 lossy=1,567,229
5. 0.004 11,291.081 ↓ 0.0 0 1

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

6. 9,055.776 9,055.776 ↓ 0.0 0 1

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

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

Bitmap Index Scan on shipmentmaster_status_lostdate (cost=0.00..242,289.81 rows=3,570,098 width=0) (actual time=2,235.301..2,235.301 rows=4,292,142 loops=1)

  • Index Cond: ((status)::text = ANY ('{Shipped,ReadyToShip,shipped}'::text[]))
8. 10,282.660 10,282.660 ↑ 1.0 1 514,133

Index Scan using deliveryboyrunsheet_idx on deliveryboyrunsheet drs (cost=0.56..8.58 rows=1 width=53) (actual time=0.020..0.020 rows=1 loops=514,133)

  • Index Cond: ((shippingid)::text = (sm.shippingid)::text)
  • Filter: ((status)::text <> '1001'::text)
  • Rows Removed by Filter: 0
9. 9,254.394 9,254.394 ↑ 1.0 1 514,133

Index Only Scan using shipmentmasterinformation_pdd_shippingid on shipmentmasterinformation smi (cost=0.56..4.70 rows=1 width=22) (actual time=0.018..0.018 rows=1 loops=514,133)

  • Index Cond: (shippingid = (sm.shippingid)::text)
  • Heap Fetches: 10,685
10. 0.381 0.723 ↑ 1.0 1,055 1

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

  • Buckets: 2,048 Batches: 1 Memory Usage: 73kB
11. 0.342 0.342 ↑ 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.342 rows=1,055 loops=1)

12.          

SubPlan (for Hash Left Join)

13. 514.150 13,367.900 ↑ 1.0 1 514,150

Limit (cost=0.70..0.91 rows=1 width=8) (actual time=0.026..0.026 rows=1 loops=514,150)

14. 12,853.750 12,853.750 ↑ 42.0 1 514,150

Index Only Scan Backward using shipmentauditlog_pkey on shipmentauditlog al (cost=0.70..9.44 rows=42 width=8) (actual time=0.025..0.025 rows=1 loops=514,150)

  • Index Cond: (shippingid = (sm.shippingid)::text)
  • Heap Fetches: 19,571
15. 514.150 10,283.000 ↑ 1.0 1 514,150

Limit (cost=0.70..0.91 rows=1 width=19) (actual time=0.020..0.020 rows=1 loops=514,150)

16. 9,768.850 9,768.850 ↑ 42.0 1 514,150

Index Only Scan Backward using shipmentauditlog_pkey on shipmentauditlog al_1 (cost=0.70..9.44 rows=42 width=19) (actual time=0.019..0.019 rows=1 loops=514,150)

  • Index Cond: (shippingid = (sm.shippingid)::text)
  • Heap Fetches: 19,571
Planning time : 2.806 ms
Execution time : 74,427.903 ms