Skip site navigation (1) Skip section navigation (2)

Tuning pointers for the DB.

From: Warren Vanichuk <pyber(at)street-light(dot)com>
To: pgsql-admin(at)postgresql(dot)org
Subject: Tuning pointers for the DB.
Date: 2000-11-22 23:01:14
Message-ID: Pine.LNX.4.21.0011221418370.1722-100000@urd.street-light.com (view raw or flat)
Thread:
Lists: pgsql-admin
Greetings.  :)

Having just gotten our database all planned out and implimented, and rolled
out live, I'd like to now take the time to sit back and tweak the system up
to get the most bang for my processing buck so to speak.

The database itself isn't that large, comprising 14 tables.  Most of the
tables are fairly static, only getting new information added to them every
week or so, if they are lucky.  4 tables however, get alot of
updates/inserts done onto them with each web request, which averages 2-3
requests a second.

The general flow of the data is like :
	-> User hits a webpage.

	-> A storage procedure is called that updates a pageview table,
           simply incrementing a counter.

	-> The program selects N links out of the database from a view,
           based on various criteria like category Y.

	-> For the N links selected out of the database a storage procedure
           is called that either inserts a row into two tables (if the data
           doesn't already exist) or updates a row in two tables (if the
           data already exists).

The entire thing is done inside a transaction (BEGIN/END).

And now the questions begin.. :)

How often should I vacuum, given than the 2 statistics tables being updated
constantly are joined in the view to produce the list of links?  When is a
vacuum analyze a good idea in this instance? :)

I have setup an index on the two stats tables for the linkid, but I don't
want to go overboard with them, as warned against in the manual.  However,
given the following setup :

table1             table2
-------------      ----------
linkid int4	   linkid int4
linkmax int4       linkcur int4
linkurl text       linkcat int4
		   linkloc int4

And the query :

select linkid, linkurl from table1, table2 where table1.linkid =
table2.linkid and table1.linkmax < table2.linkcur

Is there any benefit to setting up an index on table2 like :

create index table2_linkidcur_idx on table2 (linkid, linkcur ); ?

Basically I'm wondering when it's a good idea to create indexes, if there is
an easy way to spot that an index is needed, etc..  :)

Any information and pointers anybody could offer would be greatly
apprieciated. :)

Sincerely, Warren


pgsql-admin by date

Next:From: Thomas HellerDate: 2000-11-23 09:52:19
Subject: Re: Lack of Performance
Previous:From: Oliver ElphickDate: 2000-11-22 20:25:44
Subject: Re: [GENERAL] Logging

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group