explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 5yKH

Settings
# exclusive inclusive rows x rows loops node
1. 0.003 13,910.239 ↑ 1.0 1 1

Limit (cost=269,117.27..269,117.31 rows=1 width=40) (actual time=13,910.238..13,910.239 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)))))
2. 2.377 13,910.236 ↑ 72.0 1 1

GroupAggregate (cost=269,117.27..269,120.51 rows=72 width=40) (actual time=13,910.236..13,910.236 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))
3. 6.554 13,907.859 ↓ 17.6 1,269 1

Sort (cost=269,117.27..269,117.45 rows=72 width=124) (actual time=13,907.341..13,907.859 rows=1,269 loops=1)

  • 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: 315kB
4. 10.800 13,901.305 ↓ 35.3 2,539 1

Hash Join (cost=64,811.15..269,115.04 rows=72 width=124) (actual time=2,174.789..13,901.305 rows=2,539 loops=1)

  • 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)
5. 5.965 13,890.371 ↓ 35.3 2,539 1

Hash Join (cost=64,797.33..269,099.95 rows=72 width=24) (actual time=2,174.698..13,890.371 rows=2,539 loops=1)

  • Output: lineitem.l_extendedprice, lineitem.l_discount, supplier.s_nationkey, orders.o_orderdate
  • Inner Unique: true
  • Hash Cond: (lineitem.l_suppkey = supplier.s_suppkey)
6. 233.172 13,859.204 ↓ 35.3 2,539 1

Hash Join (cost=64,342.33..268,644.04 rows=72 width=24) (actual time=2,149.400..13,859.204 rows=2,539 loops=1)

  • Output: lineitem.l_extendedprice, lineitem.l_discount, lineitem.l_suppkey, orders.o_orderdate
  • Inner Unique: true
  • Hash Cond: (lineitem.l_partkey = part.p_partkey)
7. 6,049.017 13,559.394 ↓ 33.9 367,146 1

Hash Join (cost=57,726.76..261,891.85 rows=10,822 width=32) (actual time=2,073.726..13,559.394 rows=367,146 loops=1)

  • Output: lineitem.l_extendedprice, lineitem.l_discount, lineitem.l_partkey, lineitem.l_suppkey, orders.o_orderdate
  • Hash Cond: (lineitem.l_orderkey = orders.o_orderkey)
8. 5,436.797 5,436.797 ↓ 1.0 6,001,215 1

Seq Scan on public.lineitem (cost=0.00..181,553.63 rows=6,000,863 width=36) (actual time=0.025..5,436.797 rows=6,001,215 loops=1)

  • 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
9. 103.088 2,073.580 ↓ 33.9 91,707 1

Hash (cost=57,692.95..57,692.95 rows=2,705 width=8) (actual time=2,073.580..2,073.580 rows=91,707 loops=1)

  • Output: orders.o_orderdate, orders.o_orderkey
  • Buckets: 131072 (originally 4096) Batches: 2 (originally 1) Memory Usage: 3073kB
10. 461.471 1,970.492 ↓ 33.9 91,707 1

Hash Join (cost=5,816.33..57,692.95 rows=2,705 width=8) (actual time=291.403..1,970.492 rows=91,707 loops=1)

  • Output: orders.o_orderdate, orders.o_orderkey
  • Hash Cond: (orders.o_custkey = customer.c_custkey)
11. 1,217.660 1,217.660 ↑ 1.0 457,263 1

Seq Scan on public.orders (cost=0.00..50,125.00 rows=459,888 width=16) (actual time=0.018..1,217.660 rows=457,263 loops=1)

  • 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: 1042737
12. 25.625 291.361 ↓ 34.2 30,183 1

Hash (cost=5,805.31..5,805.31 rows=882 width=4) (actual time=291.361..291.361 rows=30,183 loops=1)

  • Output: customer.c_custkey
  • Buckets: 32768 (originally 1024) Batches: 1 (originally 1) Memory Usage: 1318kB
13. 120.587 265.736 ↓ 34.2 30,183 1

Hash Join (cost=25.99..5,805.31 rows=882 width=4) (actual time=0.858..265.736 rows=30,183 loops=1)

  • Output: customer.c_custkey
  • Hash Cond: (customer.c_nationkey = n1.n_nationkey)
14. 144.320 144.320 ↑ 1.0 150,000 1

Seq Scan on public.customer (cost=0.00..5,208.00 rows=150,000 width=12) (actual time=0.011..144.320 rows=150,000 loops=1)

  • 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
15. 0.007 0.829 ↓ 5.0 5 1

Hash (cost=25.97..25.97 rows=1 width=4) (actual time=0.829..0.829 rows=5 loops=1)

  • Output: n1.n_nationkey
  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
16. 0.029 0.822 ↓ 5.0 5 1

Hash Join (cost=12.14..25.97 rows=1 width=4) (actual time=0.802..0.822 rows=5 loops=1)

  • Output: n1.n_nationkey
  • Inner Unique: true
  • Hash Cond: (n1.n_regionkey = region.r_regionkey)
17. 0.016 0.016 ↑ 6.8 25 1

Seq Scan on public.nation n1 (cost=0.00..11.70 rows=170 width=12) (actual time=0.005..0.016 rows=25 loops=1)

  • Output: n1.n_nationkey, n1.n_name, n1.n_regionkey, n1.n_comment
18. 0.009 0.777 ↑ 1.0 1 1

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

  • Output: region.r_regionkey
  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
19. 0.768 0.768 ↑ 1.0 1 1

Seq Scan on public.region (cost=0.00..12.12 rows=1 width=4) (actual time=0.766..0.768 rows=1 loops=1)

  • Output: region.r_regionkey
  • Filter: (region.r_name = 'ASIA'::bpchar)
  • Rows Removed by Filter: 4
20. 1.077 66.638 ↓ 1.0 1,338 1

Hash (cost=6,599.00..6,599.00 rows=1,325 width=4) (actual time=66.638..66.638 rows=1,338 loops=1)

  • Output: part.p_partkey
  • Buckets: 2048 Batches: 1 Memory Usage: 64kB
21. 65.561 65.561 ↓ 1.0 1,338 1

Seq Scan on public.part (cost=0.00..6,599.00 rows=1,325 width=4) (actual time=0.202..65.561 rows=1,338 loops=1)

  • Output: part.p_partkey
  • Filter: ((part.p_type)::text = 'LARGE BRUSHED NICKEL'::text)
  • Rows Removed by Filter: 198662
22. 9.502 25.202 ↑ 1.0 10,000 1

Hash (cost=330.00..330.00 rows=10,000 width=12) (actual time=25.202..25.202 rows=10,000 loops=1)

  • Output: supplier.s_suppkey, supplier.s_nationkey
  • Buckets: 16384 Batches: 1 Memory Usage: 597kB
23. 15.700 15.700 ↑ 1.0 10,000 1

Seq Scan on public.supplier (cost=0.00..330.00 rows=10,000 width=12) (actual time=0.009..15.700 rows=10,000 loops=1)

  • Output: supplier.s_suppkey, supplier.s_nationkey
24. 0.000 0.067 ↑ 6.8 25 1

Hash (cost=11.70..11.70 rows=170 width=108) (actual time=0.067..0.067 rows=25 loops=1)

  • Output: n2.n_name, n2.n_nationkey
  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
25. 0.000 0.036 ↑ 6.8 25 1

Seq Scan on public.nation n2 (cost=0.00..11.70 rows=170 width=108) (actual time=0.014..0.036 rows=25 loops=1)

  • Output: n2.n_name, n2.n_nationkey
  • 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
26. 0.007 0.829 ↓ 5.0 5 1

Hash (cost=25.97..25.97 rows=1 width=4) (actual time=0.829..0.829 rows=5 loops=1)

  • Output: n1.n_nationkey
  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
27. 0.029 0.822 ↓ 5.0 5 1

Hash Join (cost=12.14..25.97 rows=1 width=4) (actual time=0.802..0.822 rows=5 loops=1)

  • Output: n1.n_nationkey
  • Inner Unique: true
  • Hash Cond: (n1.n_regionkey = region.r_regionkey)
28. 0.016 0.016 ↑ 6.8 25 1

Seq Scan on public.nation n1 (cost=0.00..11.70 rows=170 width=12) (actual time=0.005..0.016 rows=25 loops=1)

  • Output: n1.n_nationkey, n1.n_name, n1.n_regionkey, n1.n_comment
29. 0.009 0.777 ↑ 1.0 1 1

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

  • Output: region.r_regionkey
  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
30. 0.768 0.768 ↑ 1.0 1 1

Seq Scan on public.region (cost=0.00..12.12 rows=1 width=4) (actual time=0.766..0.768 rows=1 loops=1)

  • Output: region.r_regionkey
  • Filter: (region.r_name = 'ASIA'::bpchar)
  • Rows Removed by Filter: 4
31. 1.077 66.638 ↓ 1.0 1,338 1

Hash (cost=6,599.00..6,599.00 rows=1,325 width=4) (actual time=66.638..66.638 rows=1,338 loops=1)

  • Output: part.p_partkey
  • Buckets: 2048 Batches: 1 Memory Usage: 64kB
32. 65.561 65.561 ↓ 1.0 1,338 1

Seq Scan on public.part (cost=0.00..6,599.00 rows=1,325 width=4) (actual time=0.202..65.561 rows=1,338 loops=1)

  • Output: part.p_partkey
  • Filter: ((part.p_type)::text = 'LARGE BRUSHED NICKEL'::text)
  • Rows Removed by Filter: 198662
33. 9.502 25.202 ↑ 1.0 10,000 1

Hash (cost=330.00..330.00 rows=10,000 width=12) (actual time=25.202..25.202 rows=10,000 loops=1)

  • Output: supplier.s_suppkey, supplier.s_nationkey
  • Buckets: 16384 Batches: 1 Memory Usage: 597kB
34. 15.700 15.700 ↑ 1.0 10,000 1

Seq Scan on public.supplier (cost=0.00..330.00 rows=10,000 width=12) (actual time=0.009..15.700 rows=10,000 loops=1)

  • Output: supplier.s_suppkey, supplier.s_nationkey
35. 0.031 0.067 ↑ 6.8 25 1

Hash (cost=11.70..11.70 rows=170 width=108) (actual time=0.067..0.067 rows=25 loops=1)

  • Output: n2.n_name, n2.n_nationkey
  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
36. 0.036 0.036 ↑ 6.8 25 1

Seq Scan on public.nation n2 (cost=0.00..11.70 rows=170 width=108) (actual time=0.014..0.036 rows=25 loops=1)

  • Output: n2.n_name, n2.n_nationkey
Planning time : 13.793 ms
Execution time : 13,912.022 ms