pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Chris Cheston <ccheston@gmail.com>
Cc: Josh Berkus <josh@agliodbs.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: postgres 7.4 at 100%
Date: Tue, 29 Jun 2004 09:50:32 -0400
Message-ID: <28630.1088517032@sss.pgh.pa.us> (raw)
In-Reply-To: <e071108e0406290100338c171d@mail.gmail.com>
References: <e071108e04062423591d2b8aa9@mail.gmail.com>
	<1088397641.1105.10.camel@localhost>
	<17884.1088399982@sss.pgh.pa.us>
	<200406280947.59420.josh@agliodbs.com>
	<e071108e0406290100338c171d@mail.gmail.com>

Chris Cheston <ccheston@gmail.com> writes:
> Wow, this simple query is taking 676.24 ms to execute! it only takes
> 18 ms on our other machine.

> This table has 150,000 rows. Is this normal?

> live=# explain analyze SELECT id FROM calllogs WHERE from = 'you';
>                                                 QUERY PLAN
> ----------------------------------------------------------------------------------------------------------
>  Seq Scan on calllogs  (cost=0.00..136.11 rows=24 width=4) (actual
> time=0.30..574.72 rows=143485 loops=1)
>    Filter: (from = 'you'::character varying)
>  Total runtime: 676.24 msec
> (3 rows)

So the query is pulling 140K+ rows out of a table with 150K entries?
No chance that an index will help for that.  You're fortunate that the
thing did not try to use an index though, because it thinks there are
only 24 rows matching 'you', which is one of the more spectacular
statistical failures I've seen lately.  I take it you haven't ANALYZEd
this table in a long time?

It is hard to believe that your other machine can pull 140K+ rows in
18 msec, though.  Are you sure the table contents are the same in both
cases?

If they are, the only reason I can think of for the discrepancy is a
large amount of dead space in this copy of the table.  What does VACUUM
VERBOSE show for it, and how does that compare to what you see on the
other machine?  Try a CLUSTER or VACUUM FULL to see if you can shrink
the table's physical size (number of pages).

			regards, tom lane



view thread (24+ messages)  latest in thread

Message-ID: <28630.1088517032@sss.pgh.pa.us>
Permalink:  ../28630.1088517032@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/28630.1088517032@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 using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: tgl@sss.pgh.pa.us, ccheston@gmail.com, josh@agliodbs.com
  Subject: Re: postgres 7.4 at 100%
  In-Reply-To: <28630.1088517032@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