explain.depesz.com

PostgreSQL's explain analyze made readable

Result: rHkh : q9 ssd 40g RD - NoIdx

Settings
# exclusive inclusive rows x rows loops node
1. 0.702 930,339.117 ↑ 2,337.3 175 1

Finalize GroupAggregate (cost=16,602,064.84..16,872,874.41 rows=409,020 width=144) (actual time=926,074.054..930,339.117 rows=175 loops=1)

  • Group Key: nation.n_name, (date_part(_year_::text, (orders.o_orderdate)::timestamp without time zone))
2. 1,277.494 930,338.415 ↑ 1,558.2 525 1

Gather Merge (cost=16,602,064.84..16,857,536.16 rows=818,040 width=144) (actual time=926,038.536..930,338.415 rows=525 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
3. 3,242.178 929,060.921 ↑ 2,337.3 175 3 / 3

Partial GroupAggregate (cost=16,601,064.81..16,762,114.02 rows=409,020 width=144) (actual time=924,836.865..929,060.921 rows=175 loops=3)

  • Group Key: nation.n_name, (date_part(_year_::text, (orders.o_orderdate)::timestamp without time zone))
4. 9,493.850 925,818.743 ↑ 1.8 4,344,197 3 / 3

Sort (cost=16,601,064.81..16,620,301.23 rows=7,694,568 width=135) (actual time=924,751.307..925,818.743 rows=4,344,197 loops=3)

  • Sort Key: nation.n_name, (date_part(_year_::text, (orders.o_orderdate)::timestamp without time zone)) DESC
  • Sort Method: external merge Disk: 322,456kB
5. 7,165.013 916,324.893 ↑ 1.8 4,344,197 3 / 3

Hash Join (cost=4,714,568.44..14,796,730.12 rows=7,694,568 width=135) (actual time=830,406.579..916,324.893 rows=4,344,197 loops=3)

  • Hash Cond: (lineitem.l_suppkey = supplier.s_suppkey)
6. 41,904.519 908,542.429 ↑ 1.8 4,344,197 3 / 3

Hash Join (cost=4,684,687.50..14,479,446.60 rows=7,752,278 width=43) (actual time=681,410.513..908,542.429 rows=4,344,197 loops=3)

  • Hash Cond: (lineitem.l_orderkey = orders.o_orderkey)
7. 46,989.773 664,738.311 ↑ 1.8 4,344,197 3 / 3

Hash Join (cost=1,995,092.59..11,321,292.18 rows=7,752,278 width=47) (actual time=145,175.558..664,738.311 rows=4,344,197 loops=3)

  • Hash Cond: ((lineitem.l_suppkey = partsupp.ps_suppkey) AND (lineitem.l_partkey = partsupp.ps_partkey))
8. 85,389.703 477,027.279 ↑ 1.8 4,344,197 3 / 3

Hash Join (cost=274,528.19..9,158,718.54 rows=7,752,278 width=45) (actual time=2,824.786..477,027.279 rows=4,344,197 loops=3)

  • Hash Cond: (lineitem.l_partkey = part.p_partkey)
9. 388,814.553 388,814.553 ↑ 1.2 80,004,097 3 / 3

Parallel Seq Scan on lineitem (cost=0.00..5,861,299.00 rows=100,001,700 width=41) (actual time=0.159..388,814.553 rows=80,004,097 loops=3)

10. 117.124 2,823.023 ↑ 1.5 434,469 3 / 3

Hash (cost=263,921.35..263,921.35 rows=646,467 width=4) (actual time=2,823.023..2,823.023 rows=434,469 loops=3)

  • Buckets: 131,072 Batches: 16 Memory Usage: 1,987kB
11. 2,705.899 2,705.899 ↑ 1.5 434,469 3 / 3

Seq Scan on part (cost=0.00..263,921.35 rows=646,467 width=4) (actual time=0.180..2,705.899 rows=434,469 loops=3)

  • Filter: ((p_name)::text ~~ _%orchid%_::text)
  • Rows Removed by Filter: 7,565,531
12. 10,744.934 140,721.259 ↑ 1.0 32,000,000 3 / 3

Hash (cost=1,053,011.36..1,053,011.36 rows=32,002,536 width=22) (actual time=140,721.259..140,721.259 rows=32,000,000 loops=3)

  • Buckets: 65,536 Batches: 512 Memory Usage: 3,941kB
13. 129,976.325 129,976.325 ↑ 1.0 32,000,000 3 / 3

Seq Scan on partsupp (cost=0.00..1,053,011.36 rows=32,002,536 width=22) (actual time=0.030..129,976.325 rows=32,000,000 loops=3)

14. 16,298.515 201,899.599 ↑ 1.0 60,000,000 3 / 3

Hash (cost=1,705,054.96..1,705,054.96 rows=60,009,996 width=8) (actual time=201,899.599..201,899.599 rows=60,000,000 loops=3)

  • Buckets: 131,072 Batches: 1,024 Memory Usage: 3,316kB
15. 185,601.084 185,601.084 ↑ 1.0 60,000,000 3 / 3

Seq Scan on orders (cost=0.00..1,705,054.96 rows=60,009,996 width=8) (actual time=0.392..185,601.084 rows=60,000,000 loops=3)

16. 122.802 617.451 ↑ 1.0 400,000 3 / 3

Hash (cost=18,239.94..18,239.94 rows=400,000 width=108) (actual time=617.451..617.451 rows=400,000 loops=3)

  • Buckets: 32,768 Batches: 16 Memory Usage: 1,788kB
17. 136.413 494.649 ↑ 1.0 400,000 3 / 3

Hash Join (cost=13.82..18,239.94 rows=400,000 width=108) (actual time=0.561..494.649 rows=400,000 loops=3)

  • Hash Cond: (supplier.s_nationkey = nation.n_nationkey)
18. 358.204 358.204 ↑ 1.0 400,000 3 / 3

Seq Scan on supplier (cost=0.00..13,197.00 rows=400,000 width=12) (actual time=0.514..358.204 rows=400,000 loops=3)

19. 0.011 0.032 ↑ 6.8 25 3 / 3

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
20. 0.021 0.021 ↑ 6.8 25 3 / 3

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

Planning time : 5.696 ms
Execution time : 948,528.425 ms