Snort mailing list archives
RE: How to create the DB indices with postgresql
From: "Hutchinson, Andrew" <Andrew.Hutchinson () Vanderbilt edu>
Date: Thu, 27 Jun 2002 09:23:30 -0500
Daniel: AFAIK, you get a performance boost on SELECTs from a UNIQUE index as opposed to a normal index (due to the fact that once a single match is found, the query can return the result and cease the index scan). I'm not sure how significant this boost is, but according to my Postgresql references, it is worthwhile to opt for UNIQUE when possible. Additionally, the UNIQUE keyword will enforce data integrity - it somebody tries to insert a duplicate, you'll know about it, which may be desirable. One more pointer with Postgres - you mentioned that you had added some indices, but saw no benefit. It is possible to create an "good" index (i.e. an index that _should_ improve performance), but see no performance benefit. If this happens, it is likely because the system tables used by the query optimizer to develop its execution plan have not been updated. If this happens, you can use the VACUUM statement like this: VACUUM VERBOSE ANALYZE [tablename]; Then re-run your query, and it should be optimized properly. The VERBOSE statement is optional, but I like to see what's going on... Again, hope this helps. Andrew -----Original Message----- From: Daniel Lang [mailto:dl () leo org] Sent: Thursday, June 27, 2002 8:28 AM To: Hutchinson, Andrew Cc: snort-users () lists sourceforge net Subject: Re: [Snort-users] How to create the DB indices with postgresql Dear Andrew, Hutchinson, Andrew wrote on Thu, Jun 27, 2002 at 08:05:45AM -0500: [..]
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).
Thanks. Ok. I've looked in to the ACID source and found the names of at least two of the indexes (timestamp_index and signature_index), so I used them, and named the other indexes alike.
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.
As I said, I didn't specify UNIQUE anywhere, but I had a look into my pgsql error log. The messages are like this: ERROR: Cannot insert a duplicate key into unique index sig_reference_pkey [..] ERROR: Cannot insert a duplicate key into unique index acid_event_pkey [..] These indexes are created implicity on creation of the tables: [..] NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index 'sig_reference_pkey' for table 'sig_reference' NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index 'acid_event_pkey' for table 'acid_event' [..] So, these have probably nothing to do with the manually created indexes. Sorry for not checking this before asking. :-/
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); Ok, thanks, I try that. It seems to work, such that the statement is accepted. :))
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!).
Hmm ok, I did not use unique here either. Is there any benefit or drawback (not) using UNIQUE, even if it would be possible?
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
[..] I guess I have to trust ACID here. I did not craft any SQL statements by hand. But thanks for the hint.
Hope that this helps,
Yes, thanks. :) Best regards, Daniel -- IRCnet: Mr-Spock - ceterum censeo Microsoftinem esse delendam - *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
Current thread:
- How to create the DB indices with postgresql Daniel Lang (Jun 27)
- <Possible follow-ups>
- RE: How to create the DB indices with postgresql Hutchinson, Andrew (Jun 27)
- Re: How to create the DB indices with postgresql Daniel Lang (Jun 27)
- RE: How to create the DB indices with postgresql Hutchinson, Andrew (Jun 27)
- Re: How to create the DB indices with postgresql Ben (Jun 27)
- Re: How to create the DB indices with postgresql Daniel Lang (Jun 28)
- Re: How to create the DB indices with postgresql Ben (Jun 27)