explain.depesz.com

PostgreSQL's explain analyze made readable

Result: QBE4

Settings
# exclusive inclusive rows x rows loops node
1. 33.457 142,703.682 ↑ 1.0 1 1

Limit (cost=1,507,850.30..1,507,850.45 rows=1 width=40) (actual time=142,670.226..142,703.682 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=4758237 read=334854, temp read=20705 written=20788
  • I/O Timings: read=401876.173
2. 0.000 142,670.225 ↑ 640.0 1 1

Finalize GroupAggregate (cost=1,507,850.30..1,507,944.36 rows=640 width=40) (actual time=142,670.225..142,670.225 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=1614529 read=114143, temp read=7116 written=6844
  • I/O Timings: read=133467.018
3. 0.000 142,703.642 ↑ 133.5 4 1

Gather Merge (cost=1,507,850.30..1,507,923.29 rows=534 width=72) (actual time=142,665.413..142,703.642 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=4758237 read=334854, temp read=20705 written=20788
  • I/O Timings: read=401876.173
4. 17.310 427,880.301 ↑ 133.5 2 3

Partial GroupAggregate (cost=1,506,850.28..1,506,861.63 rows=267 width=72) (actual time=142,622.826..142,626.767 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=4758237 read=334854, temp read=20705 written=20788
  • I/O Timings: read=401876.173
  • Worker 0: actual time=142653.662..142658.407 rows=2 loops=1
  • Buffers: shared hit=1612388 read=109925, temp read=6993 written=6620
  • I/O Timings: read=133486.004
  • Worker 1: actual time=142633.302..142635.590 rows=2 loops=1
  • Buffers: shared hit=1531320 read=110786, temp read=6596 written=7324
  • I/O Timings: read=134923.151
5. 37.635 427,862.991 ↓ 30.3 8,102 3

Sort (cost=1,506,850.28..1,506,850.95 rows=267 width=124) (actual time=142,620.291..142,620.997 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: 1029kB
  • Worker 0: Sort Method: quicksort Memory: 1035kB
  • Worker 1: Sort Method: quicksort Memory: 797kB
  • Buffers: shared hit=4758237 read=334854, temp read=20705 written=20788
  • I/O Timings: read=401876.173
  • Worker 0: actual time=142651.230..142651.953 rows=8326 loops=1
  • Buffers: shared hit=1612388 read=109925, temp read=6993 written=6620
  • I/O Timings: read=133486.004
  • Worker 1: actual time=142630.919..142631.495 rows=7733 loops=1
  • Buffers: shared hit=1531320 read=110786, temp read=6596 written=7324
  • I/O Timings: read=134923.151
6. 64.311 427,825.356 ↓ 30.3 8,102 3

Hash Join (cost=97,247.92..1,506,839.52 rows=267 width=124) (actual time=4,033.776..142,608.452 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=4758223 read=334854, temp read=20705 written=20788
  • I/O Timings: read=401876.173
  • Worker 0: actual time=4015.305..142638.822 rows=8326 loops=1
  • Buffers: shared hit=1612381 read=109925, temp read=6993 written=6620
  • I/O Timings: read=133486.004
  • Worker 1: actual time=4052.788..142619.513 rows=7733 loops=1
  • Buffers: shared hit=1531313 read=110786, temp read=6596 written=7324
  • I/O Timings: read=134923.151
7. 71.682 427,760.964 ↓ 30.3 8,102 3

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

  • Output: lineitem.l_extendedprice, lineitem.l_discount, supplier.s_nationkey, orders.o_orderdate
  • Inner Unique: true
  • Buffers: shared hit=4758186 read=334854, temp read=20705 written=20788
  • I/O Timings: read=401876.173
  • Worker 0: actual time=4015.117..142617.191 rows=8326 loops=1
  • Buffers: shared hit=1612366 read=109925, temp read=6993 written=6620
  • I/O Timings: read=133486.004
  • Worker 1: actual time=4052.680..142599.694 rows=7733 loops=1
  • Buffers: shared hit=1531298 read=110786, temp read=6596 written=7324
  • I/O Timings: read=134923.151
8. 29.748 427,543.446 ↓ 30.3 8,102 3

Parallel Hash Join (cost=97,233.80..1,506,322.74 rows=267 width=24) (actual time=4,033.622..142,514.482 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=4685127 read=334854, temp read=20705 written=20788
  • I/O Timings: read=401876.173
  • Worker 0: actual time=4015.089..142539.640 rows=8326 loops=1
  • Buffers: shared hit=1587332 read=109925, temp read=6993 written=6620
  • I/O Timings: read=133486.004
  • Worker 1: actual time=4052.653..142531.515 rows=7733 loops=1
  • Buffers: shared hit=1508039 read=110786, temp read=6596 written=7324
  • I/O Timings: read=134923.151
9. 890.634 424,918.977 ↑ 5.5 8,104 3

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

  • Output: lineitem.l_extendedprice, lineitem.l_discount, lineitem.l_partkey, lineitem.l_suppkey, orders.o_orderdate
  • Buffers: shared hit=4659782 read=303598, temp read=20705 written=20788
  • I/O Timings: read=401830.327
  • Worker 0: actual time=3153.497..141668.609 rows=8329 loops=1
  • Buffers: shared hit=1575559 read=100701, temp read=6993 written=6620
  • I/O Timings: read=133463.567
  • Worker 1: actual time=3190.120..141660.161 rows=7736 loops=1
  • Buffers: shared hit=1501383 read=101562, temp read=6596 written=7324
  • I/O Timings: read=134899.741
10. 2,065.605 10,675.809 ↓ 27.1 304,159 3

Parallel Hash Join (cost=45,766.54..422,941.03 rows=11,230 width=8) (actual time=3,157.012..3,558.603 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=313293, temp read=20705 written=20788
  • Worker 0: actual time=3153.314..3558.551 rows=309649 loops=1
  • Buffers: shared hit=98322, temp read=6993 written=6620
  • Worker 1: actual time=3159.251..3545.427 rows=292491 loops=1
  • Buffers: shared hit=112553, temp read=6596 written=7324
11. 7,466.364 7,466.364 ↑ 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.015..2,488.788 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: 1478301 Removes: 1093849 Bits Set: 19.19%
  • Buffers: shared hit=276222
  • Worker 0: actual time=0.011..2516.702 rows=379012 loops=1
  • Buffers: shared hit=88584
  • Worker 1: actual time=0.023..2484.365 rows=420083 loops=1
  • Buffers: shared hit=98030
12. 307.440 1,143.840 ↓ 27.2 100,090 3

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

  • Output: customer.c_custkey
  • Buckets: 131072 (originally 16384) Batches: 8 (originally 1) Memory Usage: 2528kB
  • Buffers: shared hit=37071, temp written=824
  • Worker 0: actual time=383.936..383.936 rows=79085 loops=1
  • Buffers: shared hit=9738, temp written=224
  • Worker 1: actual time=379.905..379.905 rows=117369 loops=1
  • Buffers: shared hit=14523, temp written=236
13. 149.064 836.400 ↓ 27.2 100,090 3

Hash Join (cost=24.31..45,720.58 rows=3,677 width=4) (actual time=0.093..278.800 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.093..280.000 rows=79085 loops=1
  • Buffers: shared hit=9738
  • Worker 1: actual time=0.107..278.846 rows=117369 loops=1
  • Buffers: shared hit=14523
14. 687.150 687.150 ↑ 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.050 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: 518438 Removes: 414622
  • Buffers: shared hit=37065
  • Worker 0: actual time=0.005..235.929 rows=79086 loops=1
  • Buffers: shared hit=9736
  • Worker 1: actual time=0.007..222.516 rows=117370 loops=1
  • Buffers: shared hit=14521
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.063..0.063 rows=5 loops=1
  • Buffers: shared hit=2
  • Worker 1: actual time=0.075..0.075 rows=5 loops=1
  • Buffers: shared hit=2
16. 0.075 0.171 ↓ 5.0 5 3

Hash Join (cost=12.14..24.29 rows=1 width=4) (actual time=0.050..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.052..0.059 rows=5 loops=1
  • Buffers: shared hit=2
  • Worker 1: actual time=0.062..0.070 rows=5 loops=1
  • Buffers: shared hit=2
17. 0.024 0.024 ↑ 6.8 25 3

Seq Scan on public.nation n1 (cost=0.00..11.70 rows=170 width=12) (actual time=0.005..0.008 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.004..0.007 rows=25 loops=1
  • Buffers: shared hit=1
  • Worker 1: actual time=0.007..0.010 rows=25 loops=1
  • Buffers: shared hit=1
18. 0.015 0.072 ↑ 1.0 1 3

Hash (cost=12.12..12.12 rows=1 width=4) (actual time=0.023..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.026..0.026 rows=1 loops=1
  • Buffers: shared hit=1
  • Worker 1: actual time=0.033..0.034 rows=1 loops=1
  • Buffers: shared hit=1
19. 0.057 0.057 ↑ 1.0 1 3

Seq Scan on public.region (cost=0.00..12.12 rows=1 width=4) (actual time=0.017..0.019 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.020..0.021 rows=1 loops=1
  • Buffers: shared hit=1
  • Worker 1: actual time=0.027..0.028 rows=1 loops=1
  • Buffers: shared hit=1
20. 413,352.534 413,352.534 ↓ 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.452..0.453 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: 1242409 Removes: 1234161
  • Buffers: shared hit=4346489 read=303598
  • I/O Timings: read=401830.327
  • Worker 0: actual time=0.444..0.445 rows=0 loops=309649
  • Buffers: shared hit=1477237 read=100701
  • I/O Timings: read=133463.567
  • Worker 1: actual time=0.471..0.471 rows=0 loops=292491
  • Buffers: shared hit=1388830 read=101562
  • I/O Timings: read=134899.741
21. 38.838 2,594.721 ↑ 1.3 4,433 3

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

  • Output: part.p_partkey
  • Buckets: 16384 Batches: 1 Memory Usage: 704kB
  • Buffers: shared hit=25345 read=31256
  • I/O Timings: read=45.847
  • Worker 0: actual time=860.699..860.699 rows=5226 loops=1
  • Buffers: shared hit=11773 read=9224
  • I/O Timings: read=22.437
  • Worker 1: actual time=862.510..862.511 rows=3752 loops=1
  • Buffers: shared hit=6656 read=9224
  • I/O Timings: read=23.410
22. 2,555.883 2,555.883 ↑ 1.3 4,433 3

Parallel Seq Scan on public.part (cost=0.00..51,397.32 rows=5,550 width=4) (actual time=9.141..851.961 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=25345 read=31256
  • I/O Timings: read=45.847
  • Worker 0: actual time=19.118..844.066 rows=5226 loops=1
  • Buffers: shared hit=11773 read=9224
  • I/O Timings: read=22.437
  • Worker 1: actual time=8.214..853.878 rows=3752 loops=1
  • Buffers: shared hit=6656 read=9224
  • I/O Timings: read=23.410
23. 145.836 145.836 ↑ 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.006..0.006 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=73059
  • Worker 0: actual time=0.006..0.006 rows=1 loops=8326
  • Buffers: shared hit=25034
  • Worker 1: actual time=0.005..0.005 rows=1 loops=7733
  • Buffers: shared hit=23259
24. 0.036 0.081 ↑ 6.8 25 3

Hash (cost=11.70..11.70 rows=170 width=108) (actual time=0.026..0.027 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.028..0.028 rows=25 loops=1
  • Buffers: shared hit=1
  • Worker 1: actual time=0.026..0.027 rows=25 loops=1
  • Buffers: shared hit=1
25. 0.045 0.045 ↑ 6.8 25 3

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

  • Output: n2.n_name, n2.n_nationkey
  • Buffers: shared hit=3
  • Worker 0: actual time=0.011..0.017 rows=25 loops=1
  • Buffers: shared hit=1
  • Worker 1: actual time=0.009..0.015 rows=25 loops=1
  • Buffers: shared hit=1
Planning time : 16.906 ms
Execution time : 142,704.108 ms