explain.depesz.com

PostgreSQL's explain analyze made readable

Result: haS7 : q2-norm

Settings
# exclusive inclusive rows x rows loops node
1. 0.006 773.362 ↓ 100.0 100 1

Limit (cost=64,040.50..64,040.51 rows=1 width=192) (actual time=773.348..773.362 rows=100 loops=1)

2. 0.524 773.356 ↓ 100.0 100 1

Sort (cost=64,040.50..64,040.51 rows=1 width=192) (actual time=773.346..773.356 rows=100 loops=1)

  • Sort Key: s.s_acctbal DESC, n.n_name, s.s_name, p.p_partkey
  • Sort Method: top-N heapsort Memory: 52kB
3. 0.405 772.832 ↓ 460.0 460 1

Nested Loop (cost=115.82..64,040.49 rows=1 width=192) (actual time=6.424..772.832 rows=460 loops=1)

  • Join Filter: (n.n_nationkey = s.s_nationkey)
  • Rows Removed by Join Filter: 1840
4. 0.012 0.027 ↑ 1.0 5 1

Nested Loop (cost=0.00..2.62 rows=5 width=30) (actual time=0.020..0.027 rows=5 loops=1)

  • Join Filter: (n.n_regionkey = r.r_regionkey)
  • Rows Removed by Join Filter: 20
5. 0.009 0.009 ↑ 1.0 1 1

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

  • Filter: (r_name = 'EUROPE'::bpchar)
  • Rows Removed by Filter: 4
6. 0.006 0.006 ↑ 1.0 25 1

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

7. 0.653 772.400 ↓ 460.0 460 5

Materialize (cost=115.82..64,037.80 rows=1 width=170) (actual time=0.827..154.480 rows=460 loops=5)

8. 395.778 771.747 ↓ 460.0 460 1

Nested Loop (cost=115.82..64,037.79 rows=1 width=170) (actual time=4.124..771.747 rows=460 loops=1)

  • Join Filter: (ps.ps_suppkey = s.s_suppkey)
  • Rows Removed by Join Filter: 4599540
9. 37.309 58.109 ↓ 460.0 460 1

Nested Loop (cost=115.82..63,590.79 rows=1 width=34) (actual time=2.168..58.109 rows=460 loops=1)

10. 8.516 9.595 ↑ 1.0 747 1

Bitmap Heap Scan on part p (cost=71.86..4,276.31 rows=764 width=30) (actual time=1.818..9.595 rows=747 loops=1)

  • Recheck Cond: (p_size = 15)
  • Filter: ((p_type)::text ~~ '%BRASS'::text)
  • Rows Removed by Filter: 3160
  • Heap Blocks: exact=2567
11. 1.079 1.079 ↓ 1.1 3,907 1

Bitmap Index Scan on part_idx_5 (cost=0.00..71.67 rows=3,633 width=0) (actual time=1.079..1.079 rows=3,907 loops=1)

  • Index Cond: (p_size = 15)
12. 0.000 11.205 ↑ 1.0 1 747

Index Scan using partsupp_idx_4 on partsupp ps (cost=43.96..77.63 rows=1 width=14) (actual time=0.009..0.015 rows=1 loops=747)

  • Index Cond: (ps_supplycost = (SubPlan 1))
  • Filter: (p.p_partkey = ps_partkey)
  • Rows Removed by Filter: 5
13.          

SubPlan (forIndex Scan)

14. 2.241 35.856 ↑ 1.0 1 747

Aggregate (cost=43.52..43.53 rows=1 width=6) (actual time=0.048..0.048 rows=1 loops=747)

15. 0.747 33.615 ↑ 1.0 1 747

Nested Loop (cost=0.85..43.52 rows=1 width=6) (actual time=0.039..0.045 rows=1 loops=747)

  • Join Filter: (n2.n_regionkey = r2.r_regionkey)
  • Rows Removed by Join Filter: 3
16. 1.494 1.494 ↑ 1.0 1 747

Seq Scan on region_1 r2 (cost=0.00..1.06 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=747)

  • Filter: (r_name = 'EUROPE'::bpchar)
  • Rows Removed by Filter: 4
17. 1.494 31.374 ↑ 1.0 4 747

Nested Loop (cost=0.85..42.41 rows=4 width=10) (actual time=0.027..0.042 rows=4 loops=747)

18. 3.735 26.892 ↑ 1.0 4 747

Nested Loop (cost=0.71..41.75 rows=4 width=10) (actual time=0.026..0.036 rows=4 loops=747)

19. 17.181 17.181 ↑ 1.0 4 747

Index Scan using partsupp_1_ps_partkey_idx on partsupp_1 ps2 (cost=0.42..8.50 rows=4 width=10) (actual time=0.021..0.023 rows=4 loops=747)

  • Index Cond: (p.p_partkey = ps_partkey)
20. 5.976 5.976 ↑ 1.0 1 2,988

Index Scan using supplier_1_pkey on supplier_1 s2 (cost=0.29..8.30 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=2,988)

  • Index Cond: (s_suppkey = ps2.ps_suppkey)
21. 2.988 2.988 ↑ 1.0 1 2,988

Index Scan using nation_1_pkey on nation_1 n2 (cost=0.14..0.16 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=2,988)

  • Index Cond: (n_nationkey = s2.s_nationkey)
22. 2.241 35.856 ↑ 1.0 1 747

Aggregate (cost=43.52..43.53 rows=1 width=6) (actual time=0.048..0.048 rows=1 loops=747)

23. 0.747 33.615 ↑ 1.0 1 747

Nested Loop (cost=0.85..43.52 rows=1 width=6) (actual time=0.039..0.045 rows=1 loops=747)

  • Join Filter: (n2.n_regionkey = r2.r_regionkey)
  • Rows Removed by Join Filter: 3
24. 1.494 1.494 ↑ 1.0 1 747

Seq Scan on region_1 r2 (cost=0.00..1.06 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=747)

  • Filter: (r_name = 'EUROPE'::bpchar)
  • Rows Removed by Filter: 4
25. 1.494 31.374 ↑ 1.0 4 747

Nested Loop (cost=0.85..42.41 rows=4 width=10) (actual time=0.027..0.042 rows=4 loops=747)

26. 3.735 26.892 ↑ 1.0 4 747

Nested Loop (cost=0.71..41.75 rows=4 width=10) (actual time=0.026..0.036 rows=4 loops=747)

27. 17.181 17.181 ↑ 1.0 4 747

Index Scan using partsupp_1_ps_partkey_idx on partsupp_1 ps2 (cost=0.42..8.50 rows=4 width=10) (actual time=0.021..0.023 rows=4 loops=747)

  • Index Cond: (p.p_partkey = ps_partkey)
28. 5.976 5.976 ↑ 1.0 1 2,988

Index Scan using supplier_1_pkey on supplier_1 s2 (cost=0.29..8.30 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=2,988)

  • Index Cond: (s_suppkey = ps2.ps_suppkey)
29. 2.988 2.988 ↑ 1.0 1 2,988

Index Scan using nation_1_pkey on nation_1 n2 (cost=0.14..0.16 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=2,988)

  • Index Cond: (n_nationkey = s2.s_nationkey)
30. 317.860 317.860 ↑ 1.0 10,000 460

Seq Scan on supplier s (cost=0.00..322.00 rows=10,000 width=144) (actual time=0.001..0.691 rows=10,000 loops=460)

Planning time : 7.900 ms
Execution time : 773.730 ms