Optimizer going cuckoo for full table scans

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

Browse pgsql-general by date

  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