pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Gaetano Mendola <mendola@bigfoot.com>
To: Steve <steve@hotmail.com>
Subject: Re: Utilizing multiple disks
Date: Mon, 26 Jul 2004 13:59:12 +0200
Message-ID: <4104F210.8040600@bigfoot.com> (raw)
In-Reply-To: <ce1nt2$1q9f$1@news.hub.org>
References: <cdmv71$1u0m$4@news.hub.org>
	<4102F8EF.7080504@bigfoot.com>
	<ce1nt2$1q9f$1@news.hub.org>

Steve wrote:

> Gaetano,
> 
> Thanks for your reply.
> 
> Gaetano Mendola wrote:
> 
>> Are you performing queries using the like operator? If yes did you define
>> an index on it using the right operator class ?
> 
> 
> Yes I do use the like operator to perform queries on a few 'varchar' and 
> 'text' fields. I have indexed almost every column in the table (because 
> the client application allows complex searches on multiple columns and 
> each column has an equal chance of being used in the 'WHERE' condition)

And did you use the right operator class ?
See http://www.postgresql.org/docs/7.4/interactive/indexes-opclass.html
for details

>> Are you performing queries on a calculated field ? If yes then you need
>> to construct a sort of materialized view.
> 
> 
> Hmm not sure what you mean by a calculated field. Since I'm not sure 
> what it means, I'm probably not using it. I'm definitely not running any 
> functions on columns to get an accumulated result back etc.

if you have for example a view like:

CREATE VIEW v_test AS
SELECT  foo(a)    AS  field_1,
         bar(a+b)  AS  field_2
FROM  test;


and you do:   SELECT * from v_test WHERE field_2 = 123;

then you are in trouble!


>> If you are on Linux did you mounted your data partition with the option
>> noatime ?
> 
> 
> Yes I'm on linux and am using the ext3 filesystem. I am just about to 
> mount the data partition with the noatime option. Docos do say that the 
> speed increases considerably. I'll give it a shot but I'm still very 
> interested in utilizing the extra three SCSI disks I have at my disposal.

mount that disk in RAID configuration then, you can at least try to move the
WAL on a separate disk.

> Database integrity is of utmost importance and so is speed. I know there 
> are tradeoffs but I really do think that moving the data to a separate 
> disk will help. Can you please tell me how to set this up? Thanks a lot!
> 
> Steve

The version 7.5 will give you the tablespaces  feature, in the mean time you
can move your table and or index across your disk using symlinks, moving the
file associated with your table or index in another disk and replace it with a
link.


Regards
Gaetano Mendola


















view thread (3+ messages)

Message-ID: <4104F210.8040600@bigfoot.com>
Permalink:  ../4104F210.8040600@bigfoot.com/
Also on:    postgresql.org/message-id/4104F210.8040600@bigfoot.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-admin@postgresql.org
  Cc: mendola@bigfoot.com, steve@hotmail.com
  Subject: Re: Utilizing multiple disks
  In-Reply-To: <4104F210.8040600@bigfoot.com>
  References: <cdmv71$1u0m$4@news.hub.org> <4102F8EF.7080504@bigfoot.com> <ce1nt2$1q9f$1@news.hub.org> <4104F210.8040600@bigfoot.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