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

_______________________________________________________________

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