explain.depesz.com

PostgreSQL's explain analyze made readable

Result: fZGA

Settings
# exclusive inclusive rows x rows loops node
1. 32.223 90,479.710 ↑ 1.0 1 1

Limit (cost=1,507,850.30..1,507,850.45 rows=1 width=40) (actual time=90,447.488..90,479.710 rows=1 loops=1)

  • Output: (date_part('year'::text, (orders.o_orderdate)::timestamp without time zone)), ((sum(CASE WHEN (n2.n_name = 'INDIA'::bpchar) THEN (lineitem.l_extendedprice * ('1'::numeric - lineitem.l_discount)) ELSE '0'::numeric END) / sum((lineitem.l_extendedprice * ('1'::numeric - lineitem.l_discount)))))
  • Buffers: shared hit=4852937 read=252089, temp read=20706 written=20768
  • I/O Timings: read=248316.067
2. 0.000 90,447.487 ↑ 640.0 1 1

Finalize GroupAggregate (cost=1,507,850.30..1,507,944.36 rows=640 width=40) (actual time=90,447.487..90,447.487 rows=1 loops=1)

  • Output: (date_part('year'::text, (orders.o_orderdate)::timestamp without time zone)), (sum(CASE WHEN (n2.n_name = 'INDIA'::bpchar) THEN (lineitem.l_extendedprice * ('1'::numeric - lineitem.l_discount)) ELSE '0'::numeric END) / sum((lineitem.l_extendedprice * ('1'::numeric - lineitem.l_discount))))
  • Group Key: (date_part('year'::text, (orders.o_orderdate)::timestamp without time zone))
  • Buffers: shared hit=1546373 read=84196, temp read=6706 written=6880
  • I/O Timings: read=82867.813
3. 0.000 90,479.677 ↑ 133.5 4 1

Gather Merge (cost=1,507,850.30..1,507,923.29 rows=534 width=72) (actual time=90,440.068..90,479.677 rows=4 loops=1)

  • Output: (date_part('year'::text, (orders.o_orderdate)::timestamp without time zone)), (PARTIAL sum(CASE WHEN (n2.n_name = 'INDIA'::bpchar) THEN (lineitem.l_extendedprice * ('1'::numeric - lineitem.l_discount)) ELSE '0'::numeric END)), (PARTIAL sum((lineitem.l_extendedprice * ('1'::numeric - lineitem.l_discount))))
  • Workers Planned: 2
  • Workers Launched: 2
  • Buffers: shared hit=4852937 read=252089, temp read=20706 written=20768
  • I/O Timings: read=248316.067
4. 40.113 271,314.852 ↑ 133.5 2 3

Partial GroupAggregate (cost=1,506,850.28..1,506,861.63 rows=267 width=72) (actual time=90,431.351..90,438.284 rows=2 loops=3)

  • Output: (date_part('year'::text, (orders.o_orderdate)::timestamp without time zone)), PARTIAL sum(CASE WHEN (n2.n_name = 'INDIA'::bpchar) THEN (lineitem.l_extendedprice * ('1'::numeric - lineitem.l_discount)) ELSE '0'::numeric END), PARTIAL sum((lineitem.l_extendedprice * ('1'::numeric - lineitem.l_discount)))
  • Group Key: (date_part('year'::text, (orders.o_orderdate)::timestamp without time zone))
  • Buffers: shared hit=4852937 read=252089, temp read=20706 written=20768
  • I/O Timings: read=248316.067
  • Worker 0: actual time=90426.025..90431.581 rows=2 loops=1
  • Buffers: shared hit=1909202 read=83914, temp read=8053 written=7484
  • I/O Timings: read=82356.217
  • Worker 1: actual time=90428.660..90436.503 rows=2 loops=1
  • Buffers: shared hit=1397362 read=83979, temp read=5947 written=6404
  • I/O Timings: read=83092.037
5. 32.100 271,274.739 ↓ 30.3 8,102 3

Sort (cost=1,506,850.28..1,506,850.95 rows=267 width=124) (actual time=90,424.050..90,424.913 rows=8,102 loops=3)

  • Output: (date_part('year'::text, (orders.o_orderdate)::timestamp without time zone)), n2.n_name, lineitem.l_extendedprice, lineitem.l_discount
  • Sort Key: (date_part('year'::text, (orders.o_orderdate)::timestamp without time zone))
  • Sort Method: quicksort Memory: 796kB
  • Worker 0: Sort Method: quicksort Memory: 1139kB
  • Worker 1: Sort Method: quicksort Memory: 733kB
  • Buffers: shared hit=4852937 read=252089, temp read=20706 written=20768
  • I/O Timings: read=248316.067
  • Worker 0: actual time=90420.603..90421.652 rows=9659 loops=1
  • Buffers: shared hit=1909202 read=83914, temp read=8053 written=7484
  • I/O Timings: read=82356.217
  • Worker 1: actual time=90420.652..90421.411 rows=6917 loops=1
  • Buffers: shared hit=1397362 read=83979, temp read=5947 written=6404
  • I/O Timings: read=83092.037
6. 59.478 271,242.639 ↓ 30.3 8,102 3

Hash Join (cost=97,247.92..1,506,839.52 rows=267 width=124) (actual time=4,328.581..90,414.213 rows=8,102 loops=3)

  • Output: date_part('year'::text, (orders.o_orderdate)::timestamp without time zone), n2.n_name, lineitem.l_extendedprice, lineitem.l_discount
  • Inner Unique: true
  • Hash Cond: (supplier.s_nationkey = n2.n_nationkey)
  • Buffers: shared hit=4852923 read=252089, temp read=20706 written=20768
  • I/O Timings: read=248316.067
  • Worker 0: actual time=4334.791..90408.813 rows=9659 loops=1
  • Buffers: shared hit=1909195 read=83914, temp read=8053 written=7484
  • I/O Timings: read=82356.217
  • Worker 1: actual time=4322.110..90411.812 rows=6917 loops=1
  • Buffers: shared hit=1397355 read=83979, temp read=5947 written=6404
  • I/O Timings: read=83092.037
7. 75.717 271,183.074 ↓ 30.3 8,102 3

Nested Loop (cost=97,234.10..1,506,823.63 rows=267 width=24) (actual time=4,328.491..90,394.358 rows=8,102 loops=3)

  • Output: lineitem.l_extendedprice, lineitem.l_discount, supplier.s_nationkey, orders.o_orderdate
  • Inner Unique: true
  • Buffers: shared hit=4852892 read=252089, temp read=20706 written=20768
  • I/O Timings: read=248316.067
  • Worker 0: actual time=4334.678..90389.596 rows=9659 loops=1
  • Buffers: shared hit=1909180 read=83914, temp read=8053 written=7484
  • I/O Timings: read=82356.217
  • Worker 1: actual time=4321.996..90396.571 rows=6917 loops=1
  • Buffers: shared hit=1397340 read=83979, temp read=5947 written=6404
  • I/O Timings: read=83092.037
8. 23.325 270,985.827 ↓ 30.3 8,102 3

Parallel Hash Join (cost=97,233.80..1,506,322.74 rows=267 width=24) (actual time=4,328.468..90,328.609 rows=8,102 loops=3)

  • Output: lineitem.l_extendedprice, lineitem.l_discount, lineitem.l_suppkey, orders.o_orderdate
  • Inner Unique: true
  • Hash Cond: (lineitem.l_partkey = part.p_partkey)
  • SemiJoin Filter SJF1 Created Based on: (lineitem.l_partkey = part.p_partkey)
  • SemiJoin Est Selectivity: 0.12359
  • SemiJoin Est Rows Filtered: 52571038.3
  • SemiJoin Est Outer Distincts: 44908.0
  • SemiJoin Est Inner Distincts: 5550.0
  • Buffers: shared hit=4779836 read=252089, temp read=20706 written=20768
  • I/O Timings: read=248316.067
  • Worker 0: actual time=4334.646..90314.981 rows=9659 loops=1
  • Buffers: shared hit=1880153 read=83914, temp read=8053 written=7484
  • I/O Timings: read=82356.217
  • Worker 1: actual time=4321.972..90337.592 rows=6917 loops=1
  • Buffers: shared hit=1376547 read=83979, temp read=5947 written=6404
  • I/O Timings: read=83092.037
9. 1,464.461 268,041.843 ↑ 5.5 8,104 3

Nested Loop (cost=45,767.11..1,454,738.16 rows=44,908 width=32) (actual time=3,354.747..89,347.281 rows=8,104 loops=3)

  • Output: lineitem.l_extendedprice, lineitem.l_discount, lineitem.l_partkey, lineitem.l_suppkey, orders.o_orderdate
  • Buffers: shared hit=4764214 read=201394, temp read=20706 written=20768
  • I/O Timings: read=248276.949
  • Worker 0: actual time=3363.816..89335.528 rows=9661 loops=1
  • Buffers: shared hit=1876293 read=67050, temp read=8053 written=7484
  • I/O Timings: read=82342.842
  • Worker 1: actual time=3351.101..89359.926 rows=6917 loops=1
  • Buffers: shared hit=1368866 read=67083, temp read=5947 written=6404
  • I/O Timings: read=83082.122
10. 1,972.668 11,083.542 ↓ 27.1 304,159 3

Parallel Hash Join (cost=45,766.54..422,941.03 rows=11,230 width=8) (actual time=3,346.215..3,694.514 rows=304,159 loops=3)

  • Output: orders.o_orderdate, orders.o_orderkey
  • Hash Cond: (orders.o_custkey = customer.c_custkey)
  • SemiJoin Filter SJF2 Created Based on: (orders.o_custkey = customer.c_custkey)
  • SemiJoin Est Selectivity: 0.00423
  • SemiJoin Est Rows Filtered: 4562283.1
  • SemiJoin Est Outer Distincts: 869783.0
  • SemiJoin Est Inner Distincts: 3677.0
  • Buffers: shared hit=311062 read=4462, temp read=20706 written=20768
  • Worker 0: actual time=3342.115..3733.860 rows=358653 loops=1
  • Buffers: shared hit=113883 read=1594, temp read=8053 written=7484
  • Worker 1: actual time=3348.764..3666.642 rows=262631 loops=1
  • Buffers: shared hit=96020 read=1374, temp read=5947 written=6404
11. 7,977.084 7,977.084 ↑ 4.8 394,516 3

Parallel Seq Scan on public.orders (cost=0.00..369,968.87 rows=1,909,022 width=16) (actual time=0.011..2,659.028 rows=394,516 loops=3)

  • 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
  • Filter: ((orders.o_orderdate >= '1995-01-01'::date) AND (orders.o_orderdate <= '1996-12-31'::date))
  • Rows Removed by Filter: 3480829
  • SemiJoin Filter Use: SJF2 Type: Bloom Size: 512kB Hashes: 4 Lookups: 1490453 Removes: 1103748 Bits Set: 19.19%
  • Buffers: shared hit=273991 read=4462
  • Worker 0: actual time=0.011..2653.729 rows=428786 loops=1
  • Buffers: shared hit=99281 read=1594
  • Worker 1: actual time=0.010..2671.578 rows=368056 loops=1
  • Buffers: shared hit=85081 read=1374
12. 290.412 1,133.790 ↓ 27.2 100,090 3

Parallel Hash (cost=45,720.58..45,720.58 rows=3,677 width=4) (actual time=377.930..377.930 rows=100,090 loops=3)

  • Output: customer.c_custkey
  • Buckets: 131072 (originally 16384) Batches: 8 (originally 1) Memory Usage: 2592kB
  • Buffers: shared hit=37071, temp written=812
  • Worker 0: actual time=377.910..377.911 rows=117754 loops=1
  • Buffers: shared hit=14602, temp written=252
  • Worker 1: actual time=377.898..377.898 rows=88817 loops=1
  • Buffers: shared hit=10939, temp written=196
13. 155.823 843.378 ↓ 27.2 100,090 3

Hash Join (cost=24.31..45,720.58 rows=3,677 width=4) (actual time=0.095..281.126 rows=100,090 loops=3)

  • Output: customer.c_custkey
  • Hash Cond: (customer.c_nationkey = n1.n_nationkey)
  • SemiJoin Filter SJF3 Created Based on: (customer.c_nationkey = n1.n_nationkey)
  • SemiJoin Est Selectivity: 0.04000
  • SemiJoin Est Rows Filtered: 1440125.8
  • SemiJoin Est Outer Distincts: 25.0
  • SemiJoin Est Inner Distincts: 1.0
  • Buffers: shared hit=37071
  • Worker 0: actual time=0.107..292.309 rows=117754 loops=1
  • Buffers: shared hit=14602
  • Worker 1: actual time=0.106..264.931 rows=88817 loops=1
  • Buffers: shared hit=10939
14. 687.369 687.369 ↑ 6.2 100,091 3

Parallel Seq Scan on public.customer (cost=0.00..43,315.55 rows=625,055 width=12) (actual time=0.006..229.123 rows=100,091 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: SJF3 Type: HashTable Lookups: 466421 Removes: 372722
  • Buffers: shared hit=37065
  • Worker 0: actual time=0.007..236.514 rows=117754 loops=1
  • Buffers: shared hit=14600
  • Worker 1: actual time=0.006..204.733 rows=88818 loops=1
  • Buffers: shared hit=10937
15. 0.015 0.186 ↓ 5.0 5 3

Hash (cost=24.29..24.29 rows=1 width=4) (actual time=0.062..0.062 rows=5 loops=3)

  • Output: n1.n_nationkey
  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=6
  • Worker 0: actual time=0.073..0.074 rows=5 loops=1
  • Buffers: shared hit=2
  • Worker 1: actual time=0.066..0.066 rows=5 loops=1
  • Buffers: shared hit=2
16. 0.078 0.171 ↓ 5.0 5 3

Hash Join (cost=12.14..24.29 rows=1 width=4) (actual time=0.049..0.057 rows=5 loops=3)

  • Output: n1.n_nationkey
  • Inner Unique: true
  • Hash Cond: (n1.n_regionkey = region.r_regionkey)
  • Buffers: shared hit=6
  • Worker 0: actual time=0.063..0.069 rows=5 loops=1
  • Buffers: shared hit=2
  • Worker 1: actual time=0.054..0.061 rows=5 loops=1
  • Buffers: shared hit=2
17. 0.021 0.021 ↑ 6.8 25 3

Seq Scan on public.nation n1 (cost=0.00..11.70 rows=170 width=12) (actual time=0.004..0.007 rows=25 loops=3)

  • Output: n1.n_nationkey, n1.n_name, n1.n_regionkey, n1.n_comment
  • Buffers: shared hit=3
  • Worker 0: actual time=0.003..0.006 rows=25 loops=1
  • Buffers: shared hit=1
  • Worker 1: actual time=0.003..0.007 rows=25 loops=1
  • Buffers: shared hit=1
18. 0.018 0.072 ↑ 1.0 1 3

Hash (cost=12.12..12.12 rows=1 width=4) (actual time=0.024..0.024 rows=1 loops=3)

  • Output: region.r_regionkey
  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=3
  • Worker 0: actual time=0.034..0.034 rows=1 loops=1
  • Buffers: shared hit=1
  • Worker 1: actual time=0.023..0.024 rows=1 loops=1
  • Buffers: shared hit=1
19. 0.054 0.054 ↑ 1.0 1 3

Seq Scan on public.region (cost=0.00..12.12 rows=1 width=4) (actual time=0.017..0.018 rows=1 loops=3)

  • Output: region.r_regionkey
  • Filter: (region.r_name = 'ASIA'::bpchar)
  • Rows Removed by Filter: 4
  • Buffers: shared hit=3
  • Worker 0: actual time=0.027..0.028 rows=1 loops=1
  • Buffers: shared hit=1
  • Worker 1: actual time=0.018..0.019 rows=1 loops=1
  • Buffers: shared hit=1
20. 255,493.840 255,493.840 ↓ 0.0 0 912,478

Index Scan using lineitem_pkey on public.lineitem (cost=0.56..90.30 rows=158 width=36) (actual time=0.280..0.280 rows=0 loops=912,478)

  • 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
  • Index Cond: (lineitem.l_orderkey = orders.o_orderkey)
  • SemiJoin Filter Use: SJF1 Type: HashTable Lookups: 1164752 Removes: 1157017
  • Buffers: shared hit=4453152 read=196932
  • I/O Timings: read=248276.949
  • Worker 0: actual time=0.237..0.238 rows=0 loops=358653
  • Buffers: shared hit=1762410 read=65456
  • I/O Timings: read=82342.842
  • Worker 1: actual time=0.325..0.325 rows=0 loops=262631
  • Buffers: shared hit=1272846 read=65709
  • I/O Timings: read=83082.122
21. 23.043 2,920.659 ↑ 1.3 4,433 3

Parallel Hash (cost=51,397.32..51,397.32 rows=5,550 width=4) (actual time=973.553..973.553 rows=4,433 loops=3)

  • Output: part.p_partkey
  • Buckets: 16384 Batches: 1 Memory Usage: 672kB
  • Buffers: shared hit=15622 read=50695
  • I/O Timings: read=39.118
  • Worker 0: actual time=970.781..970.781 rows=4015 loops=1
  • Buffers: shared hit=3860 read=16864
  • I/O Timings: read=13.375
  • Worker 1: actual time=970.755..970.755 rows=5254 loops=1
  • Buffers: shared hit=7681 read=16896
  • I/O Timings: read=9.915
22. 2,897.616 2,897.616 ↑ 1.3 4,433 3

Parallel Seq Scan on public.part (cost=0.00..51,397.32 rows=5,550 width=4) (actual time=6.168..965.872 rows=4,433 loops=3)

  • Output: part.p_partkey
  • Filter: ((part.p_type)::text = 'LARGE BRUSHED NICKEL'::text)
  • Rows Removed by Filter: 662234
  • Buffers: shared hit=15622 read=50695
  • I/O Timings: read=39.118
  • Worker 0: actual time=1.250..954.411 rows=4015 loops=1
  • Buffers: shared hit=3860 read=16864
  • I/O Timings: read=13.375
  • Worker 1: actual time=1.280..968.166 rows=5254 loops=1
  • Buffers: shared hit=7681 read=16896
  • I/O Timings: read=9.915
23. 121.530 121.530 ↑ 1.0 1 24,306

Index Scan using supplier_pkey on public.supplier (cost=0.29..1.88 rows=1 width=12) (actual time=0.005..0.005 rows=1 loops=24,306)

  • Output: supplier.s_suppkey, supplier.s_name, supplier.s_address, supplier.s_nationkey, supplier.s_phone, supplier.s_acctbal, supplier.s_comment
  • Index Cond: (supplier.s_suppkey = lineitem.l_suppkey)
  • Buffers: shared hit=73056
  • Worker 0: actual time=0.005..0.005 rows=1 loops=9659
  • Buffers: shared hit=29027
  • Worker 1: actual time=0.006..0.006 rows=1 loops=6917
  • Buffers: shared hit=20793
24. 0.036 0.087 ↑ 6.8 25 3

Hash (cost=11.70..11.70 rows=170 width=108) (actual time=0.029..0.029 rows=25 loops=3)

  • Output: n2.n_name, n2.n_nationkey
  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
  • Buffers: shared hit=3
  • Worker 0: actual time=0.029..0.030 rows=25 loops=1
  • Buffers: shared hit=1
  • Worker 1: actual time=0.030..0.030 rows=25 loops=1
  • Buffers: shared hit=1
25. 0.051 0.051 ↑ 6.8 25 3

Seq Scan on public.nation n2 (cost=0.00..11.70 rows=170 width=108) (actual time=0.011..0.017 rows=25 loops=3)

  • Output: n2.n_name, n2.n_nationkey
  • Buffers: shared hit=3
  • Worker 0: actual time=0.012..0.018 rows=25 loops=1
  • Buffers: shared hit=1
  • Worker 1: actual time=0.012..0.018 rows=25 loops=1
  • Buffers: shared hit=1
Planning time : 2.569 ms
Execution time : 90,479.938 ms