From: Rod Taylor <rbt@rbt.ca>
To: Stephen Crowley <stephen.crowley@gmail.com>
Cc: Postgresql Performance <pgsql-performance@postgresql.org>
Subject: Re: Large # of rows in query extremely slow, not using
Date: Mon, 13 Sep 2004 21:11:13 -0400
Message-ID: <1095124271.79631.44.camel@jester> (raw)
In-Reply-To: <3f71fdf104091317515c756512@mail.gmail.com>
References: <3f71fdf104091317515c756512@mail.gmail.com>
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.
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.
>
> Running postgres 8.0 beta2 dev2
>
> explain select * from island_history where date='2004-09-07' and stock='QQQ';
> QUERY PLAN
> ---------------------------------------------------------------------------
> Seq Scan on island_history (cost=0.00..266711.23 rows=896150 width=83)
> Filter: ((date = '2004-09-07'::date) AND ((stock)::text = 'QQQ'::text))
> (2 rows)
>
> Any help would be appreciated
>
> --Stephen
>
> 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)
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend
--
Rod Taylor <rbt [at] rbt [dot] ca>
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
PGP Key: http://www.rbt.ca/signature.ascAttachments:
signature.asc (application/pgp-signature, 186B) download
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: rbt@rbt.ca, stephen.crowley@gmail.com
Subject: Re: Large # of rows in query extremely slow, not using
In-Reply-To: <1095124271.79631.44.camel@jester>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox