Snort mailing list archives
Re: How to create the DB indices with postgresql
From: "Ben" <bench () tukati com>
Date: Thu, 27 Jun 2002 09:57:16 -0700
FYI, with Postgres 7.2 you can simply run ANALYZE <tablename>, which is much faster than a vacuum and doesn't lock the table. ----- Original Message ----- From: "Hutchinson, Andrew" <Andrew.Hutchinson () Vanderbilt edu> To: "Daniel Lang" <dl () leo org> Cc: <snort-users () lists sourceforge net> Sent: Thursday, June 27, 2002 7:23 AM Subject: RE: [Snort-users] How to create the DB indices with postgresql 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 ------------------------------------------------------- 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)