[Snort-users] How to create the DB indices with postgresql

Ben bench at ...6191...
Thu Jun 27 09:58:03 EDT 2002


FYI, with Postgres 7.2 you can simply run ANALYZE <tablename>, which is much
faster than a vacuum and doesn't lock the table.

----- Original Message -----
From: "Hutchinson, Andrew" <Andrew.Hutchinson at ...3639...>
To: "Daniel Lang" <dl at ...6187...>
Cc: <snort-users at lists.sourceforge.net>
Sent: Thursday, June 27, 2002 7:23 AM
Subject: RE: [Snort-users] How to create the DB indices with postgresql


Daniel:

AFAIK, you get a performance boost on SELECTs from a UNIQUE index as
opposed to a normal index (due to the fact that once a single match is
found, the query can return the result and cease the index scan).  I'm
not sure how significant this boost is, but according to my Postgresql
references, it is worthwhile to opt for UNIQUE when possible.
Additionally, the UNIQUE keyword will enforce data integrity - it
somebody tries to insert a duplicate, you'll know about it, which may be
desirable.

One more pointer with Postgres - you mentioned that you had added some
indices, but saw no benefit.  It is possible to create an "good" index
(i.e. an index that _should_ improve performance), but see no
performance benefit.  If this happens, it is likely because the system
tables used by the query optimizer to develop its execution plan have
not been updated.  If this happens, you can use the VACUUM statement
like this:

VACUUM VERBOSE ANALYZE [tablename];

Then re-run your query, and it should be optimized properly.  The
VERBOSE statement is optional, but I like to see what's going on...

Again, hope this helps.

Andrew





More information about the Snort-users mailing list