pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Tomas Szepe <szepe@pinerecords.com>
To: Todd Nemanich <todd@twopunks.org>
Cc: pgsql-bugs@postgresql.org
Subject: Re: db growing out of proportion
Date: Sat, 31 May 2003 01:08:21 +0200
Message-ID: <20030530230821.GG21944@louise.pinerecords.com> (raw)
In-Reply-To: <3ED769E6.3050300@twopunks.org>
References: <20030529163239.GA11101@louise.pinerecords.com>
	<3ED769E6.3050300@twopunks.org>

> [todd@twopunks.org]
> 
> Additionally, you may want to take a look at your query performance. Are 
> most of your queries doing sequential scans? In my system, the crucial 
> columns of the primary tables are int8 and float8 fields. I have those 
> indexed, and I get a serious performance boost by making sure all 
> SELECT/UPDATE/DELETE queries that use those columns in the WHERE have an 
> explicit ::int8 or ::float8 (Explain analyze is your friend). During 
> peak usage, there is an order of magnitude difference (usually 10 to 
> 15x) between queries doing sequential scans on the table, and queries 
> doing index scans. Might be worth investigating if your queries are 
> taking 5 seconds when your DB is fresh. HTH.

Yes, I have taken special care to fine-tune all queries on authentic
data.  The db setup works as expected in whatever respect with the
exception of query times deterioration that apparently corelates to
the db's on-disk size growth.

Thanks for your suggestions,

-- 
Tomas Szepe <szepe@pinerecords.com>



view thread (15+ messages)  latest in thread

Message-ID: <20030530230821.GG21944@louise.pinerecords.com>
Permalink:  ../20030530230821.GG21944@louise.pinerecords.com/
Also on:    postgresql.org/message-id/20030530230821.GG21944@louise.pinerecords.com

 · 

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: szepe@pinerecords.com, todd@twopunks.org, pgsql-bugs@postgresql.org
  Subject: Re: db growing out of proportion
  In-Reply-To: <20030530230821.GG21944@louise.pinerecords.com>
  References: <20030529163239.GA11101@louise.pinerecords.com> <3ED769E6.3050300@twopunks.org> <20030530230821.GG21944@louise.pinerecords.com>

* 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