RE: postgresql indexes

["Hutchinson, Andrew" <andrew.hutchinson () Vanderbilt Edu>]

Run a "\di" at your psql prompt to see what indices you already have.

A common initial reaction is to throw an index on anything and
everything.  You have the disk and RAM, right?  It'll make your SELECTs
fast, right?  Bad idea.  Indices will help you when getting data _out_
of the db.  However, every time you want to put something _into_ the
database, you need to update every index that references an indexed
column that was inserted.  Ditto deleting or updating.  Over-indexing
can cause you unending grief, regardless of how much HDD, RAM, and CPU
power that you have.

You really need to look at what ACID is querying, look at the current
index catalog, and see if there is anywhere that a well placed index can
improve performance.  I think that if you look, you'll find that there
are already a lot of indices on your db.

I don't use ACID so I haven't done this recently, but here are some
pointers about how to optimize an application hitting a PgSQL (or,
really, any other database engine...) database:

- If you want to tune ACID, go into the PHP and find the queries that
correspond with the long-running operations that are causing you grief.
Run them manually, but preface the query with the EXPLAIN keyword.  This
will cause Postgresql to run the query through the optimizer and
describes the query plan.  You can see where indices are being used, and
where sequential scans (the sometimes unavoidable yet costly operations
where every row on a table is evaluated) are taking place.  If you can
add an index that allows a huge sequential scan to be converted to an
index scan, that's generally a good thing.

- After making any index changes, be sure to run ANALYZE VERBOSE (the
VERBOSE is optional, but it'll show you what it's doing...) so that the
column statistics can be updated and the query optimizer can be updated
with the new index information.  If you place an index and don't run
this, the index will go unused!

- Tune the database.  There are a lot of parameters, so get a good
PostgreSQL book before doing this - there are a number of them.  The
database can be tuned very significantly.  If you have not tuned it at
all, you need to do so.  I took a class w/ Bruce Momjian (one of the
creators and maintainers of PostgreSQL) and he noted that the default
configuration file that ships with PostgreSQL is intended to allow the
db to _run_ on nearly any hardware platform... but not necessarily run
WELL.  The default settings are nowhere near optimal for any
platform/configuration.  You HAVE to tune your settings, and there are a
whole lot of them (look at the postgresql.conf file in the root of your
database cluster, and you'll find the file that contains most of the
parameters.)



Hope that these pointers help,

Andrew

Andrew Hutchinson - Network Security
Vanderbilt University Medical Center
(615) 936-2856


> -----Original Message-----
> From: Bryan Irvine [mailto:bryan.irvine () kingcountyjournal com] 
> Sent: Friday, August 08, 2003 1:28 PM
> To: 'snort-users () lists sourceforge net'
> Subject: [Snort-users] postgresql indexes
>
>
> I want to be able to speed up my ACID queries and found a nifty little
> thing called "indexes".  Whoa, this is neat, uhm, any suggestions on
> what tables and columns should be indexed?  Taking into account that I
> have a metric buttload of hd space and ram what indexes would you
> create?  
>
> --Bryan
>
>
>
> -------------------------------------------------------
> This SF.Net email sponsored by: Free pre-built ASP.NET sites including
> Data Reports, E-commerce, Portals, and Forums are available now.
> Download today and enter to win an XBOX or Visual Studio .NET.
> http://aspnet.click-url.com/go/psa00100003ave/direct;at.aspnet
> _072303_01/01
> _______________________________________________
> Snort-users mailing list
> Snort-users () lists sourceforge net
> Go to this URL to change user options or unsubscribe:
> https://lists.sourceforge.net/lists/listinfo/snort-users
> Snort-users list archive:
> http://www.geocrawler.com/redir-sf.php3?list=snort-users


-------------------------------------------------------
This SF.Net email sponsored by: Free pre-built ASP.NET sites including
Data Reports, E-commerce, Portals, and Forums are available now.
Download today and enter to win an XBOX or Visual Studio .NET.
http://aspnet.click-url.com/go/psa00100003ave/direct;at.aspnet_072303_01/01
_______________________________________________
Snort-users mailing list
Snort-users () lists sourceforge net
Go to this URL to change user options or unsubscribe:
https://lists.sourceforge.net/lists/listinfo/snort-users
Snort-users list archive:
http://www.geocrawler.com/redir-sf.php3?list=snort-users