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