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 =
"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 =
"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?

On Tue, 14 Sep 2004 21:27:55 +0200, Pierre-Frédéric Caillaud
> >> 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

Reply via email to