pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Joshua Marsh <icub3d@gmail.com>
Cc: pg@fastcrypt.com
Cc: pgsql-performance@postgresql.org
Subject: Re: Large Database Performance suggestions
Date: Tue, 26 Oct 2004 12:39:44 -0400
Message-ID: <10369.1098808784@sss.pgh.pa.us> (raw)
In-Reply-To: <38242de904102608246d060f7d@mail.gmail.com>
References: <38242de904102120143c55ba34@mail.gmail.com>
	<4178F149.9090408@fastcrypt.com>
	<38242de904102608246d060f7d@mail.gmail.com>

Joshua Marsh <icub3d@gmail.com> writes:
> shared_buffers = 1000           # min 16, at least max_connections*2, 8KB each

This is on the small side for an 8G machine.  I'd try 10000 or so.

> sort_mem = 4096000

Yikes.  You do realize you just said that *each sort operation* can use 4G?
(Actually, it's probably overflowing internally; I dunno what amount of
sort space you are really ending up with but it could be small.)  Try
something saner, maybe in the 10 to 100MB range.

> vacuum_mem = 1024000

This is probably excessive as well.

> #max_fsm_pages = 20000          # min max_fsm_relations*16, 6 bytes each
> #max_fsm_relations = 1000       # min 100, ~50 bytes each

You will need to bump these up a good deal to avoid database bloat.

> Occasionally, because we store data from several sources, we will have
> requests for data from several sources.  We simply intersect the
> view_of_data table with a sources table that lists what acctno belong
> to what source.  This query would look something like this:

> SELECT acctno FROM view_of_data WHERE has_name AND is_active_member
> AND state = 'OH' INTERSECT SELECT acctno FROM sources_data WHERE
> source = 175;

IMHO you need to rethink your table layout.  There is simply no way that
that query is going to be fast.  Adding a source column to view_of_data
would work much better.

If you're not in a position to redo the tables, you might try it as a
join:

SELECT acctno FROM view_of_data JOIN sources_data USING (acctno)
WHERE has_name AND is_active_member AND state = 'OH'
      AND source = 175;

but I'm not really sure if that will be better or not.

			regards, tom lane



view thread (8+ messages)  latest in thread

Message-ID: <10369.1098808784@sss.pgh.pa.us>
Permalink:  ../10369.1098808784@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/10369.1098808784@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, icub3d@gmail.com, pg@fastcrypt.com
  Subject: Re: Large Database Performance suggestions
  In-Reply-To: <10369.1098808784@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