explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Pmcq : Optimization for: R2; AUTOMOBILE

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 8.093 1,314.102 ↑ 21.3 15,109 1

Sort (cost=230,104.47..230,908.27 rows=321,518 width=44) (actual time=1,312.603..1,314.102 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. 17.725 1,306.009 ↑ 21.3 15,109 1

Finalize GroupAggregate (cost=148,490.25..190,803.35 rows=321,518 width=44) (actual time=1,267.223..1,306.009 rows=15,109 loops=1)

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

Gather Merge (cost=148,490.25..183,435.23 rows=267,932 width=44) (actual time=1,267.209..1,288.284 rows=15,109 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
4. 43.593 3,664.209 ↑ 26.6 5,036 3

Partial GroupAggregate (cost=147,490.22..151,509.20 rows=133,966 width=44) (actual time=1,206.026..1,221.403 rows=5,036 loops=3)

  • Group Key: lineitem.l_orderkey, orders.o_orderdate, orders.o_shippriority
5. 31.335 3,620.616 ↑ 8.9 15,012 3

Sort (cost=147,490.22..147,825.14 rows=133,966 width=24) (actual time=1,205.993..1,206.872 rows=15,012 loops=3)

  • Sort Key: lineitem.l_orderkey, orders.o_orderdate, orders.o_shippriority
  • Sort Method: quicksort Memory: 1623kB
6. 44.478 3,589.281 ↑ 8.9 15,012 3

Nested Loop (cost=5,006.18..133,334.51 rows=133,966 width=24) (actual time=72.037..1,196.427 rows=15,012 loops=3)

7. 281.721 1,199.571 ↑ 1.2 48,859 3

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

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

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

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

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

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

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

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

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

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

Index Scan using pklineitem on lineitem (cost=0.43..1.44 rows=9 width=16) (actual time=0.015..0.016 rows=0 loops=146,577)

  • Index Cond: (l_orderkey = orders.o_orderkey)
  • Filter: (l_shipdate > '1995-03-01'::date)
  • Rows Removed by Filter: 4