pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Patrick Hatcher <PHatcher@macys.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Slow query on OS X box
Date: Wed, 22 Jan 2003 17:03:55 -0500
Message-ID: <24609.1043273035@sss.pgh.pa.us> (raw)
In-Reply-To: <OF5B777E26.43FD52D5-ON88256CB6.00635521@fds.com>
References: <OF5B777E26.43FD52D5-ON88256CB6.00635521@fds.com>

"Patrick Hatcher" <PHatcher@macys.com> writes:
> 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?

> 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)

This query is selecting 3280573 rows out of your 13 million.  I'd say
the machine is doing the best it can.  Returning 19000 rows per second
is not all that shabby.

Perhaps you should rethink what you're doing.  Do you actually need to
return 3 million rows to the client?

			regards, tom lane



view thread (8+ messages)  latest in thread

Message-ID: <24609.1043273035@sss.pgh.pa.us>
Permalink:  ../24609.1043273035@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/24609.1043273035@sss.pgh.pa.us

 · 

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: tgl@sss.pgh.pa.us, PHatcher@macys.com
  Subject: Re: Slow query on OS X box
  In-Reply-To: <24609.1043273035@sss.pgh.pa.us>
  References: <OF5B777E26.43FD52D5-ON88256CB6.00635521@fds.com> <24609.1043273035@sss.pgh.pa.us>

* 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