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 88CF7329E88 for ; Tue, 14 Sep 2004 02:11:19 +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 31579-06 for ; Tue, 14 Sep 2004 01:11:11 +0000 (GMT) Received: from tht.net (vista.tht.net [216.126.88.2]) by svr1.postgresql.org (Postfix) with ESMTP id CEBDA329E8E for ; Tue, 14 Sep 2004 02:11:13 +0100 (BST) Received: from [134.22.70.99] (dyn-70-99.tor.dsl.tht.net [134.22.70.99]) by tht.net (Postfix) with ESMTP id 5A3D276B25; Mon, 13 Sep 2004 21:11:19 -0400 (EDT) Subject: Re: Large # of rows in query extremely slow, not using From: Rod Taylor To: Stephen Crowley Cc: Postgresql Performance In-Reply-To: <3f71fdf104091317515c756512@mail.gmail.com> References: <3f71fdf104091317515c756512@mail.gmail.com> Content-Type: multipart/signed; micalg=pgp-sha1; protocol="application/pgp-signature"; boundary="=-e+iD4DbyehpoRLK7c36o" Message-Id: <1095124271.79631.44.camel@jester> Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.4.6 Date: Mon, 13 Sep 2004 21:11:13 -0400 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/96 X-Sequence-Number: 8224 --=-e+iD4DbyehpoRLK7c36o Content-Type: text/plain Content-Transfer-Encoding: quoted-printable On Mon, 2004-09-13 at 20:51, Stephen Crowley wrote: > Does postgres cache the entire result set before it begins returning > data to the client? Sometimes you need to be careful as to how the clients treat the data.=20 For example psql will resize columns width on the length (width) of the data returned. PHP and Perl will retrieve and cache all of the rows if you request a row count ($sth->rows() or pg_num_rows($rset)) You may find that using a cursor will help you out. > 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 > Running postgres 8.0 beta2 dev2 >=20 > explain select * from island_history where date=3D'2004-09-07' and stock= =3D'QQQ'; > QUERY PLAN > -------------------------------------------------------------------------= -- > Seq Scan on island_history (cost=3D0.00..266711.23 rows=3D896150 width= =3D83) > Filter: ((date =3D '2004-09-07'::date) AND ((stock)::text =3D 'QQQ'::t= ext)) > (2 rows) >=20 > Any help would be appreciated >=20 > --Stephen >=20 > Table "public.island_history" > Column | Type | Modifiers > ------------------+------------------------+----------- > date | date | not null > stock | character varying(6) | > time | time without time zone | not null > reference_number | numeric(9,0) | not null > message_type | character(1) | not null > buy_sell_ind | character(1) | > shares | numeric(6,0) | > remaining_shares | numeric(6,0) | > price | numeric(10,4) | > display | character(1) | > match_number | numeric(9,0) | not null > Indexes: > "island_history_pkey" PRIMARY KEY, btree (date, reference_number, > message_type, "time", match_number) > "island_history_date_stock_time" btree (date, stock, "time") > "island_history_oid" btree (oid) >=20 > ---------------------------(end of broadcast)--------------------------- > TIP 8: explain analyze is your friend --=20 Rod Taylor Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL PGP Key: http://www.rbt.ca/signature.asc --=-e+iD4DbyehpoRLK7c36o Content-Type: application/pgp-signature; name=signature.asc Content-Description: This is a digitally signed message part -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.2.5 (FreeBSD) iD8DBQBBRkUv6DETLow6vwwRAqUzAJ9JLNSP9hV8HBDSF0JUV7Eeowh1EQCfXvT5 ILK4piXRrr2f+kCkB02CJHE= =Lddw -----END PGP SIGNATURE----- --=-e+iD4DbyehpoRLK7c36o--