RE: PostgreSQL Indexes

["Hutchinson, Andrew" <Andrew.Hutchinson () Vanderbilt edu>]

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,

Andrew Hutchinson
Vanderbilt University Medical Center
NCS/Informatics/Network Security andrew.hutchinson () vanderbilt edu

-----Original Message-----
From: Gfm [mailto:gfm () fibertel com ar] 
Sent: Monday, June 10, 2002 8:39 AM
To: snort-users () 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: " 

event.timestamp 
event.signature 
signature.sig_name 
iphdr.ip_src 
(DB schema < v103) iphdr.ip_src0 + iphdr.ip_src1 + iphdr.ip_src2 +
iphdr.ip_src3 
iphdr.ip_dst 
(DB schema < v103) iphdr.ip_dst0 + iphdr.ip_dst1 + iphdr.ip_dst2 +
iphdr.ip_dst3 
tcphdr.tcp_sport 
tcphdr.tcp_dport 
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

Gus


_______________________________________________________________

Don't miss the 2002 Sprint PCS Application Developer's Conference August
25-28 in Las Vegas -
http://devcon.sprintpcs.com/adp/index.cfm?source=osdntextlink

_______________________________________________
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

_______________________________________________________________

Sponsored by:
ThinkGeek at http://www.ThinkGeek.com/
_______________________________________________
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