[Snort-users] PostgreSQL Indexes
Andrew.Hutchinson at ...3639...
Thu Jun 13 17:44:09 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 field).
Hope this helps,
Vanderbilt University Medical Center
andrew.hutchinson at ...758...
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
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