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. 26.990 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. 1.661 20,281.430 ↑ 15.0 2 3 / 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. 3.610 20,279.769 ↓ 28.2 846 3 / 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. 2.725 20,276.159 ↓ 28.2 846 3 / 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. 5.603 20,261.585 ↓ 28.2 846 3 / 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. 257.561 20,046.938 ↓ 28.2 846 3 / 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. 240.945 17,586.501 ↓ 27.1 122,382 3 / 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. 202.928 1,327.400 ↓ 27.1 30,569 3 / 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. 1,036.990 1,036.990 ↑ 1.3 152,421 3 / 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. 21.297 87.482 ↓ 27.3 10,061 3 / 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. 35.497 66.185 ↓ 27.3 10,061 3 / 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. 30.278 30.278 ↑ 1.2 50,000 3 / 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.012 0.410 ↓ 5.0 5 3 / 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.025 0.398 ↓ 5.0 5 3 / 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.181 0.181 ↑ 1.0 1 3 / 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.192 0.192 ↑ 6.8 25 3 / 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. 16,018.156 16,018.156 ↑ 4.2 4 91,707 / 3

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. 2,202.876 2,202.876 ↓ 0.0 0 367,146 / 3

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. 209.044 209.044 ↑ 1.0 1 2,539 / 3

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 5.924 ↑ 1.0 1 2,539 / 3

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. 16,018.156 16,018.156 ↑ 4.2 4 91,707 / 3

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. 2,202.876 2,202.876 ↓ 0.0 0 367,146 / 3

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. 209.044 209.044 ↑ 1.0 1 2,539 / 3

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. 5.924 5.924 ↑ 1.0 1 2,539 / 3

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