explain.depesz.com

PostgreSQL's explain analyze made readable

Result: a9Ru

Settings
# exclusive inclusive rows x rows loops node
1. 10.934 33,570.124 ↑ 1.0 1 1

Limit (cost=1,852,374.03..1,852,374.17 rows=1 width=248) (actual time=33,559.192..33,570.124 rows=1 loops=1)

  • Output: n1.n_name, n2.n_name, (date_part('year'::text, (lineitem.l_shipdate)::timestamp without time zone)), (sum((lineitem.l_extendedprice * ('1'::numeric - lineitem.l_discount))))
  • Buffers: shared hit=6647421 read=1056760
  • I/O Timings: read=151.436
2. 0.000 33,559.190 ↑ 1,270.0 1 1

Finalize GroupAggregate (cost=1,852,374.03..1,852,550.12 rows=1,270 width=248) (actual time=33,559.190..33,559.190 rows=1 loops=1)

  • Output: n1.n_name, n2.n_name, (date_part('year'::text, (lineitem.l_shipdate)::timestamp without time zone)), sum((lineitem.l_extendedprice * ('1'::numeric - lineitem.l_discount)))
  • Group Key: n1.n_name, n2.n_name, (date_part('year'::text, (lineitem.l_shipdate)::timestamp without time zone))
  • Buffers: shared hit=2237826 read=352597
  • I/O Timings: read=54.102
3. 0.000 33,570.095 ↑ 264.5 4 1

Gather Merge (cost=1,852,374.03..1,852,514.67 rows=1,058 width=248) (actual time=33,547.206..33,570.095 rows=4 loops=1)

  • Output: n1.n_name, n2.n_name, (date_part('year'::text, (lineitem.l_shipdate)::timestamp without time zone)), (PARTIAL sum((lineitem.l_extendedprice * ('1'::numeric - lineitem.l_discount))))
  • Workers Planned: 2
  • Workers Launched: 2
  • Buffers: shared hit=6647421 read=1056760
  • I/O Timings: read=151.436
4. 54.963 100,648.167 ↑ 176.3 3 3

Partial GroupAggregate (cost=1,851,374.01..1,851,392.52 rows=529 width=248) (actual time=33,534.098..33,549.389 rows=3 loops=3)

  • Output: n1.n_name, n2.n_name, (date_part('year'::text, (lineitem.l_shipdate)::timestamp without time zone)), PARTIAL sum((lineitem.l_extendedprice * ('1'::numeric - lineitem.l_discount)))
  • Group Key: n1.n_name, n2.n_name, (date_part('year'::text, (lineitem.l_shipdate)::timestamp without time zone))
  • Buffers: shared hit=6647421 read=1056760
  • I/O Timings: read=151.436
  • Worker 0: actual time=33528.896..33547.004 rows=3 loops=1
  • Buffers: shared hit=2210960 read=353939
  • I/O Timings: read=25.951
  • Worker 1: actual time=33537.837..33555.576 rows=4 loops=1
  • Buffers: shared hit=2198635 read=350224
  • I/O Timings: read=71.383
5. 191.976 100,593.204 ↓ 27.9 14,745 3

Sort (cost=1,851,374.01..1,851,375.33 rows=529 width=228) (actual time=33,528.073..33,531.068 rows=14,745 loops=3)

  • Output: n1.n_name, n2.n_name, (date_part('year'::text, (lineitem.l_shipdate)::timestamp without time zone)), lineitem.l_extendedprice, lineitem.l_discount
  • Sort Key: n1.n_name, n2.n_name, (date_part('year'::text, (lineitem.l_shipdate)::timestamp without time zone))
  • Sort Method: quicksort Memory: 3377kB
  • Worker 0: Sort Method: quicksort Memory: 3349kB
  • Worker 1: Sort Method: quicksort Memory: 3329kB
  • Buffers: shared hit=6647421 read=1056760
  • I/O Timings: read=151.436
  • Worker 0: actual time=33522.944..33524.594 rows=14780 loops=1
  • Buffers: shared hit=2210960 read=353939
  • I/O Timings: read=25.951
  • Worker 1: actual time=33531.908..33534.106 rows=19505 loops=1
  • Buffers: shared hit=2198635 read=350224
  • I/O Timings: read=71.383
6. 443.907 100,401.228 ↓ 37.2 19,669 3

Parallel Hash Join (cost=48,167.88..1,851,350.08 rows=529 width=228) (actual time=338.862..33,467.076 rows=19,669 loops=3)

  • Output: n1.n_name, n2.n_name, date_part('year'::text, (lineitem.l_shipdate)::timestamp without time zone), lineitem.l_extendedprice, lineitem.l_discount
  • Hash Cond: (orders.o_custkey = customer.c_custkey)
  • Join Filter: (((n1.n_name = 'RUSSIA'::bpchar) AND (n2.n_name = 'INDIA'::bpchar)) OR ((n1.n_name = 'INDIA'::bpchar) AND (n2.n_name = 'RUSSIA'::bpchar)))
  • SemiJoin Filter SJF1 Created Based on: (orders.o_custkey = customer.c_custkey)
  • SemiJoin Est Selectivity: 0.08222
  • SemiJoin Est Rows Filtered: 13766227.0
  • SemiJoin Est Outer Distincts: 89442.0
  • SemiJoin Est Inner Distincts: 7354.0
  • Rows Removed by Join Filter: 19608
  • Buffers: shared hit=6647393 read=1056760
  • I/O Timings: read=151.436
  • Worker 0: actual time=330.406..33443.682 rows=19654 loops=1
  • Buffers: shared hit=2210946 read=353939
  • I/O Timings: read=25.951
  • Worker 1: actual time=340.182..33474.621 rows=19505 loops=1
  • Buffers: shared hit=2198621 read=350224
  • I/O Timings: read=71.383
7. 3,800.682 99,061.065 ↑ 2.3 39,290 3

Nested Loop (cost=3,066.16..1,805,901.54 rows=89,442 width=128) (actual time=37.930..33,020.355 rows=39,290 loops=3)

  • Output: lineitem.l_shipdate, lineitem.l_extendedprice, lineitem.l_discount, orders.o_custkey, n1.n_name
  • Inner Unique: true
  • Buffers: shared hit=6610291 read=1056760
  • I/O Timings: read=151.436
  • Worker 0: actual time=38.867..32982.975 rows=39321 loops=1
  • Buffers: shared hit=2197946 read=353939
  • I/O Timings: read=25.951
  • Worker 1: actual time=38.752..33045.808 rows=38929 loops=1
  • Buffers: shared hit=2184518 read=350224
  • I/O Timings: read=71.383
8. 1,693.713 61,280.919 ↓ 5.5 492,456 3

Parallel Hash Join (cost=3,065.72..1,621,982.92 rows=89,442 width=128) (actual time=32.579..20,426.973 rows=492,456 loops=3)

  • Output: lineitem.l_shipdate, lineitem.l_extendedprice, lineitem.l_discount, lineitem.l_orderkey, n1.n_name
  • Hash Cond: (lineitem.l_suppkey = supplier.s_suppkey)
  • SemiJoin Filter SJF2 Created Based on: (lineitem.l_suppkey = supplier.s_suppkey)
  • SemiJoin Est Selectivity: 0.00706
  • SemiJoin Est Rows Filtered: 18117326.8
  • SemiJoin Est Outer Distincts: 98056.0
  • SemiJoin Est Inner Distincts: 692.0
  • Buffers: shared hit=688912 read=1056722
  • I/O Timings: read=39.431
  • Worker 0: actual time=32.240..20474.640 rows=491338 loops=1
  • Buffers: shared hit=228554 read=353931
  • I/O Timings: read=4.629
  • Worker 1: actual time=33.285..20376.118 rows=488437 loops=1
  • Buffers: shared hit=226828 read=350210
  • I/O Timings: read=27.932
9. 59,490.891 59,490.891 ↑ 15.4 492,456 3

Parallel Seq Scan on public.lineitem (cost=0.00..1,589,881.57 rows=7,602,539 width=32) (actual time=0.374..19,830.297 rows=492,456 loops=3)

  • Output: lineitem.l_orderkey, lineitem.l_partkey, lineitem.l_suppkey, lineitem.l_linenumber, lineitem.l_quantity, lineitem.l_extendedprice, lineitem.l_discount, lineitem.l_tax, lineitem.l_returnflag, lineitem.l_linestatus, lineitem.l_shipdate, lineitem.l_commitdate, lineitem.l_receiptdate, lineitem.l_shipinstruct, lineitem.l_shipmode, lineitem.l_comment
  • Filter: ((lineitem.l_shipdate >= '1995-01-01'::date) AND (lineitem.l_shipdate <= '1996-12-31'::date))
  • Rows Removed by Filter: 13918576
  • SemiJoin Filter Use: SJF2 Type: HashTable Lookups: 6136717 Removes: 5639124
  • Buffers: shared hit=686766 read=1056414
  • I/O Timings: read=36.246
  • Worker 0: actual time=0.039..19878.928 rows=491338 loops=1
  • Buffers: shared hit=227821 read=353843
  • I/O Timings: read=4.629
  • Worker 1: actual time=1.073..19826.249 rows=488437 loops=1
  • Buffers: shared hit=226108 read=350096
  • I/O Timings: read=27.932
10. 24.954 96.315 ↓ 3.9 2,700 3

Parallel Hash (cost=3,057.07..3,057.07 rows=692 width=108) (actual time=32.105..32.105 rows=2,700 loops=3)

  • Output: supplier.s_suppkey, n1.n_name
  • Buckets: 8192 (originally 2048) Batches: 1 (originally 1) Memory Usage: 688kB
  • Buffers: shared hit=2146 read=308
  • I/O Timings: read=3.185
  • Worker 0: actual time=32.167..32.167 rows=2725 loops=1
  • Buffers: shared hit=733 read=88
  • Worker 1: actual time=32.009..32.009 rows=2685 loops=1
  • Buffers: shared hit=720 read=114
11. 2.868 71.361 ↓ 3.9 2,700 3

Hash Join (cost=12.58..3,057.07 rows=692 width=108) (actual time=0.057..23.787 rows=2,700 loops=3)

  • Output: supplier.s_suppkey, n1.n_name
  • Inner Unique: true
  • Hash Cond: (supplier.s_nationkey = n1.n_nationkey)
  • SemiJoin Filter SJF3 Created Based on: (supplier.s_nationkey = n1.n_nationkey)
  • SemiJoin Est Selectivity: 0.08000
  • SemiJoin Est Rows Filtered: 92000.0
  • SemiJoin Est Outer Distincts: 25.0
  • SemiJoin Est Inner Distincts: 2.0
  • Buffers: shared hit=2146 read=308
  • I/O Timings: read=3.185
  • Worker 0: actual time=0.053..22.011 rows=2725 loops=1
  • Buffers: shared hit=733 read=88
  • Worker 1: actual time=0.055..22.487 rows=2685 loops=1
  • Buffers: shared hit=720 read=114
12. 68.433 68.433 ↑ 21.8 2,701 3

Parallel Seq Scan on public.supplier (cost=0.00..2,886.24 rows=58,824 width=12) (actual time=0.006..22.811 rows=2,701 loops=3)

  • Output: supplier.s_suppkey, supplier.s_name, supplier.s_address, supplier.s_nationkey, supplier.s_phone, supplier.s_acctbal, supplier.s_comment
  • SemiJoin Filter Use: SJF3 Type: HashTable Lookups: 32474 Removes: 29784
  • Buffers: shared hit=2143 read=308
  • I/O Timings: read=3.185
  • Worker 0: actual time=0.006..20.744 rows=2726 loops=1
  • Buffers: shared hit=732 read=88
  • Worker 1: actual time=0.004..21.730 rows=2686 loops=1
  • Buffers: shared hit=719 read=114
13. 0.015 0.060 ↑ 1.0 2 3

Hash (cost=12.55..12.55 rows=2 width=108) (actual time=0.020..0.020 rows=2 loops=3)

  • Output: n1.n_name, n1.n_nationkey
  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=3
  • Worker 0: actual time=0.017..0.017 rows=2 loops=1
  • Buffers: shared hit=1
  • Worker 1: actual time=0.017..0.017 rows=2 loops=1
  • Buffers: shared hit=1
14. 0.045 0.045 ↑ 1.0 2 3

Seq Scan on public.nation n1 (cost=0.00..12.55 rows=2 width=108) (actual time=0.010..0.015 rows=2 loops=3)

  • Output: n1.n_name, n1.n_nationkey
  • Filter: ((n1.n_name = 'RUSSIA'::bpchar) OR (n1.n_name = 'INDIA'::bpchar))
  • Rows Removed by Filter: 23
  • Buffers: shared hit=3
  • Worker 0: actual time=0.008..0.012 rows=2 loops=1
  • Buffers: shared hit=1
  • Worker 1: actual time=0.008..0.013 rows=2 loops=1
  • Buffers: shared hit=1
15. 33,979.464 33,979.464 ↓ 0.0 0 1,477,368

Index Scan using orders_pkey on public.orders (cost=0.43..2.06 rows=1 width=12) (actual time=0.023..0.023 rows=0 loops=1,477,368)

  • Output: orders.o_orderkey, orders.o_custkey, orders.o_orderstatus, orders.o_totalprice, orders.o_orderdate, orders.o_orderpriority, orders.o_clerk, orders.o_shippriority, orders.o_comment
  • Index Cond: (orders.o_orderkey = lineitem.l_orderkey)
  • SemiJoin Filter Use: SJF1 Type: HashTable Lookups: 497593 Removes: 457974
  • Buffers: shared hit=5921379 read=38
  • I/O Timings: read=112.005
  • Worker 0: actual time=0.023..0.023 rows=0 loops=491338
  • Buffers: shared hit=1969392 read=8
  • I/O Timings: read=21.323
  • Worker 1: actual time=0.023..0.023 rows=0 loops=488437
  • Buffers: shared hit=1957690 read=14
  • I/O Timings: read=43.451
16. 115.680 896.256 ↓ 5.5 40,093 3

Parallel Hash (cost=45,009.80..45,009.80 rows=7,354 width=108) (actual time=298.752..298.752 rows=40,093 loops=3)

  • Output: customer.c_custkey, n2.n_name
  • Buckets: 131072 (originally 32768) Batches: 1 (originally 1) Memory Usage: 9376kB
  • Buffers: shared hit=37068
  • Worker 0: actual time=290.345..290.345 rows=42077 loops=1
  • Buffers: shared hit=12986
  • Worker 1: actual time=298.841..298.841 rows=45884 loops=1
  • Buffers: shared hit=14089
17. 46.998 780.576 ↓ 5.5 40,093 3

Hash Join (cost=12.58..45,009.80 rows=7,354 width=108) (actual time=0.053..260.192 rows=40,093 loops=3)

  • Output: customer.c_custkey, n2.n_name
  • Inner Unique: true
  • Hash Cond: (customer.c_nationkey = n2.n_nationkey)
  • SemiJoin Filter SJF4 Created Based on: (customer.c_nationkey = n2.n_nationkey)
  • SemiJoin Est Selectivity: 0.08000
  • SemiJoin Est Rows Filtered: 1380120.5
  • SemiJoin Est Outer Distincts: 25.0
  • SemiJoin Est Inner Distincts: 2.0
  • Buffers: shared hit=37068
  • Worker 0: actual time=0.046..251.165 rows=42077 loops=1
  • Buffers: shared hit=12986
  • Worker 1: actual time=0.050..267.563 rows=45884 loops=1
  • Buffers: shared hit=14089
18. 733.515 733.515 ↑ 15.6 40,094 3

Parallel Seq Scan on public.customer (cost=0.00..43,315.55 rows=625,055 width=12) (actual time=0.006..244.505 rows=40,094 loops=3)

  • Output: customer.c_custkey, customer.c_name, customer.c_address, customer.c_nationkey, customer.c_phone, customer.c_acctbal, customer.c_mktsegment, customer.c_comment
  • SemiJoin Filter Use: SJF4 Type: HashTable Lookups: 404387 Removes: 372068
  • Buffers: shared hit=37065
  • Worker 0: actual time=0.004..234.795 rows=42078 loops=1
  • Buffers: shared hit=12985
  • Worker 1: actual time=0.005..249.376 rows=45885 loops=1
  • Buffers: shared hit=14088
19. 0.012 0.063 ↑ 1.0 2 3

Hash (cost=12.55..12.55 rows=2 width=108) (actual time=0.021..0.021 rows=2 loops=3)

  • Output: n2.n_name, n2.n_nationkey
  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=3
  • Worker 0: actual time=0.020..0.020 rows=2 loops=1
  • Buffers: shared hit=1
  • Worker 1: actual time=0.023..0.023 rows=2 loops=1
  • Buffers: shared hit=1
20. 0.051 0.051 ↑ 1.0 2 3

Seq Scan on public.nation n2 (cost=0.00..12.55 rows=2 width=108) (actual time=0.012..0.017 rows=2 loops=3)

  • Output: n2.n_name, n2.n_nationkey
  • Filter: ((n2.n_name = 'INDIA'::bpchar) OR (n2.n_name = 'RUSSIA'::bpchar))
  • Rows Removed by Filter: 23
  • Buffers: shared hit=3
  • Worker 0: actual time=0.011..0.016 rows=2 loops=1
  • Buffers: shared hit=1
  • Worker 1: actual time=0.013..0.018 rows=2 loops=1
  • Buffers: shared hit=1