explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ALfe

Settings
# exclusive inclusive rows x rows loops node
1. 0.084 8,375.014 ↓ 100.0 100 1

Limit (cost=2,036,188.06..2,036,188.07 rows=1 width=196) (actual time=8,374.928..8,375.014 rows=100 loops=1)

2. 171.147 8,374.930 ↓ 100.0 100 1

Sort (cost=2,036,188.06..2,036,188.07 rows=1 width=196) (actual time=8,374.927..8,374.930 rows=100 loops=1)

  • Sort Key: supplier.s_acctbal DESC, nation.n_name, supplier.s_name, part.p_partkey
  • Sort Method: top-N heapsort Memory: 71kB
3. 311.697 8,203.783 ↓ 4,044.0 4,044 1

Merge Join (cost=375,794.42..2,036,188.05 rows=1 width=196) (actual time=6,292.174..8,203.783 rows=4,044 loops=1)

  • Merge Cond: (part.p_partkey = partsupp.ps_partkey)
  • Join Filter: (partsupp.ps_supplycost = (SubPlan 1))
  • Rows Removed by Join Filter: 1132
4. 0.000 187.692 ↓ 1.1 7,916 1

Gather Merge (cost=1,000.45..207,422.68 rows=7,504 width=32) (actual time=175.777..187.692 rows=7,916 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
5. 5,978.778 5,978.778 ↑ 1.2 2,639 3

Parallel Index Scan using part_pk on part (cost=0.43..205,556.51 rows=3,127 width=32) (actual time=0.767..1,992.926 rows=2,639 loops=3)

  • Filter: (((p_type)::text ~~ '%TIN'::text) AND (p_size = '38'::numeric))
  • Rows Removed by Filter: 664028
6. 631.560 6,208.530 ↑ 1.2 1,282,938 1

Sort (cost=374,793.86..378,794.30 rows=1,600,177 width=176) (actual time=6,114.698..6,208.530 rows=1,282,938 loops=1)

  • Sort Key: partsupp.ps_partkey
  • Sort Method: quicksort Memory: 390003kB
7. 368.576 5,576.970 ↑ 1.2 1,283,200 1

Custom Scan (GpuJoin) on partsupp (cost=16,534.95..209,897.22 rows=1,600,177 width=176) (actual time=2,339.907..5,576.970 rows=1,283,200 loops=1)

  • Depth 1: GpuHashJoin (plan nrows: 8000887...1600177, actual nrows: 8000000...1283200)
  • GPU Preference: GPU0 (GeForce RTX 2080)
8. 5,095.597 5,208.394 ↑ 1.0 8,000,000 1

Outer Scan: partsupp (cost=0.00..262,995.87 rows=8,000,887 width=18) (actual time=373.175..5,208.394 rows=8,000,000 loops=1)

  • HashKeys: partsupp.ps_suppkey
  • JoinQuals: (supplier.s_suppkey = partsupp.ps_suppkey)
  • KDS-Hash (size plan: 4551.00KB, exec: 3649.99KB)
9. 11.224 112.797 ↑ 1.2 16,040 1

Hash Join (cost=2.51..3,840.51 rows=20,000 width=170) (actual time=0.058..112.797 rows=16,040 loops=1)

  • Hash Cond: (supplier.s_nationkey = nation.n_nationkey)
10. 101.543 101.543 ↑ 1.0 100,000 1

Seq Scan on supplier (cost=0.00..3,263.00 rows=100,000 width=148) (actual time=0.007..101.543 rows=100,000 loops=1)

11. 0.000 0.030 ↑ 1.2 4 1

Hash (cost=2.45..2.45 rows=5 width=30) (actual time=0.030..0.030 rows=4 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
12. 0.016 0.030 ↑ 1.2 4 1

Hash Join (cost=1.07..2.45 rows=5 width=30) (actual time=0.025..0.030 rows=4 loops=1)

  • Hash Cond: (nation.n_regionkey = region.r_regionkey)
13. 0.005 0.005 ↑ 1.0 25 1

Seq Scan on nation (cost=0.00..1.25 rows=25 width=34) (actual time=0.004..0.005 rows=25 loops=1)

14. 0.004 0.009 ↑ 1.0 1 1

Hash (cost=1.06..1.06 rows=1 width=4) (actual time=0.009..0.009 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
15. 0.005 0.005 ↑ 1.0 1 1

Seq Scan on region (cost=0.00..1.06 rows=1 width=4) (actual time=0.005..0.005 rows=1 loops=1)

  • Filter: (r_name = 'MIDDLE EAST'::bpchar)
  • Rows Removed by Filter: 4
16.          

SubPlan (forMerge Join)

17. 25.880 1,495.864 ↑ 1.0 1 5,176

Aggregate (cost=240.81..240.82 rows=1 width=32) (actual time=0.288..0.289 rows=1 loops=5,176)

18. 5.176 1,469.984 ↑ 4.0 1 5,176

Nested Loop (cost=0.86..240.80 rows=4 width=6) (actual time=0.268..0.284 rows=1 loops=5,176)

  • Join Filter: (nation_1.n_regionkey = region_1.r_regionkey)
  • Rows Removed by Join Filter: 3
19. 10.352 10.352 ↑ 1.0 1 5,176

Seq Scan on region region_1 (cost=0.00..1.06 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=5,176)

  • Filter: (r_name = 'MIDDLE EAST'::bpchar)
  • Rows Removed by Filter: 4
20. 144.928 1,454.456 ↑ 4.8 4 5,176

Nested Loop (cost=0.86..239.50 rows=19 width=10) (actual time=0.254..0.281 rows=4 loops=5,176)

21. 1,185.304 1,288.824 ↑ 4.8 4 5,176

Nested Loop (cost=0.72..236.57 rows=19 width=10) (actual time=0.252..0.272 rows=4 loops=5176) -> Index Scan using partsupp_pk on partsupp partsupp_1 (cost=0.43..78.68 rows=19 width=12) (actual time=0.245..0.249 rows=4 loops=5,176)

  • Index Cond: (part.p_partkey = ps_partkey)
22. 103.520 103.520 ↑ 1.0 1 20,704

Index Scan using supplier_pk on supplier supplier_1 (cost=0.29..8.31 rows=1 width=10) (actual time=0.005..0.005 rows=1 loops=20,704)

  • Index Cond: (s_suppkey = partsupp_1.ps_suppkey)
23. 20.704 20.704 ↑ 1.0 1 20,704

Index Scan using nation_pk on nation nation_1 (cost=0.14..0.16 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=20,704)

  • Index Cond: (n_nationkey = supplier_1.s_nationkey)
Planning time : 39.091 ms
Execution time : 8,521.370 ms