Skip site navigation (1) Skip section navigation (2)

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

From: "Joshua D(dot) Drake" <jd(at)commandprompt(dot)com>
To: Stephen Crowley <stephen(dot)crowley(at)gmail(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org, Pierre-Frédéric Caillaud <lists(at)boutiquenumerique(dot)com>
Subject: Re: Large # of rows in query extremely slow, not using
Date: 2004-09-17 03:14:16
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-performance
> 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?


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 -
Mammoth PostgreSQL Replicator. Integrated Replication for PostgreSQL

Attachment: jd.vcf
Description: text/x-vcard (640 bytes)

In response to

pgsql-performance by date

Next:From: Simon RiggsDate: 2004-09-17 07:39:10
Subject: Re: Data Warehouse Reevaluation - MySQL vs Postgres --
Previous:From: Stephen CrowleyDate: 2004-09-17 01:51:11
Subject: Re: Large # of rows in query extremely slow, not using

Privacy Policy | About PostgreSQL
Copyright © 1996-2018 The PostgreSQL Global Development Group