explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 03Sx

Settings
# exclusive inclusive rows x rows loops node
1. 0.003 6,856.429 ↑ 1.0 1 1

Limit (cost=204,952.45..204,952.60 rows=1 width=40) (actual time=6,856.428..6,856.429 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.030 6,856.426 ↑ 72.0 1 1

Finalize GroupAggregate (cost=204,952.45..204,963.02 rows=72 width=40) (actual time=6,856.426..6,856.426 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 6,856.396 ↑ 15.0 4 1

Gather Merge (cost=204,952.45..204,960.65 rows=60 width=72) (actual time=6,855.285..6,856.396 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.974 20,426.928 ↑ 15.0 2 3

Partial GroupAggregate (cost=203,952.43..203,953.70 rows=30 width=72) (actual time=6,807.964..6,808.976 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=6785.141..6786.033 rows=2 loops=1
  • Worker 1: actual time=6784.949..6786.270 rows=2 loops=1
5. 8.055 20,421.954 ↓ 28.2 846 3

Sort (cost=203,952.43..203,952.50 rows=30 width=124) (actual time=6,806.917..6,807.318 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: 84kB
  • Worker 0: Sort Method: quicksort Memory: 90kB
  • Worker 1: Sort Method: quicksort Memory: 141kB
  • Worker 0: actual time=6784.180..6784.546 rows=766 loops=1
  • Worker 1: actual time=6783.677..6784.185 rows=1077 loops=1
6. 5.781 20,413.899 ↓ 28.2 846 3

Hash Join (cost=198,807.93..203,951.69 rows=30 width=124) (actual time=6,780.382..6,804.633 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
  • Hash Cond: (supplier.s_nationkey = n2.n_nationkey)
  • Worker 0: actual time=6760.954..6781.182 rows=766 loops=1
  • Worker 1: actual time=6748.336..6780.444 rows=1077 loops=1
7. 3.615 20,407.911 ↓ 28.2 846 3

Hash Join (cost=198,794.10..203,937.34 rows=30 width=24) (actual time=6,779.635..6,802.637 rows=846 loops=3)

  • Output: lineitem.l_extendedprice, lineitem.l_discount, supplier.s_nationkey, orders.o_orderdate
  • Inner Unique: true
  • Hash Cond: (lineitem.l_suppkey = supplier.s_suppkey)
  • Worker 0: actual time=6759.878..6778.957 rows=766 loops=1
  • Worker 1: actual time=6747.236..6777.787 rows=1077 loops=1
8. 96.393 20,344.206 ↓ 28.2 846 3

Parallel Hash Join (cost=198,339.10..203,481.96 rows=30 width=24) (actual time=6,759.517..6,781.402 rows=846 loops=3)

  • Output: lineitem.l_extendedprice, lineitem.l_discount, lineitem.l_suppkey, orders.o_orderdate
  • Hash Cond: (part.p_partkey = lineitem.l_partkey)
  • Worker 0: actual time=6741.076..6759.136 rows=766 loops=1
  • Worker 1: actual time=6726.009..6755.184 rows=1077 loops=1
9. 98.916 98.916 ↑ 1.2 446 3

Parallel Seq Scan on public.part (cost=0.00..5,140.67 rows=552 width=4) (actual time=0.090..32.972 rows=446 loops=3)

  • 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
  • Filter: ((part.p_type)::text = 'LARGE BRUSHED NICKEL'::text)
  • Rows Removed by Filter: 66221
  • Worker 0: actual time=0.027..32.745 rows=487 loops=1
  • Worker 1: actual time=0.034..33.396 rows=424 loops=1
10. 762.789 20,148.897 ↓ 27.1 122,382 3

Parallel Hash (cost=198,282.74..198,282.74 rows=4,509 width=32) (actual time=6,716.299..6,716.299 rows=122,382 loops=3)

  • Output: lineitem.l_extendedprice, lineitem.l_discount, lineitem.l_partkey, lineitem.l_suppkey, orders.o_orderdate
  • Buckets: 65536 (originally 16384) Batches: 8 (originally 1) Memory Usage: 3808kB
  • Worker 0: actual time=6693.180..6693.180 rows=123347 loops=1
  • Worker 1: actual time=6691.221..6691.221 rows=121211 loops=1
11. 5,464.575 19,386.108 ↓ 27.1 122,382 3

Parallel Hash Join (cost=42,339.00..198,282.74 rows=4,509 width=32) (actual time=1,549.595..6,462.036 rows=122,382 loops=3)

  • Output: lineitem.l_extendedprice, lineitem.l_discount, lineitem.l_partkey, lineitem.l_suppkey, orders.o_orderdate
  • Hash Cond: (lineitem.l_orderkey = orders.o_orderkey)
  • Worker 0: actual time=1526.291..6442.281 rows=123347 loops=1
  • Worker 1: actual time=1524.922..6443.061 rows=121211 loops=1
12. 9,275.343 9,275.343 ↑ 1.2 2,000,405 3

Parallel Seq Scan on public.lineitem (cost=0.00..146,548.60 rows=2,500,360 width=36) (actual time=0.528..3,091.781 rows=2,000,405 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
  • Worker 0: actual time=0.576..3086.082 rows=1997659 loops=1
  • Worker 1: actual time=0.537..3110.736 rows=1993604 loops=1
13. 522.573 4,646.190 ↓ 27.1 30,569 3

Parallel Hash (cost=42,324.92..42,324.92 rows=1,127 width=8) (actual time=1,548.730..1,548.730 rows=30,569 loops=3)

  • Output: orders.o_orderdate, orders.o_orderkey
  • Buckets: 131072 (originally 4096) Batches: 1 (originally 1) Memory Usage: 5664kB
  • Worker 0: actual time=1525.627..1525.627 rows=36360 loops=1
  • Worker 1: actual time=1523.658..1523.658 rows=34880 loops=1
14. 498.120 4,123.617 ↓ 27.1 30,569 3

Parallel Hash Join (cost=4,601.64..42,324.92 rows=1,127 width=8) (actual time=160.006..1,374.539 rows=30,569 loops=3)

  • Output: orders.o_orderdate, orders.o_orderkey
  • Hash Cond: (orders.o_custkey = customer.c_custkey)
  • Worker 0: actual time=137.150..1309.923 rows=36360 loops=1
  • Worker 1: actual time=134.390..1329.676 rows=34880 loops=1
15. 3,147.345 3,147.345 ↑ 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.554..1,049.115 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.808..976.739 rows=181126 loops=1
  • Worker 1: actual time=0.031..1005.525 rows=174373 loops=1
16. 97.758 478.152 ↓ 27.3 10,061 3

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

  • Output: customer.c_custkey
  • Buckets: 32768 (originally 1024) Batches: 1 (originally 1) Memory Usage: 1720kB
  • Worker 0: actual time=136.287..136.287 rows=7062 loops=1
  • Worker 1: actual time=134.318..134.318 rows=6381 loops=1
17. 137.976 380.394 ↓ 27.3 10,061 3

Hash Join (cost=25.99..4,597.04 rows=368 width=4) (actual time=4.021..126.798 rows=10,061 loops=3)

  • Output: customer.c_custkey
  • Hash Cond: (customer.c_nationkey = n1.n_nationkey)
  • Worker 0: actual time=0.600..100.448 rows=7062 loops=1
  • Worker 1: actual time=10.898..112.311 rows=6381 loops=1
18. 241.647 241.647 ↑ 1.2 50,000 3

Parallel Seq Scan on public.customer (cost=0.00..4,333.00 rows=62,500 width=12) (actual time=3.731..80.549 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.458..66.573 rows=35412 loops=1
  • Worker 1: actual time=10.722..82.719 rows=31437 loops=1
19. 0.027 0.771 ↓ 5.0 5 3

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

  • Output: n1.n_nationkey
  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Worker 0: actual time=0.109..0.109 rows=5 loops=1
  • Worker 1: actual time=0.134..0.134 rows=5 loops=1
20. 0.159 0.744 ↓ 5.0 5 3

Hash Join (cost=12.14..25.97 rows=1 width=4) (actual time=0.224..0.248 rows=5 loops=3)

  • Output: n1.n_nationkey
  • Inner Unique: true
  • Hash Cond: (n1.n_regionkey = region.r_regionkey)
  • Worker 0: actual time=0.078..0.100 rows=5 loops=1
  • Worker 1: actual time=0.094..0.124 rows=5 loops=1
21. 0.063 0.063 ↑ 6.8 25 3

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

  • Output: n1.n_nationkey, n1.n_name, n1.n_regionkey, n1.n_comment
  • Worker 0: actual time=0.008..0.020 rows=25 loops=1
  • Worker 1: actual time=0.009..0.026 rows=25 loops=1
22. 0.033 0.522 ↑ 1.0 1 3

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

  • Output: region.r_regionkey
  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Worker 0: actual time=0.029..0.029 rows=1 loops=1
  • Worker 1: actual time=0.036..0.036 rows=1 loops=1
23. 0.489 0.489 ↑ 1.0 1 3

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

  • Output: region.r_regionkey
  • Filter: (region.r_name = 'ASIA'::bpchar)
  • Rows Removed by Filter: 4
  • Worker 0: actual time=0.016..0.018 rows=1 loops=1
  • Worker 1: actual time=0.020..0.023 rows=1 loops=1
24. 25.866 60.090 ↑ 1.0 10,000 3

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

  • Output: supplier.s_suppkey, supplier.s_nationkey
  • Buckets: 16384 Batches: 1 Memory Usage: 597kB
  • Worker 0: actual time=18.714..18.714 rows=10000 loops=1
  • Worker 1: actual time=21.128..21.128 rows=10000 loops=1
25. 34.224 34.224 ↑ 1.0 10,000 3

Seq Scan on public.supplier (cost=0.00..330.00 rows=10,000 width=12) (actual time=0.016..11.408 rows=10,000 loops=3)

  • Output: supplier.s_suppkey, supplier.s_nationkey
  • Worker 0: actual time=0.016..10.024 rows=10000 loops=1
  • Worker 1: actual time=0.018..11.142 rows=10000 loops=1
26. 0.087 0.207 ↑ 6.8 25 3

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

  • Output: n2.n_name, n2.n_nationkey
  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
  • Worker 0: actual time=0.069..0.069 rows=25 loops=1
  • Worker 1: actual time=0.088..0.088 rows=25 loops=1
27. 0.120 0.120 ↑ 6.8 25 3

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

  • Output: n2.n_name, n2.n_nationkey
  • Worker 0: actual time=0.025..0.041 rows=25 loops=1
  • Worker 1: actual time=0.029..0.052 rows=25 loops=1
Planning time : 13.523 ms
Execution time : 6,868.572 ms