explain.depesz.com

PostgreSQL's explain analyze made readable

Result: okCk

Settings
# exclusive inclusive rows x rows loops node
1. 0.003 20,308.448 ↑ 1.0 1 1

Limit (cost=47,770.09..47,770.24 rows=1 width=40) (actual time=20,308.448..20,308.448 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. 0.025 20,308.445 ↑ 72.0 1 1

Finalize GroupAggregate (cost=47,770.09..47,780.66 rows=72 width=40) (actual time=20,308.445..20,308.445 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. 0.000 20,308.420 ↑ 15.0 4 1

Gather Merge (cost=47,770.09..47,778.29 rows=60 width=72) (actual time=20,307.455..20,308.420 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
4. 4.983 60,844.290 ↑ 15.0 2 3

Partial GroupAggregate (cost=46,770.07..46,771.34 rows=30 width=72) (actual time=20,280.445..20,281.430 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))
  • Worker 0: actual time=20239.874..20240.888 rows=2 loops=1
  • Worker 1: actual time=20294.838..20295.815 rows=2 loops=1
5. 10.830 60,839.307 ↓ 28.2 846 3

Sort (cost=46,770.07..46,770.14 rows=30 width=124) (actual time=20,279.391..20,279.769 rows=846 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: 99kB
  • Worker 0: Sort Method: quicksort Memory: 96kB
  • Worker 1: Sort Method: quicksort Memory: 96kB
  • Worker 0: actual time=20238.755..20239.163 rows=835 loops=1
  • Worker 1: actual time=20293.848..20294.215 rows=836 loops=1
6. 8.176 60,828.477 ↓ 28.2 846 3

Nested Loop (cost=4,602.90..46,769.33 rows=30 width=124) (actual time=283.936..20,276.159 rows=846 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
  • Worker 0: actual time=280.061..20235.551 rows=835 loops=1
  • Worker 1: actual time=103.865..20290.572 rows=836 loops=1
7. 16.808 60,784.755 ↓ 28.2 846 3

Nested Loop (cost=4,602.76..46,764.16 rows=30 width=24) (actual time=283.726..20,261.585 rows=846 loops=3)

  • Output: lineitem.l_extendedprice, lineitem.l_discount, supplier.s_nationkey, orders.o_orderdate
  • Inner Unique: true
  • Worker 0: actual time=280.005..20221.342 rows=835 loops=1
  • Worker 1: actual time=103.338..20275.779 rows=836 loops=1
8. 772.683 60,140.814 ↓ 28.2 846 3

Nested Loop (cost=4,602.47..46,755.08 rows=30 width=24) (actual time=282.892..20,046.938 rows=846 loops=3)

  • Output: lineitem.l_extendedprice, lineitem.l_discount, lineitem.l_suppkey, orders.o_orderdate
  • Inner Unique: true
  • Worker 0: actual time=278.948..20004.144 rows=835 loops=1
  • Worker 1: actual time=102.808..20063.825 rows=836 loops=1
9. 722.835 52,759.503 ↓ 27.1 122,382 3

Nested Loop (cost=4,602.05..44,757.15 rows=4,509 width=32) (actual time=89.118..17,586.501 rows=122,382 loops=3)

  • Output: lineitem.l_extendedprice, lineitem.l_discount, lineitem.l_partkey, lineitem.l_suppkey, orders.o_orderdate
  • Worker 0: actual time=84.885..17533.627 rows=122197 loops=1
  • Worker 1: actual time=86.777..17589.306 rows=123108 loops=1
10. 608.784 3,982.200 ↓ 27.1 30,569 3

Parallel Hash Join (cost=4,601.62..42,324.89 rows=1,127 width=8) (actual time=88.249..1,327.400 rows=30,569 loops=3)

  • Output: orders.o_orderdate, orders.o_orderkey
  • Hash Cond: (orders.o_custkey = customer.c_custkey)
  • Worker 0: actual time=84.734..1326.635 rows=30516 loops=1
  • Worker 1: actual time=85.533..1236.281 rows=30833 loops=1
11. 3,110.970 3,110.970 ↑ 1.3 152,421 3

Parallel Seq Scan on public.orders (cost=0.00..37,000.00 rows=191,620 width=16) (actual time=0.582..1,036.990 rows=152,421 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: 347579
  • Worker 0: actual time=0.046..1040.752 rows=151656 loops=1
  • Worker 1: actual time=0.836..948.910 rows=152994 loops=1
12. 63.891 262.446 ↓ 27.3 10,061 3

Parallel Hash (cost=4,597.02..4,597.02 rows=368 width=4) (actual time=87.482..87.482 rows=10,061 loops=3)

  • Output: customer.c_custkey
  • Buckets: 32768 (originally 1024) Batches: 1 (originally 1) Memory Usage: 1720kB
  • Worker 0: actual time=84.568..84.568 rows=9111 loops=1
  • Worker 1: actual time=84.568..84.568 rows=10533 loops=1
13. 106.491 198.555 ↓ 27.3 10,061 3

Hash Join (cost=25.96..4,597.02 rows=368 width=4) (actual time=0.445..66.185 rows=10,061 loops=3)

  • Output: customer.c_custkey
  • Hash Cond: (customer.c_nationkey = n1.n_nationkey)
  • Worker 0: actual time=0.102..60.883 rows=9111 loops=1
  • Worker 1: actual time=0.106..67.958 rows=10533 loops=1
14. 90.834 90.834 ↑ 1.2 50,000 3

Parallel Seq Scan on public.customer (cost=0.00..4,333.00 rows=62,500 width=12) (actual time=0.009..30.278 rows=50,000 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
  • Worker 0: actual time=0.010..28.113 rows=45789 loops=1
  • Worker 1: actual time=0.010..31.134 rows=52129 loops=1
15. 0.036 1.230 ↓ 5.0 5 3

Hash (cost=25.95..25.95 rows=1 width=4) (actual time=0.410..0.410 rows=5 loops=3)

  • Output: n1.n_nationkey
  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Worker 0: actual time=0.072..0.072 rows=5 loops=1
  • Worker 1: actual time=0.071..0.071 rows=5 loops=1
16. 0.075 1.194 ↓ 5.0 5 3

Nested Loop (cost=0.00..25.95 rows=1 width=4) (actual time=0.375..0.398 rows=5 loops=3)

  • Output: n1.n_nationkey
  • Join Filter: (n1.n_regionkey = region.r_regionkey)
  • Rows Removed by Join Filter: 20
  • Worker 0: actual time=0.037..0.060 rows=5 loops=1
  • Worker 1: actual time=0.037..0.059 rows=5 loops=1
17. 0.543 0.543 ↑ 1.0 1 3

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

  • Output: region.r_regionkey, region.r_name, region.r_comment
  • Filter: (region.r_name = 'ASIA'::bpchar)
  • Rows Removed by Filter: 4
  • Worker 0: actual time=0.016..0.017 rows=1 loops=1
  • Worker 1: actual time=0.015..0.017 rows=1 loops=1
18. 0.576 0.576 ↑ 6.8 25 3

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

  • Output: n1.n_nationkey, n1.n_name, n1.n_regionkey, n1.n_comment
  • Worker 0: actual time=0.005..0.017 rows=25 loops=1
  • Worker 1: actual time=0.005..0.018 rows=25 loops=1
19. 48,054.468 48,054.468 ↑ 4.2 4 91,707

Index Scan using lineitem_pkey on public.lineitem (cost=0.43..1.99 rows=17 width=36) (actual time=0.480..0.524 rows=4 loops=91,707)

  • 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)
  • Worker 0: actual time=0.479..0.523 rows=4 loops=30516
  • Worker 1: actual time=0.478..0.522 rows=4 loops=30833
20. 6,608.628 6,608.628 ↓ 0.0 0 367,146

Index Scan using part_pkey on public.part (cost=0.42..0.44 rows=1 width=4) (actual time=0.018..0.018 rows=0 loops=367,146)

  • Output: part.p_partkey, part.p_name, part.p_mfgr, part.p_brand, part.p_type, part.p_size, part.p_container, part.p_retailprice, part.p_comment
  • Index Cond: (part.p_partkey = lineitem.l_partkey)
  • Filter: ((part.p_type)::text = 'LARGE BRUSHED NICKEL'::text)
  • Rows Removed by Filter: 1
  • Worker 0: actual time=0.018..0.018 rows=0 loops=122197
  • Worker 1: actual time=0.018..0.018 rows=0 loops=123108
21. 627.133 627.133 ↑ 1.0 1 2,539

Index Scan using supplier_pkey on public.supplier (cost=0.29..0.30 rows=1 width=12) (actual time=0.247..0.247 rows=1 loops=2,539)

  • 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)
  • Worker 0: actual time=0.254..0.254 rows=1 loops=835
  • Worker 1: actual time=0.247..0.247 rows=1 loops=836
22. 0.000 17.773 ↑ 1.0 1 2,539

Index Scan using nation_pkey on public.nation n2 (cost=0.14..0.17 rows=1 width=108) (actual time=0.007..0.007 rows=1 loops=2,539)

  • Output: n2.n_nationkey, n2.n_name, n2.n_regionkey, n2.n_comment
  • Index Cond: (n2.n_nationkey = supplier.s_nationkey)
  • Worker 0: actual time=0.007..0.007 rows=1 loops=835
  • Worker 1: actual time=0.008..0.008 rows=1 loops=836
  • Output: n1.n_nationkey, n1.n_name, n1.n_regionkey, n1.n_comment
  • Worker 0: actual time=0.005..0.017 rows=25 loops=1
  • Worker 1: actual time=0.005..0.018 rows=25 loops=1
23. 48,054.468 48,054.468 ↑ 4.2 4 91,707

Index Scan using lineitem_pkey on public.lineitem (cost=0.43..1.99 rows=17 width=36) (actual time=0.480..0.524 rows=4 loops=91,707)

  • 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)
  • Worker 0: actual time=0.479..0.523 rows=4 loops=30516
  • Worker 1: actual time=0.478..0.522 rows=4 loops=30833
24. 6,608.628 6,608.628 ↓ 0.0 0 367,146

Index Scan using part_pkey on public.part (cost=0.42..0.44 rows=1 width=4) (actual time=0.018..0.018 rows=0 loops=367,146)

  • Output: part.p_partkey, part.p_name, part.p_mfgr, part.p_brand, part.p_type, part.p_size, part.p_container, part.p_retailprice, part.p_comment
  • Index Cond: (part.p_partkey = lineitem.l_partkey)
  • Filter: ((part.p_type)::text = 'LARGE BRUSHED NICKEL'::text)
  • Rows Removed by Filter: 1
  • Worker 0: actual time=0.018..0.018 rows=0 loops=122197
  • Worker 1: actual time=0.018..0.018 rows=0 loops=123108
25. 627.133 627.133 ↑ 1.0 1 2,539

Index Scan using supplier_pkey on public.supplier (cost=0.29..0.30 rows=1 width=12) (actual time=0.247..0.247 rows=1 loops=2,539)

  • 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)
  • Worker 0: actual time=0.254..0.254 rows=1 loops=835
  • Worker 1: actual time=0.247..0.247 rows=1 loops=836
26. 17.773 17.773 ↑ 1.0 1 2,539

Index Scan using nation_pkey on public.nation n2 (cost=0.14..0.17 rows=1 width=108) (actual time=0.007..0.007 rows=1 loops=2,539)

  • Output: n2.n_nationkey, n2.n_name, n2.n_regionkey, n2.n_comment
  • Index Cond: (n2.n_nationkey = supplier.s_nationkey)
  • Worker 0: actual time=0.007..0.007 rows=1 loops=835
  • Worker 1: actual time=0.008..0.008 rows=1 loops=836
Planning time : 18.512 ms
Execution time : 20,313.639 ms