Optimizer going cuckoo for full table scans

Started by Bruce Momjianabout 23 years ago1 messagesgeneral
Jump to latest
#1Bruce Momjian
bruce@momjian.us

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