explain.depesz.com

PostgreSQL's explain analyze made readable

Result: YJgw

Settings
# exclusive inclusive rows x rows loops node
1. 0.046 4,531.084 ↑ 1,347,148,579.3 299 1

Unique (cost=37,885,679,310,789.98..37,887,693,297,916.02 rows=402,797,425,209 width=16) (actual time=4,531.019..4,531.084 rows=299 loops=1)

  • Output: ld.id
  • Buffers: shared hit=256,254, temp written=3,684
2. 0.415 4,531.038 ↑ 1,347,148,579.3 299 1

Sort (cost=37,885,679,310,789.98..37,886,686,304,353.00 rows=402,797,425,209 width=16) (actual time=4,531.018..4,531.038 rows=299 loops=1)

  • Output: ld.id
  • Sort Key: ld.id
  • Sort Method: quicksort Memory: 39kB
  • Buffers: shared hit=256,254, temp written=3,684
3. 0.143 4,530.623 ↑ 1,347,148,579.3 299 1

Subquery Scan on ld (cost=29,520,238,812,482.00..37,766,735,092,035.07 rows=402,797,425,209 width=16) (actual time=1,689.812..4,530.623 rows=299 loops=1)

  • Output: ld.id
  • Buffers: shared hit=256,254, temp written=3,684
4. 66.913 4,530.480 ↑ 1,347,148,579.3 299 1

Hash Right Join (cost=29,520,238,812,482.00..37,762,707,117,782.98 rows=402,797,425,209 width=817) (actual time=1,689.811..4,530.480 rows=299 loops=1)

  • Output: l.id, NULL::json, NULL::numeric, NULL::text, NULL::double precision, NULL::double precision, NULL::text, NULL::json, NULL::timestamp without time zone, NULL::timestamp without time zone, NULL::uuid, NULL::double precision, NULL::json, NULL::double precision, NULL::double precision, NULL::double precision, NULL::double precision, NULL::double precision, NULL::json, NULL::numeric, NULL::text, NULL::json, NULL::double precision, NULL::json, NULL::numeric, NULL::json, NULL::double precision, NULL::double precision, NULL::text, NULL::timestamp without time zone, NULL::timestamp without time zone, NULL::double precision, NULL::jsonb, NULL::double precision, NULL::json, NULL::timestamp without time zone, NULL::timestamp without time zone, NULL::timestamp without time zone, NULL::json, NULL::uuid, NULL::integer, NULL::integer, NULL::integer, NULL::integer, NULL::point, NULL::point, NULL::double precision, NULL::double precision, NULL::boolean
  • Hash Cond: (ld_1.id = l.id)
  • Buffers: shared hit=256,254, temp written=3,684
5.          

CTE load_distances

6. 1,312.867 3,957.445 ↑ 669,752,759.9 517,925 1

Nested Loop Left Join (cost=0.00..15,610,282,676,147.87 rows=346,881,698,149,427 width=60) (actual time=247.520..3,957.445 rows=517,925 loops=1)

  • Output: l_1.id, l_1.origin_point, d0.distance, l_1.destination_point, d1.distance
  • Join Filter: point_eq(l_1.destination_point, d1.origin_point)
  • Rows Removed by Join Filter: 11,380,476
  • Buffers: shared hit=223,010
7. 1,381.945 2,126.653 ↑ 25,935.3 517,925 1

Nested Loop Left Join (cost=0.00..604,685,821.00 rows=13,432,533,025 width=54) (actual time=105.048..2,126.653 rows=517,925 loops=1)

  • Output: l_1.id, l_1.origin_point, l_1.destination_point, d0.distance
  • Join Filter: point_eq(l_1.origin_point, d0.destination_point)
  • Rows Removed by Join Filter: 11,383,847
  • Buffers: shared hit=213,474
8. 226.783 226.783 ↑ 1.0 517,925 1

Seq Scan on public.load l_1 (cost=0.00..209,139.57 rows=520,157 width=48) (actual time=0.017..226.783 rows=517,925 loops=1)

  • Output: l_1.id, l_1.document, l_1.origin_point, l_1.destination_point, l_1.origin_geog, l_1.destination_geog
  • Buffers: shared hit=203,938
9. 412.984 517.925 ↑ 3,521.5 22 517,925

Materialize (cost=0.00..12,828.55 rows=77,472 width=22) (actual time=0.000..0.001 rows=22 loops=517,925)

  • Output: d0.distance, d0.destination_point
  • Buffers: shared hit=9,536
10. 104.941 104.941 ↑ 3,521.5 22 1

Seq Scan on public.deadhead d0 (cost=0.00..12,441.19 rows=77,472 width=22) (actual time=12.217..104.941 rows=22 loops=1)

  • Output: d0.distance, d0.destination_point
  • Filter: point_eq(d0.origin_point, '(-87.8950999999999993,41.9724000000000004)'::point)
  • Rows Removed by Filter: 232,536
  • Buffers: shared hit=9,536
11. 375.539 517.925 ↑ 3,521.5 22 517,925

Materialize (cost=0.00..12,828.55 rows=77,472 width=22) (actual time=0.000..0.001 rows=22 loops=517,925)

  • Output: d1.distance, d1.origin_point
  • Buffers: shared hit=9,536
12. 142.386 142.386 ↑ 3,521.5 22 1

Seq Scan on public.deadhead d1 (cost=0.00..12,441.19 rows=77,472 width=22) (actual time=36.310..142.386 rows=22 loops=1)

  • Output: d1.distance, d1.origin_point
  • Filter: point_eq(d1.destination_point, '(-95.2335999999999956,29.767199999999999)'::point)
  • Rows Removed by Filter: 232,536
  • Buffers: shared hit=9,536
13.          

CTE missing_inserts_odh

14. 107.303 424.999 ↓ 0.0 0 1

Insert on public.missing_deadhead (cost=6,954,978,047,896.01..6,954,978,047,902.51 rows=200 width=136) (actual time=424.999..424.999 rows=0 loops=1)

  • Conflict Resolution: NOTHING
  • Conflict Arbiter Indexes: missing_deadhead_uk
  • Tuples Inserted: 1
  • Conflicting Tuples: 15,767
  • Buffers: shared hit=79,089, temp read=3,686
15. 72.835 317.696 ↓ 78.8 15,768 1

Subquery Scan on "*SELECT*" (cost=6,954,978,047,896.01..6,954,978,047,902.51 rows=200 width=136) (actual time=239.738..317.696 rows=15,768 loops=1)

  • Output: ""*SELECT*"".origin_lon, ""*SELECT*"".origin_lat, ""*SELECT*"".destination_lon, ""*SELECT*"".destination_lat, nextval('missing_deadhead_id_seq'::regclass)
  • Buffers: shared hit=15,768, temp read=3,686
16. 144.023 244.861 ↓ 78.8 15,768 1

HashAggregate (cost=6,954,978,047,896.01..6,954,978,047,898.01 rows=200 width=32) (actual time=239.673..244.861 rows=15,768 loops=1)

  • Output: ('-87.8950999999999993'::double precision), ('41.9724000000000004'::double precision), (l2.origin_point[0]), (l2.origin_point[1])
  • Group Key: '-87.8950999999999993'::double precision, '41.9724000000000004'::double precision, l2.origin_point[0], l2.origin_point[1]
  • Buffers: temp read=3,686
17. 100.838 100.838 ↑ 3,418,079.0 507,422 1

CTE Scan on load_distances l2 (cost=0.00..6,937,633,962,988.54 rows=1,734,408,490,747 width=32) (actual time=0.017..100.838 rows=507,422 loops=1)

  • Output: '-87.8950999999999993'::double precision, '41.9724000000000004'::double precision, l2.origin_point[0], l2.origin_point[1]
  • Filter: (l2.odh IS NULL)
  • Rows Removed by Filter: 10,503
  • Buffers: temp read=3,686
18.          

CTE missing_inserts_ddh

19. 85.155 418.709 ↓ 0.0 0 1

Insert on public.missing_deadhead missing_deadhead_1 (cost=6,954,978,047,896.01..6,954,978,047,902.51 rows=200 width=136) (actual time=418.709..418.709 rows=0 loops=1)

  • Conflict Resolution: NOTHING
  • Conflict Arbiter Indexes: missing_deadhead_uk
  • Tuples Inserted: 1
  • Conflicting Tuples: 19,109
  • Buffers: shared hit=95,646, temp read=3,686 written=1
20. 86.734 333.554 ↓ 95.5 19,110 1

Subquery Scan on "*SELECT*_1" (cost=6,954,978,047,896.01..6,954,978,047,902.51 rows=200 width=136) (actual time=240.691..333.554 rows=19,110 loops=1)

  • Output: ""*SELECT*_1"".origin_lon, ""*SELECT*_1"".origin_lat, ""*SELECT*_1"".destination_lon, ""*SELECT*_1"".destination_lat, nextval('missing_deadhead_id_seq'::regclass)
  • Buffers: shared hit=19,110, temp read=3,686 written=1
21. 145.116 246.820 ↓ 95.5 19,110 1

HashAggregate (cost=6,954,978,047,896.01..6,954,978,047,898.01 rows=200 width=32) (actual time=240.648..246.820 rows=19,110 loops=1)

  • Output: (l2_1.destination_point[0]), (l2_1.destination_point[1]), ('-95.2335999999999956'::double precision), ('29.767199999999999'::double precision)
  • Group Key: l2_1.destination_point[0], l2_1.destination_point[1], '-95.2335999999999956'::double precision, '29.767199999999999'::double precision
  • Buffers: temp read=3,686 written=1
22. 101.704 101.704 ↑ 3,440,938.5 504,051 1

CTE Scan on load_distances l2_1 (cost=0.00..6,937,633,962,988.54 rows=1,734,408,490,747 width=32) (actual time=0.037..101.704 rows=504,051 loops=1)

  • Output: l2_1.destination_point[0], l2_1.destination_point[1], '-95.2335999999999956'::double precision, '29.767199999999999'::double precision
  • Filter: (l2_1.ddh IS NULL)
  • Rows Removed by Filter: 13,874
  • Buffers: temp read=3,686 written=1
23. 4,156.014 4,156.014 ↑ 669,752,759.9 517,925 1

CTE Scan on load_distances ld_1 (cost=0.00..6,937,633,962,988.54 rows=346,881,698,149,427 width=16) (actual time=247.524..4,156.014 rows=517,925 loops=1)

  • Output: ld_1.id, ld_1.origin_point, ld_1.odh, ld_1.destination_point, ld_1.ddh
  • Buffers: shared hit=223,010, temp written=3,684
24. 0.420 307.553 ↑ 1.0 299 1

Hash (cost=40,525.32..40,525.32 rows=303 width=16) (actual time=307.553..307.553 rows=299 loops=1)

  • Output: l.id
  • Buckets: 1,024 Batches: 1 Memory Usage: 23kB
  • Buffers: shared hit=33,244
25. 1.661 307.133 ↑ 1.0 299 1

Nested Loop Left Join (cost=0.00..40,525.32 rows=303 width=16) (actual time=3.036..307.133 rows=299 loops=1)

  • Output: l.id
  • Join Filter: (l.carrier_groups ? c.carriergroupid)
  • Rows Removed by Join Filter: 5,083
  • Buffers: shared hit=33,244
26. 304.874 304.874 ↑ 1.0 299 1

Seq Scan on v5search.loads l (cost=0.00..36,504.72 rows=303 width=48) (actual time=3.005..304.874 rows=299 loops=1)

  • Output: l.id, l.carrier_groups
  • Filter: (l.dimensions_width > '18'::double precision)
  • Rows Removed by Filter: 260,630
  • Buffers: shared hit=33,243
27. 0.590 0.598 ↑ 51.8 17 299

Materialize (cost=0.00..23.20 rows=880 width=32) (actual time=0.000..0.002 rows=17 loops=299)

  • Output: c.carriergroupid
  • Buffers: shared hit=1
28. 0.008 0.008 ↑ 51.8 17 1

Seq Scan on v5search_private.carrier_group_carrier_array c (cost=0.00..18.80 rows=880 width=32) (actual time=0.005..0.008 rows=17 loops=1)

  • Output: c.carriergroupid
  • Buffers: shared hit=1
Planning time : 1.356 ms
Execution time : 5,379.107 ms