pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Rod Taylor <rbt@rbt.ca>
To: Patrick Hatcher <PHatcher@macys.com>
Cc: Postgresql Performance <pgsql-performance@postgresql.org>
Subject: Re: Slow query on OS X box
Date: 22 Jan 2003 15:02:05 -0500
Message-ID: <1043265724.83856.147.camel@jester> (raw)
In-Reply-To: <OF5B777E26.43FD52D5-ON88256CB6.00635521@fds.com>
References: <OF5B777E26.43FD52D5-ON88256CB6.00635521@fds.com>

On Wed, 2003-01-22 at 13:26, Patrick Hatcher wrote:
> I have a table that contains over 13 million rows.  This query takes an
> extremely long time to return.  I've vacuum full, analyzed, and re-indexed
> the table. Still the results are the same.  Any ideas?

Yeah, you're pulling out 3.2 million rows from (possibly) a wide table
bytewise.  Do all of those fields actually have data?  Thats always
going to take a while -- and I find it hard to believe you're actually
doing something with all of those rows that runs regularly.

If every one of those rows was maxed out (ignoring the text field at the
end) you could have ~ 15GB of data to pull out.  Without knowing the
type of data actually in the table, I'm going to bet it's a harddrive
limitation.

The index on 'wizard' is next to useless as at least 1/4 of the data in
the table is under the same key.  You might try a partial index on
'wizard' (skip the value 'Keyword').  It won't help this query, but
it'll help ones looking for values other than 'Keyword'.

Anyway, you might try a CURSOR.  Fetch rows out 5000 at a time, do some
work with them, then grab some more.  This -- more or less -- will allow
you to process the rows received while awaiting the remaining lines to
be processed by the database. Depending on what you're doing with them
it'll give a chance for the diskdrive to catch up.  If the kernels smart
it'll read ahead of the scan.  This doesn't remove read time, but hides
it while you're transferring the data out (from the db to your client)
or processing it.

> mdc_oz=# explain analyze select wizard from search_log where wizard
> ='Keyword' and sdate between '2002-12-01' and '2003-01-15';
>                                                          QUERY PLAN
> -----------------------------------------------------------------------------------------------------------------------------
>  Seq Scan on search_log  (cost=0.00..609015.34 rows=3305729 width=10)
> (actual time=99833.83..162951.25 rows=3280573 loops=1)
>    Filter: ((wizard = 'Keyword'::character varying) AND (sdate >
> = '2002-12-01'::date) AND (sdate <= '2003-01-15'::date))
>  Total runtime: 174713.25 msec
> (3 rows)
-- 
Rod Taylor <rbt@rbt.ca>

PGP Key: http://www.rbt.ca/rbtpub.asc

Attachments:

  signature.asc (application/pgp-signature, 186B) download

view thread (8+ messages)  latest in thread

Message-ID: <1043265724.83856.147.camel@jester>
Permalink:  ../1043265724.83856.147.camel@jester/
Also on:    postgresql.org/message-id/1043265724.83856.147.camel@jester

 · 

reply

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 from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-performance@postgresql.org
  Cc: rbt@rbt.ca, PHatcher@macys.com
  Subject: Re: Slow query on OS X box
  In-Reply-To: <1043265724.83856.147.camel@jester>
  References: <OF5B777E26.43FD52D5-ON88256CB6.00635521@fds.com> <1043265724.83856.147.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