explain.depesz.com

PostgreSQL's explain analyze made readable

Result: lvvH

Settings
# exclusive inclusive rows x rows loops node
1. 1.252 73,024.884 ↑ 1.0 1 1

Limit (cost=1,852,374.03..1,852,374.17 rows=1 width=248) (actual time=73,023.633..73,024.884 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=6547848 read=1180942
  • I/O Timings: read=173901.032
2. 0.000 73,023.632 ↑ 1,270.0 1 1

Finalize GroupAggregate (cost=1,852,374.03..1,852,550.12 rows=1,270 width=248) (actual time=73,023.632..73,023.632 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=2219386 read=400973
  • I/O Timings: read=58028.150
3. 0.000 73,024.860 ↑ 264.5 4 1

Gather Merge (cost=1,852,374.03..1,852,514.67 rows=1,058 width=248) (actual time=73,020.568..73,024.860 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=6547848 read=1180942
  • I/O Timings: read=173901.032
4. 36.408 218,988.069 ↑ 176.3 3 3

Partial GroupAggregate (cost=1,851,374.01..1,851,392.52 rows=529 width=248) (actual time=72,986.073..72,996.023 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=6547848 read=1180942
  • I/O Timings: read=173901.032
  • Worker 0: actual time=72956.318..72969.840 rows=4 loops=1
  • Buffers: shared hit=2174071 read=392470
  • I/O Timings: read=57268.486
  • Worker 1: actual time=72981.988..72995.265 rows=4 loops=1
  • Buffers: shared hit=2154391 read=387499
  • I/O Timings: read=58604.395
5. 117.693 218,951.661 ↓ 30.9 16,370 3

Sort (cost=1,851,374.01..1,851,375.33 rows=529 width=228) (actual time=72,982.501..72,983.887 rows=16,370 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: 3394kB
  • Worker 0: Sort Method: quicksort Memory: 3348kB
  • Worker 1: Sort Method: quicksort Memory: 3313kB
  • Buffers: shared hit=6547848 read=1180942
  • I/O Timings: read=173901.032
  • Worker 0: actual time=72952.871..72954.607 rows=19646 loops=1
  • Buffers: shared hit=2174071 read=392470
  • I/O Timings: read=57268.486
  • Worker 1: actual time=72978.398..72980.121 rows=19393 loops=1
  • Buffers: shared hit=2154391 read=387499
  • I/O Timings: read=58604.395
6. 261.939 218,833.968 ↓ 37.2 19,669 3

Parallel Hash Join (cost=48,167.88..1,851,350.08 rows=529 width=228) (actual time=297.198..72,944.656 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=6547820 read=1180942
  • I/O Timings: read=173901.032
  • Worker 0: actual time=282.010..72918.892 rows=19646 loops=1
  • Buffers: shared hit=2174057 read=392470
  • I/O Timings: read=57268.486
  • Worker 1: actual time=291.996..72938.476 rows=19393 loops=1
  • Buffers: shared hit=2154377 read=387499
  • I/O Timings: read=58604.395
7. 2,290.311 217,774.536 ↑ 2.3 39,290 3

Nested Loop (cost=3,066.16..1,805,901.54 rows=89,442 width=128) (actual time=23.908..72,591.512 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=6510724 read=1180942
  • I/O Timings: read=173901.032
  • Worker 0: actual time=20.959..72562.042 rows=39120 loops=1
  • Buffers: shared hit=2159009 read=392470
  • I/O Timings: read=57268.486
  • Worker 1: actual time=22.392..72594.689 rows=38860 loops=1
  • Buffers: shared hit=2145394 read=387499
  • I/O Timings: read=58604.395
8. 920.058 32,290.593 ↓ 5.5 492,456 3

Parallel Hash Join (cost=3,065.72..1,621,982.92 rows=89,442 width=128) (actual time=18.381..10,763.531 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=665989 read=1104263
  • I/O Timings: read=3948.194
  • Worker 0: actual time=18.722..11186.404 rows=489798 loops=1
  • Buffers: shared hit=221254 read=366951
  • I/O Timings: read=1336.930
  • Worker 1: actual time=17.691..10646.670 rows=486932 loops=1
  • Buffers: shared hit=219078 read=362267
  • I/O Timings: read=1585.458
9. 31,317.414 31,317.414 ↑ 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.561..10,439.138 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: 6182521 Removes: 5681883
  • Buffers: shared hit=663688 read=1104263
  • I/O Timings: read=3948.194
  • Worker 0: actual time=0.008..10862.335 rows=489798 loops=1
  • Buffers: shared hit=220507 read=366951
  • I/O Timings: read=1336.930
  • Worker 1: actual time=1.644..10318.859 rows=486932 loops=1
  • Buffers: shared hit=218301 read=362267
  • I/O Timings: read=1585.458
10. 18.462 53.121 ↓ 3.9 2,700 3

Parallel Hash (cost=3,057.07..3,057.07 rows=692 width=108) (actual time=17.706..17.707 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=2301
  • Worker 0: actual time=18.637..18.638 rows=2600 loops=1
  • Buffers: shared hit=747
  • Worker 1: actual time=15.822..15.822 rows=2680 loops=1
  • Buffers: shared hit=777
11. 2.970 34.659 ↓ 3.9 2,700 3

Hash Join (cost=12.58..3,057.07 rows=692 width=108) (actual time=0.051..11.553 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=2301
  • Worker 0: actual time=0.055..12.035 rows=2600 loops=1
  • Buffers: shared hit=747
  • Worker 1: actual time=0.049..11.437 rows=2680 loops=1
  • Buffers: shared hit=777
12. 31.626 31.626 ↑ 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..10.542 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: 33773 Removes: 30953
  • Buffers: shared hit=2298
  • Worker 0: actual time=0.005..11.210 rows=2601 loops=1
  • Buffers: shared hit=746
  • Worker 1: actual time=0.005..10.355 rows=2681 loops=1
  • Buffers: shared hit=776
13. 0.018 0.063 ↑ 1.0 2 3

Hash (cost=12.55..12.55 rows=2 width=108) (actual time=0.020..0.021 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.018..0.018 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. 183,193.632 183,193.632 ↓ 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.124..0.124 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: 500638 Removes: 460749
  • Buffers: shared hit=5844735 read=76679
  • I/O Timings: read=169952.838
  • Worker 0: actual time=0.124..0.124 rows=0 loops=489798
  • Buffers: shared hit=1937755 read=25519
  • I/O Timings: read=55931.557
  • Worker 1: actual time=0.126..0.126 rows=0 loops=486932
  • Buffers: shared hit=1926316 read=25232
  • I/O Timings: read=57018.938
16. 116.505 797.493 ↓ 5.5 40,093 3

Parallel Hash (cost=45,009.80..45,009.80 rows=7,354 width=108) (actual time=265.830..265.831 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=259.594..259.594 rows=48716 loops=1
  • Buffers: shared hit=15034
  • Worker 1: actual time=262.227..262.227 rows=29010 loops=1
  • Buffers: shared hit=8969
17. 67.917 680.988 ↓ 5.5 40,093 3

Hash Join (cost=12.58..45,009.80 rows=7,354 width=108) (actual time=0.047..226.996 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.050..222.958 rows=48716 loops=1
  • Buffers: shared hit=15034
  • Worker 1: actual time=0.047..223.190 rows=29010 loops=1
  • Buffers: shared hit=8969
18. 613.008 613.008 ↑ 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..204.336 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: 528612 Removes: 486058
  • Buffers: shared hit=37065
  • Worker 0: actual time=0.005..199.832 rows=48717 loops=1
  • Buffers: shared hit=15033
  • Worker 1: actual time=0.005..203.790 rows=29010 loops=1
  • Buffers: shared hit=8968
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.019..0.020 rows=2 loops=1
  • Buffers: shared hit=1
  • Worker 1: actual time=0.024..0.024 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.015 rows=2 loops=1
  • Buffers: shared hit=1
  • Worker 1: actual time=0.014..0.019 rows=2 loops=1
  • Buffers: shared hit=1