explain.depesz.com

PostgreSQL's explain analyze made readable

Result: U3uS : Optimization for: Optimization for: R2; AUTOMOBILE; lineitem

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 8.445 864.248 ↑ 21.3 15,109 1

Sort (cost=221,560.26..222,364.05 rows=321,515 width=44) (actual time=862.430..864.248 rows=15,109 loops=1)

  • Sort Key: (sum((lineitem.l_extendedprice * ('1'::numeric - lineitem.l_discount)))) DESC, orders.o_orderdate
  • Sort Method: quicksort Memory: 1565kB
2. 12.585 855.803 ↑ 21.3 15,109 1

Finalize GroupAggregate (cost=139,946.66..182,259.44 rows=321,515 width=44) (actual time=813.339..855.803 rows=15,109 loops=1)

  • Group Key: lineitem.l_orderkey, orders.o_orderdate, orders.o_shippriority
3. 0.000 843.218 ↑ 17.7 15,109 1

Gather Merge (cost=139,946.66..174,891.38 rows=267,930 width=44) (actual time=813.318..843.218 rows=15,109 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
4. 45.198 2,323.281 ↑ 26.6 5,036 3

Partial GroupAggregate (cost=138,946.63..142,965.58 rows=133,965 width=44) (actual time=758.449..774.427 rows=5,036 loops=3)

  • Group Key: lineitem.l_orderkey, orders.o_orderdate, orders.o_shippriority
5. 30.954 2,278.083 ↑ 8.9 15,012 3

Sort (cost=138,946.63..139,281.54 rows=133,965 width=24) (actual time=758.415..759.361 rows=15,012 loops=3)

  • Sort Key: lineitem.l_orderkey, orders.o_orderdate, orders.o_shippriority
  • Sort Method: quicksort Memory: 2056kB
6. 43.062 2,247.129 ↑ 8.9 15,012 3

Nested Loop (cost=5,006.18..124,791.01 rows=133,965 width=24) (actual time=71.311..749.043 rows=15,012 loops=3)

7. 268.428 1,178.028 ↑ 1.2 48,859 3

Hash Join (cost=5,005.74..39,729.31 rows=61,025 width=12) (actual time=70.150..392.676 rows=48,859 loops=3)

  • Hash Cond: (orders.o_custkey = customer.c_custkey)
8. 700.512 700.512 ↑ 1.3 245,722 3

Parallel Seq Scan on orders (cost=0.00..33,907.50 rows=310,875 width=16) (actual time=0.144..233.504 rows=245,722 loops=3)

  • Filter: (o_orderdate < '1995-03-31'::date)
  • Rows Removed by Filter: 254278
9. 30.591 209.088 ↓ 1.0 29,752 3

Hash (cost=4,637.68..4,637.68 rows=29,445 width=4) (actual time=69.695..69.696 rows=29,752 loops=3)

  • Buckets: 32768 Batches: 1 Memory Usage: 1302kB
10. 161.868 178.497 ↓ 1.0 29,752 3

Bitmap Heap Scan on customer (cost=684.62..4,637.68 rows=29,445 width=4) (actual time=6.237..59.499 rows=29,752 loops=3)

  • Recheck Cond: (c_mktsegment = 'AUTOMOBILE'::bpchar)
  • Heap Blocks: exact=3585
11. 16.629 16.629 ↓ 1.0 29,752 3

Bitmap Index Scan on index_customer_segment (cost=0.00..677.26 rows=29,445 width=0) (actual time=5.543..5.543 rows=29,752 loops=3)

  • Index Cond: (c_mktsegment = 'AUTOMOBILE'::bpchar)
12. 1,026.039 1,026.039 ↓ 0.0 0 146,577

Index Scan using index_lineitem_orderdate on lineitem (cost=0.43..1.30 rows=9 width=16) (actual time=0.007..0.007 rows=0 loops=146,577)

  • Index Cond: ((l_orderkey = orders.o_orderkey) AND (l_shipdate > '1995-03-01'::date))