Here are some results of explain analyze, I've included the LIMIT 10 because otherwise the resultset would exhaust all available memory.
explain analyze select * from history where date='2004-09-07' and stock='ORCL' LIMIT 10; "Limit (cost=0.00..17.92 rows=10 width=83) (actual time=1612.000..1702.000 rows=10 loops=1)" " -> 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)" " Index Cond: ((date = '2004-09-07'::date) AND ((stock)::text = 'ORCL'::text))" "Total runtime: 1702.000 ms" Ok, so for 100,000 rows it decides to use the index and returns very quicktly.. now for explain analyze select * from history where date='2004-09-07' and stock='MSFT' LIMIT 10; "Limit (cost=0.00..14.30 rows=10 width=83) (actual time=346759.000..346759.000 rows=10 loops=1)" " -> Seq Scan on island_history (cost=0.00..417867.13 rows=292274 width=83) (actual time=346759.000..346759.000 rows=10 loops=1)" " Filter: ((date = '2004-09-07'::date) AND ((stock)::text = 'MSFT'::text))" "Total runtime: 346759.000 ms" Nearly 8 minutes.. Why would it take this long? Is there anything else I can do to debug this? When I set enable_seqscan to OFF and force everything to use the index every stock I query returns within 100ms, but turn seqscan back ON and its back up to taking several minutes for non-index using plans. Any ideas? --Stephen On Tue, 14 Sep 2004 21:27:55 +0200, Pierre-Frédéric Caillaud <[EMAIL PROTECTED]> wrote: > > >> I have a table with ~8 million rows and I am executing a query which > >> should return about ~800,000 rows. The problem is that as soon as I > >> execute the query it absolutely kills my machine and begins swapping > >> for 5 or 6 minutes before it begins returning results. Is postgres > >> trying to load the whole query into memory before returning anything? > >> Also, why would it choose not to use the index? It is properly > >> estimating the # of rows returned. If I set enable_seqscan to off it > >> is just as slow. > > 1; EXPLAIN ANALYZE. > > Note the time it takes. It should not swap, just read data from the disk > (and not kill the machine). ---------------------------(end of broadcast)--------------------------- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match