X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id ACDCD329DFC for ; Fri, 17 Sep 2004 02:51:25 +0100 (BST) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 64269-05 for ; Fri, 17 Sep 2004 01:51:13 +0000 (GMT) Received: from mproxy.gmail.com (rproxy.gmail.com [64.233.170.201]) by svr1.postgresql.org (Postfix) with ESMTP id 81CE9329DB1 for ; Fri, 17 Sep 2004 02:51:15 +0100 (BST) Received: by mproxy.gmail.com with SMTP id v30so107836rnb for ; Thu, 16 Sep 2004 18:51:11 -0700 (PDT) Received: by 10.38.8.54 with SMTP id 54mr202461rnh; Thu, 16 Sep 2004 18:51:11 -0700 (PDT) Received: by 10.38.8.79 with HTTP; Thu, 16 Sep 2004 18:51:11 -0700 (PDT) Message-ID: <3f71fdf1040916185113e277e6@mail.gmail.com> Date: Thu, 16 Sep 2004 20:51:11 -0500 From: Stephen Crowley Reply-To: Stephen Crowley To: pgsql-performance@postgresql.org Subject: Re: Large # of rows in query extremely slow, not using Cc: =?ISO-8859-1?Q?Pierre-Fr=E9d=E9ric_Caillaud?= In-Reply-To: Mime-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable References: <3f71fdf104091317515c756512@mail.gmail.com> <20040914184358.08e271fe@kingfisher.intern.logi-track.com> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200409/184 X-Sequence-Number: 8312 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=3D'2004-09-07' and stock=3D'ORCL' LIMIT 10; "Limit (cost=3D0.00..17.92 rows=3D10 width=3D83) (actual time=3D1612.000..1702.000 rows=3D10 loops=3D1)" " -> Index Scan using island_history_date_stock_time on island_history (cost=3D0.00..183099.72 rows=3D102166 width=3D83) (actual time=3D1612.000..1702.000 rows=3D10 loops=3D1)" " Index Cond: ((date =3D '2004-09-07'::date) AND ((stock)::text =3D '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=3D'2004-09-07' and stock=3D'MSFT' LIMIT 10; "Limit (cost=3D0.00..14.30 rows=3D10 width=3D83) (actual time=3D346759.000..346759.000 rows=3D10 loops=3D1)" " -> Seq Scan on island_history (cost=3D0.00..417867.13 rows=3D292274 width=3D83) (actual time=3D346759.000..346759.000 rows=3D10 loops=3D1)" " Filter: ((date =3D '2004-09-07'::date) AND ((stock)::text =3D '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=E9d=E9ric Caillaud wrote: >=20 > >> 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. >=20 > 1; EXPLAIN ANALYZE. >=20 > Note the time it takes. It should not swap, just read data from t= he disk > (and not kill the machine).