From: | Greg Stark <gsstark(at)mit(dot)edu> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Optimizer going cuckoo for full table scans |
Date: | 2003-02-27 18:41:33 |
Message-ID: | 877kblmuqq.fsf@stark.dyndns.tv |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Does it ever make sense for random_page_cost to be *below* 1? It seems like
something is whacked if the database is still doing sequential scans even if I
set random_page_cost below one.
Here it's doing sequential scans even though I have it set to 0.6. It doesn't
switch to indexes until I lower it to 0.5. The index is twice as fast too.
slo=> analyze foobartab;
ANALYZE
Time: 321.71 ms
slo=> select * from pg_stats where tablename = 'foobartab' and attname='foobar_id';
schemaname | tablename | attname | null_frac | avg_width | n_distinct | most_common_vals | most_common_freqs | histogram_bounds | correlation
------------+-----------+-----------+-----------+-----------+------------+--------------------------------------+---------------------------------------------------------------+----------------------------------------------+-------------
public | foobartab | foobar_id | 0 | 4 | 18 | {900,800,1000,700,600,2200,400,1900} | {0.134667,0.130667,0.129,0.116,0.110667,0.11,0.085,0.0723333} | {100,100,200,200,200,200,500,1200,2300,2700} | 1
(1 row)
Time: 10.93 ms
slo=> set random_page_cost = 0.6;
SET
Time: 4.89 ms
slo=> explain analyze select * from foobartab where foobar_id = 900;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------
Seq Scan on foobartab (cost=0.00..3967.61 rows=13269 width=192) (actual time=133.23..390.89 rows=11892 loops=1)
Filter: (foobar_id = 900)
Total runtime: 408.28 msec
(3 rows)
Time: 414.80 ms
slo=> set random_page_cost = 0.5;
SET
Time: 4.67 ms
slo=> explain analyze select * from foobartab where foobar_id = 900;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------
Index Scan using idx_foobartab on foobartab (cost=0.00..3564.34 rows=13269 width=192) (actual time=0.08..199.03 rows=11892 loops=1)
Index Cond: (foobar_id = 900)
Total runtime: 214.03 msec
(3 rows)
--
greg
From | Date | Subject | |
---|---|---|---|
Next Message | Ed L. | 2003-02-27 19:00:50 | Re: 7.4? |
Previous Message | Patrick Bye (WFF) | 2003-02-27 18:21:30 | Re: Help! I don't get mail anymore |