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

> 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

Try increasing your statistics target and re-running analyze. Try say 100?

Sincerely,

Joshua D. Drake




> 
> 
> 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).
> 
> 
> ---------------------------(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


-- 
Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC
Postgresql support, programming shared hosting and dedicated hosting.
+1-503-667-4564 - jd@commandprompt.com - http://www.commandprompt.com
Mammoth PostgreSQL Replicator. Integrated Replication for PostgreSQL