Re: Large # of rows in query extremely slow, not using

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
<lists@boutiquenumerique.com> 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).