explain.depesz.com

PostgreSQL's explain analyze made readable

Result: qd2u : q2-source

Settings
# exclusive inclusive rows x rows loops node
1. 0.008 825.137 ↓ 100.0 100 1

Limit (cost=63,855.71..63,855.72 rows=1 width=192) (actual time=825.122..825.137 rows=100 loops=1)

2. 0.507 825.129 ↓ 100.0 100 1

Sort (cost=63,855.71..63,855.72 rows=1 width=192) (actual time=825.121..825.129 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: 52kB
3. 0.405 824.622 ↓ 460.0 460 1

Nested Loop (cost=116.82..63,855.70 rows=1 width=192) (actual time=5.270..824.622 rows=460 loops=1)

  • Join Filter: (nation.n_nationkey = supplier.s_nationkey)
  • Rows Removed by Join Filter: 1840
4. 0.006 0.017 ↑ 1.0 5 1

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

  • Join Filter: (nation.n_regionkey = region.r_regionkey)
  • Rows Removed by Join Filter: 20
5. 0.005 0.005 ↑ 1.0 1 1

Seq Scan on region (cost=0.00..1.06 rows=1 width=4) (actual time=0.004..0.005 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 (cost=0.00..1.25 rows=25 width=34) (actual time=0.002..0.006 rows=25 loops=1)

7. 0.642 824.200 ↓ 460.0 460 5

Materialize (cost=116.82..63,853.01 rows=1 width=170) (actual time=0.541..164.840 rows=460 loops=5)

8. 397.108 823.558 ↓ 460.0 460 1

Nested Loop (cost=116.82..63,853.00 rows=1 width=170) (actual time=2.696..823.558 rows=460 loops=1)

  • Join Filter: (partsupp.ps_suppkey = supplier.s_suppkey)
  • Rows Removed by Join Filter: 4599540
9. 36.076 101.230 ↓ 460.0 460 1

Nested Loop (cost=116.82..63,406.00 rows=1 width=34) (actual time=1.227..101.230 rows=460 loops=1)

10. 24.181 24.816 ↑ 1.0 747 1

Bitmap Heap Scan on part (cost=72.86..4,307.68 rows=761 width=30) (actual time=1.025..24.816 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. 0.635 0.635 ↓ 1.0 3,907 1

Bitmap Index Scan on part_idx_5 (cost=0.00..72.67 rows=3,767 width=0) (actual time=0.635..0.635 rows=3,907 loops=1)

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

Index Scan using partsupp_idx_4 on partsupp (cost=43.96..77.65 rows=1 width=14) (actual time=0.029..0.054 rows=1 loops=747)

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

SubPlan (for Index Scan)

14. 1.494 33.615 ↑ 1.0 1 747

Aggregate (cost=43.53..43.54 rows=1 width=6) (actual time=0.045..0.045 rows=1 loops=747)

15. 1.494 32.121 ↑ 1.0 1 747

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

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

Seq Scan on region region_1 (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 29.133 ↑ 1.0 4 747

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

18. 3.735 24.651 ↑ 1.0 4 747

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

19. 14.940 14.940 ↑ 1.0 4 747

Index Scan using partsupp_idx_1 on partsupp partsupp_1 (cost=0.42..8.50 rows=4 width=10) (actual time=0.019..0.020 rows=4 loops=747)

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

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

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

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

  • Index Cond: (n_nationkey = supplier_1.s_nationkey)
22. 1.494 33.615 ↑ 1.0 1 747

Aggregate (cost=43.53..43.54 rows=1 width=6) (actual time=0.045..0.045 rows=1 loops=747)

23. 1.494 32.121 ↑ 1.0 1 747

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

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

Seq Scan on region region_1 (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 29.133 ↑ 1.0 4 747

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

26. 3.735 24.651 ↑ 1.0 4 747

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

27. 14.940 14.940 ↑ 1.0 4 747

Index Scan using partsupp_idx_1 on partsupp partsupp_1 (cost=0.42..8.50 rows=4 width=10) (actual time=0.019..0.020 rows=4 loops=747)

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

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

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

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

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

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

Planning time : 4.990 ms
Execution time : 825.362 ms