explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 9fam : Optimization for: plan #XyCI

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 3,874.476 358,894.653 ↑ 4,845.2 9,170,583 1

Merge Right Join (cost=30,750,269.74..700,295,938.33 rows=44,432,915,539 width=112) (actual time=342,219.439..358,894.653 rows=9,170,583 loops=1)

  • Merge Cond: ((weight.me_id)::text = (ld.me_id)::text)
2.          

CTE load

3. 31,763.728 31,763.728 ↑ 1.2 9,170,583 1

Seq Scan on load (cost=0.00..3,345,484.88 rows=10,950,796 width=124) (actual time=0.057..31,763.728 rows=9,170,583 loops=1)

  • Filter: (((carrier_name)::text <> 'TRANSPLACE QA'::text) AND ((mode_type)::text = 'TRUCK'::text) AND ((carrier_name)::text <> ALL ('{"CPU (Customer Pick up)","CUSTOMER PICK UP - PRE LOADED","CUSTOMER PICKUP","CUSTOMER PICK UPS-CANADIAN SHIPMENTS","CUSTOMER ARRANGED","CUSTOMER PICK-UP - REEFER","Customer Pick up","TRANSPLACE QA"}'::text[])) AND ((equipment_type)::text = ANY ('{DRYVAN,"TRUCK,VAN","TRUCK, VAN","53 FT DRYVAN","53 FT VAN","53 FT DRY VAN","DRY VAN-AIR RIDE","CLOSED VAN","53FT DRYVAN","Dry Van","Van Trailer","VAN TRAILER","48 FT DRY VAN TRAILER","TRAILER, DRY WEIGHT","53FT DRY VAN WOOD FLOOR"}'::text[])))
  • Rows Removed by Filter: 21,095,543
4.          

CTE weight

5. 2,382.701 233,506.992 ↓ 13.0 577,498 1

HashAggregate (cost=20,908,713.30..20,909,158.08 rows=44,478 width=17) (actual time=233,281.377..233,506.992 rows=577,498 loops=1)

  • Group Key: line_item.me_id
  • Filter: (sum(line_item.weight) IS NOT NULL)
  • Rows Removed by Filter: 985
6. 12,425.298 231,124.291 ↓ 1.5 5,855,750 1

Hash Join (cost=4,417,820.86..20,879,729.51 rows=3,864,505 width=17) (actual time=49,030.850..231,124.291 rows=5,855,750 loops=1)

  • Hash Cond: ((line_item.freight_id)::text = (freight.freight_id)::text)
7. 169,827.109 169,827.109 ↓ 1.5 6,141,025 1

Seq Scan on line_item (cost=0.00..16,139,286.12 rows=4,156,774 width=26) (actual time=0.092..169,827.109 rows=6,141,025 loops=1)

  • Filter: (((hazmat)::text = 'N'::text) AND ((status)::text = 'ACTIVE'::text) AND (updated_datetime > (now() - '21 days'::interval)))
  • Rows Removed by Filter: 291,477,136
8. 16,756.504 48,871.884 ↓ 1.0 52,475,399 1

Hash (cost=3,510,452.60..3,510,452.60 rows=52,199,141 width=9) (actual time=48,871.884..48,871.884 rows=52,475,399 loops=1)

  • Buckets: 524,288 Batches: 256 Memory Usage: 12,482kB
9. 32,115.380 32,115.380 ↓ 1.0 52,475,399 1

Seq Scan on freight (cost=0.00..3,510,452.60 rows=52,199,141 width=9) (actual time=0.012..32,115.380 rows=52,475,399 loops=1)

  • Filter: (((status)::text = 'ACTIVE'::text) AND ((order_type)::text <> ALL ('{"CUSTOMER PICKUP",CUSTOMER_PICKUP,CPU,CPU-CHILD,RETAIL_CPU,OB_CPU,CPU-PARENT,"CUSTOMER OB CPU",HOD_CPU,CPU_DRAYAGE,"CPU MANAGED"}'::text[])))
  • Rows Removed by Filter: 3,939,846
10.          

CTE order_type

11. 543.541 42,932.254 ↓ 157.5 574,567 1

Hash Join (cost=4,074,865.51..4,093,292.33 rows=3,649 width=102) (actual time=42,285.786..42,932.254 rows=574,567 loops=1)

  • Hash Cond: (((raw_data.me_id)::text = (me_order_type_characterized_1.me_id)::text) AND (raw_data.n_order_types = (max(me_order_type_characterized_1.n_order_types))))
12.          

CTE me_order_type_characterized

13. 154.098 40,984.080 ↑ 1.3 576,661 1

Subquery Scan on me_order_type_characterized (cost=4,023,425.66..4,056,614.36 rows=729,766 width=49) (actual time=39,519.241..40,984.080 rows=576,661 loops=1)

14. 361.585 40,829.982 ↑ 1.3 576,661 1

WindowAgg (cost=4,023,425.66..4,047,492.29 rows=729,766 width=29) (actual time=39,519.238..40,829.982 rows=576,661 loops=1)

15. 305.965 40,468.397 ↑ 1.3 576,661 1

Unique (cost=4,023,425.66..4,029,248.14 rows=729,766 width=21) (actual time=39,519.228..40,468.397 rows=576,661 loops=1)

16. 3,117.665 40,162.432 ↓ 1.5 1,133,141 1

Sort (cost=4,023,425.66..4,025,366.48 rows=776,331 width=21) (actual time=39,519.226..40,162.432 rows=1,133,141 loops=1)

  • Sort Key: freight_1.me_id, freight_1.order_type
  • Sort Method: external merge Disk: 36,344kB
17. 37,044.767 37,044.767 ↓ 1.5 1,133,141 1

Seq Scan on freight freight_1 (cost=0.00..3,931,554.48 rows=776,331 width=21) (actual time=835.956..37,044.767 rows=1,133,141 loops=1)

  • Filter: (((status)::text = 'ACTIVE'::text) AND (updated_datetime > (now() - '21 days'::interval)) AND ((order_type)::text <> ALL ('{"CUSTOMER PICKUP",CUSTOMER_PICKUP,CPU,CPU-CHILD,RETAIL_CPU,OB_CPU,CPU-PARENT,"CUSTOMER OB CPU",HOD_CPU,CPU_DRAYAGE,"CPU MANAGED"}'::text[])))
  • Rows Removed by Filter: 55,282,104
18. 39,622.289 39,622.289 ↑ 1.3 576,661 1

CTE Scan on me_order_type_characterized raw_data (cost=0.00..14,595.32 rows=729,766 width=102) (actual time=39,519.244..39,622.289 rows=576,661 loops=1)

19. 187.730 2,766.424 ↓ 2,872.8 574,567 1

Hash (cost=18,248.15..18,248.15 rows=200 width=70) (actual time=2,766.424..2,766.424 rows=574,567 loops=1)

  • Buckets: 262,144 (originally 1024) Batches: 4 (originally 1) Memory Usage: 14,337kB
20. 798.865 2,578.694 ↓ 2,872.8 574,567 1

HashAggregate (cost=18,244.15..18,246.15 rows=200 width=70) (actual time=2,359.654..2,578.694 rows=574,567 loops=1)

  • Group Key: me_order_type_characterized_1.me_id
21. 1,779.829 1,779.829 ↑ 1.3 576,661 1

CTE Scan on me_order_type_characterized me_order_type_characterized_1 (cost=0.00..14,595.32 rows=729,766 width=70) (actual time=0.002..1,779.829 rows=576,661 loops=1)

22. 2,163.585 236,079.068 ↓ 13.0 577,498 1

Sort (cost=4,323.44..4,434.64 rows=44,478 width=70) (actual time=235,732.778..236,079.068 rows=577,498 loops=1)

  • Sort Key: weight.me_id
  • Sort Method: external merge Disk: 15,840kB
23. 233,915.483 233,915.483 ↓ 13.0 577,498 1

CTE Scan on weight (cost=0.00..889.56 rows=44,478 width=70) (actual time=233,281.382..233,915.483 rows=577,498 loops=1)

24. 3,104.508 118,941.109 ↑ 21.8 9,170,583 1

Materialize (cost=2,398,011.02..5,949,217.28 rows=199,797,273 width=104) (actual time=106,486.653..118,941.109 rows=9,170,583 loops=1)

25. 3,840.302 115,836.601 ↑ 21.8 9,170,583 1

Merge Left Join (cost=2,398,011.02..5,449,724.09 rows=199,797,273 width=104) (actual time=106,486.647..115,836.601 rows=9,170,583 loops=1)

  • Merge Cond: ((ld.me_id)::text = (order_type.me_id)::text)
26. 27,813.074 67,134.583 ↑ 1.2 9,170,583 1

Sort (cost=2,397,722.14..2,425,099.13 rows=10,950,796 width=72) (actual time=61,729.809..67,134.583 rows=9,170,583 loops=1)

  • Sort Key: ld.me_id
  • Sort Method: external merge Disk: 432,152kB
27. 39,321.509 39,321.509 ↑ 1.2 9,170,583 1

CTE Scan on load ld (cost=0.00..219,015.92 rows=10,950,796 width=72) (actual time=0.062..39,321.509 rows=9,170,583 loops=1)

28. 1,636.871 44,861.716 ↓ 157.5 574,567 1

Sort (cost=288.88..298.00 rows=3,649 width=94) (actual time=44,756.825..44,861.716 rows=574,567 loops=1)

  • Sort Key: order_type.me_id
  • Sort Method: external sort Disk: 19,488kB
29. 43,224.845 43,224.845 ↓ 157.5 574,567 1

CTE Scan on order_type (cost=0.00..72.98 rows=3,649 width=94) (actual time=42,285.790..43,224.845 rows=574,567 loops=1)

Planning time : 1.391 ms
Execution time : 360,040.095 ms