[pgsql-jp: 25069] Re: optimizerの動作

[ARAI Shunichi]

新井です。
ご回答ありがとうございます。

set enable_sort to off;の場合(またはfoo.id1=?が2件以上の場合)

---
Limit  (cost=0.00..7645.59 rows=100 width=364) (actual time=27.09..785.79
rows=100 loops=1)
  ->  Nested Loop  (cost=0.00..67848.02 rows=887 width=364) (actual
time=27.02..785.01 rows=101 loops=1)
        ->  Index Scan Backward using bar_updated_idx on message
(cost=0.00..12412.14 rows=33496 width=360) (actual time=3.65..255.87
rows=4145 loops=1)
        ->  Seq Scan on foo (cost=0.00..1.64 rows=1 width=4) (actual
time=0.05..0.12 rows=1 loops=4145)
Total runtime: 788.22 msec
---

set enable_sort to on;
bar.id2のindexをdropした場合。

---
Limit  (cost=3160.48..3160.48 rows=100 width=364) (actual
time=13043.01..13043.65 rows=100 loops=1)
  ->  Sort  (cost=3160.48..3160.48 rows=887 width=364) (actual
time=13042.94..13043.41 rows=101 loops=1)
        ->  Hash Join  (cost=1.64..3117.05 rows=887 width=364) (actual
time=16.5
5..2299.52 rows=17161 loops=1)
              ->  Seq Scan on bar (cost=0.00..2099.83 rows=33483 width=360)
 (actual time=0.72..1979.48 rows=33493 loops=1)
              ->  Hash  (cost=1.64..1.64 rows=1 width=4) (actual
time=1.05..1.05
 rows=0 loops=1)
                    ->  Seq Scan on foo (cost=0.00..1.64 rows=1 width=4)
 (actual time=0.92..0.97 rows=1 loops=1)
Total runtime: 13297.21 msec
---

bar.updatedのindexもdropした場合。

---
Limit  (cost=3160.48..3160.48 rows=100 width=364) (actual
time=8245.45..8246.73
rows=100 loops=1)
  ->  Sort  (cost=3160.48..3160.48 rows=887 width=364) (actual
time=8245.32..8246.43 rows=101 loops=1)
        ->  Hash Join  (cost=1.64..3117.05 rows=887 width=364) (actual
time=28.5
1..2038.22 rows=17161 loops=1)
              ->  Seq Scan on bar (cost=0.00..2099.83 rows=33483 width=360)
 (actual time=1.93..1746.43 rows=33496 loops=1)
              ->  Hash  (cost=1.64..1.64 rows=1 width=4) (actual
time=0.84..0.84
 rows=0 loops=1)
                    ->  Seq Scan on foo (cost=0.00..1.64 rows=1 width=4)
 (actual time=0.75..0.81 rows=1 loops=1)
Total runtime: 12193.77 msec
---

analyzeもしていますが、やはりset enable_sort to offをしないと
時間のかかるクエリプランになってしまうようです。

-----
新井 俊一
arai ＠ mellowtone.org
http://www.mellowtone.org/