Ok.. now I ran "VACUUM FULL' and things seem to be working as they should..
explain analyze select * from history where date='2004-09-07' and stock='MSFT'; Seq Scan on island_history (cost=0.00..275359.13 rows=292274 width=83) (actual time=50.000..411683.000 rows=265632 loops=1) Filter: ((date = '2004-09-07'::date) AND ((stock)::text = 'MSFT'::text)) Total runtime: 412703.000 ms random_page_cost and effective_cache_size are both default, 8 and 1000 explain analyze select * from history where date='2004-09-07' and stock='ORCL'; "Index Scan using island_history_date_stock_time on island_history (cost=0.00..181540.07 rows=102166 width=83) (actual time=551.000..200268.000 rows=159618 loops=1)" " Index Cond: ((date = '2004-09-07'::date) AND ((stock)::text = 'ORCL'::text))" "Total runtime: 201009.000 ms" So now this in all in proportion and works as expected.. the question is, why would the fact that it needs to be vaccumed cause such a huge hit in performance? When i vacuumed it did free up nearly 25% of the space. --Stephen On Fri, 17 Sep 2004 22:44:05 +0200, Manfred Koizar <[EMAIL PROTECTED]> wrote: > On Thu, 16 Sep 2004 20:51:11 -0500, Stephen Crowley > <[EMAIL PROTECTED]> wrote: > >explain analyze select * from history where date='2004-09-07' and > >stock='ORCL' LIMIT 10; > > >" -> Index Scan using island_history_date_stock_time on > >island_history (cost=0.00..183099.72 rows=102166 width=83) (actual > >time=1612.000..1702.000 rows=10 loops=1)" > ^^ > LIMIT 10 hides what would be the most interesting info here. I don't > believe that > EXPLAIN ANALYSE SELECT * FROM history WHERE ... > consumes lots of memory. Please try it. > > And when you post the results please include your Postgres version, some > info about hardware and OS, and your non-default settings, especially > random_page_cost and effective_cache_size. > > May I guess that the correlation of the physical order of tuples in your > table to the contents of the date column is pretty good (examine > correlation in pg_stats) and that island_history_date_stock_time is a > 3-column index? > > It is well known that the optimizer overestimates the cost of index > scans in those situations. This can be compensated to a certain degree > by increasing effective_cache_size and/or decreasing random_page_cost > (which might harm other planner decisions). > > You could also try > CREATE INDEX history_date_stock ON history("date", stock); > > This will slow down INSERTs and UPDATEs, though. > > Servus > Manfred > ---------------------------(end of broadcast)--------------------------- TIP 6: Have you searched our list archives? http://archives.postgresql.org