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=11892loops=1)
Index Cond: (foobar_id = 900)
Total runtime: 214.03 msec
(3 rows)
--
greg
