[Snort-users] PostgreSQL Indexes

Hutchinson, Andrew Andrew.Hutchinson at ...3639...
Wed Jun 12 06:24:04 EDT 2002

Here's a sample:

CREATE INDEX idx_event_timestamp ON event(timestamp)

The basic syntax is:

CREATE [UNIQUE] INDEX [indexname] ON tablename(columnname1[,...])

I've mainly used MySQL, but am cutting over to PostgreSQL so I can use
stored procedures and triggers.  In MySQL, I found that there is a
compound index on the events table, referencing (event.sid,event.cid).
It you use this index, be certain to query on both fields in that order
to take advantage of the index.  Otherwise queries looking for a
specific sid will be glacially slow - this was a problem that I noticed
with the early revs of SnortReport (they queried on sid only, not
sid.cid).  If you see this issue, you can either rewrite the queries to
use the compound index (preferable) or create another index on event.sid
only (less preferable, since you'll me unnecessarily re-indexing that

Hope this helps,

Andrew Hutchinson
Vanderbilt University Medical Center
NCS/Informatics/Network Security andrew.hutchinson at ...758...

-----Original Message-----
From: Gfm [mailto:gfm at ...6046...] 
Sent: Monday, June 10, 2002 8:39 AM
To: snort-users at lists.sourceforge.net
Subject: [Snort-users] PostgreSQL Indexes

Hi, I'm currently implementing snort on openbsd 3.1
using PostgreSQL.

I read in the ACID FAQ that' "Many of the required
indexes are not created in initial PostgreSQL creation
script. At a minimum the following fields should have
indexes created on them: " 

(DB schema < v103) iphdr.ip_src0 + iphdr.ip_src1 + iphdr.ip_src2 +
(DB schema < v103) iphdr.ip_dst0 + iphdr.ip_dst1 + iphdr.ip_dst2 +
acid_ag_alert.ag_sid + acid_ag_alert.ag_cid 

The problem it's that I'm pretty new on PostgreSQL,
can anyone please help me in creating those indexes. 

It's recommended to create any other index to improve
the ACID/Snortreport performance?

Thanks a lot for your help



Don't miss the 2002 Sprint PCS Application Developer's Conference August
25-28 in Las Vegas -

Snort-users mailing list
Snort-users at lists.sourceforge.net
Go to this URL to change user options or unsubscribe:
Snort-users list archive:

More information about the Snort-users mailing list