RE: How to create the DB indices with postgresql

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

Daniel:

Hopefully this will answer a couple of your questions.

1. Your syntax on the CREATE INDEX statement looks good.  The name of
the index should not matter - however, I usually use a notation that
will let me know that that entity is an index (something like
"idx_event_timestamp" or "ev_ts_idx" or something else that has meaning
to you and will be decipherable for others).

2.  Without the error messages you are receiving regarding UNIQUE keys,
it's difficult to say what is going on.  If you try to specify a unique
key on a column that contains duplicate values, or try to insert a
duplicate value into a unique column, postgresql will raise an error
condition.  In this specific instance (event.timestamp) I would NOT
specify UNIQUE, because it is very possible that two events can have the
same timestamp.

3.  I don't use acid, but I would assume from the fields referenced by
"acid_ag_alert.ag_sid + acid_ag_alert.ag_cid" that they are referring to
the creation of a compound index.  This would be accomplished like this:

        CREATE UNIQUE INDEX idx_ag_sid_cid ON
acid_ag_alert(ag_sid,ag_cid);

I speculate that UNIQUE is ok in this instance b/c the cid.sid combo
should be unique to each event (can't have the safe event id from the
same sensor twice!).

One caveat is to always be certain to use the whole compound index if
you write your own sql statements.  Referencing a single key of a
compound index gives you no benefit.  Additionally, if you query and
want to take advantage of a compound key, you MUST use the key elements
IN ORDER (i.e. if your index uses ag_sid.ag_cid and your query
references ag_cid first, the compound index will not be used.).  At
least this is the case in MySQL - I guess that postgresql could be
different, but I don't think that it is.

Hope that this helps,

Andrew

-----Original Message-----
From: Daniel Lang [mailto:dl () leo org] 
Sent: Thursday, June 27, 2002 6:04 AM
To: snort-users () lists sourceforge net
Subject: [Snort-users] How to create the DB indices with postgresql


Hi,

sorry if this is FAQ (it was not answerered in the Snort/ACID FAQ),
geocrawler doesn't seem to support searching the archives, so after some
fruitless search, I dare to ask directly.

"ACID FAQ B-9 PostgreSQL optimizations" suggest to add
indexes to the databases, mentioning fields, that should
have indexes created.

Now I'm not an SQL expert, and I'm not sure how to create these indexes.

The CREATE INDEX command needs a name for the created index, and I don't
know, if the name needs to be a specific one.

For the first field (event.timestamp) I tried:

 CREATE INDEX event_timestamp ON event (timestamp); 

and such alike for the other fields, but it seemed not
to result in any benefit. Also I got error messages sometimes regarding
a 'unique index' (I did not specify UNIQUE anywhere).

Further some field descriptions from the FAQ are mysterious to me:

(DB schema < v103) iphdr.ip_src0 + iphdr.ip_src1 + iphdr.ip_src2 +
iphdr.ip_src3

This seems only to be required if the schema version is below 103? As
far as I can tell, I'm using 105, so I omitted them just.

Also I'm not sure, what is meant by:

acid_ag_alert.ag_sid + acid_ag_alert.ag_cid

The addition of an index or concatenation? How would one specify that
suggestion.

Please clarify if the names of the indexes are important, and which
names to use, and how to create these '+' connected indexes. 

Thank's a lot.

Best regards,
 Daniel
-- 
IRCnet: Mr-Spock              - Truth lies in the eye of the beholder - 
*Daniel Lang * dl () leo org * +49 89 289 25735 * http://www.leo.org/~dl/*


-------------------------------------------------------
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


-------------------------------------------------------
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