> 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?
Try increasing your statistics target and re-running analyze. Try say 100?
Joshua D. Drake
> On Tue, 14 Sep 2004 21:27:55 +0200, Pierre-Frédéric Caillaud
> <lists(at)boutiquenumerique(dot)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(at)commandprompt(dot)com - http://www.commandprompt.com
Mammoth PostgreSQL Replicator. Integrated Replication for PostgreSQL
Description: text/x-vcard (640 bytes)
In response to
pgsql-performance by date
|Next:||From: Simon Riggs||Date: 2004-09-17 07:39:10|
|Subject: Re: Data Warehouse Reevaluation - MySQL vs Postgres --|
|Previous:||From: Stephen Crowley||Date: 2004-09-17 01:51:11|
|Subject: Re: Large # of rows in query extremely slow, not using|