jug

228 reputation

bio	website
	location
	age
visits	member for	2 years, 4 months
	seen	21 hours ago
stats	profile views	25

	228 reputation	bio	website		visits	member for	2 years, 4 months
29 badges		location			seen	21 hours ago

summary answers questions tags badges favorites bounties reputation activity

31 Comments

suggestions reviews revisions comments badges posts accepts all

Jan 23	comment	Best book on query optimization, rewriting, refactoring Books about optimizations I liked are "The Art of SQL" by Stephane Faroult and Peter Robson (not specific for PostgreSQL, but it's exactly about what you want) and "PostgreSQL 9.0 High Performance" by Gregory Smith.
Aug 10	comment	Can spatial index help a “range - order by - limit” query About "Covering indexes" in PostgreSQL see also Erwin Brandstetter's comment to the question.
Aug 7	comment	Can spatial index help a “range - order by - limit” query If you had many updates, the nested set model would be a bad choice with respect to performance (if you have access to the book "The art of SQL", take a look at the chapter about hierachic models). But anyway, your main problem is similar to finding the maximum/the highest values (of an independent variable) on an interval, for which it is hard to design an indexing method. To my knowledge, the closest match to the index you need is the knngist module, but you would have to modify it to fit your needs. A spatial index is unlikely to be helpful.
Aug 7	comment	Can spatial index help a “range - order by - limit” query OK, I asked to see if using an alternative to the nested set model might be profitable (according to your response probably not).
Aug 7	comment	Can spatial index help a “range - order by - limit” query Two questions: (1) What kind of usage pattern do you expect for the table? Are there mostly reads or are there frequent updates (especially of the nested set variables)? (2) Is there any connection between the nested set integer variables lset and rset and the text variable word?
Feb 10	comment	Postgres Index scan forward vs backward = speed difference of 357X slower Do you have NULLs in the shareschange column? If yes (and lots of them), then use a partial index on the non-NULL elements only.
Feb 10	comment	Ways to speed up IN queries under PostgreSQL Where do these long lists of id's come from? Are they stored/calculated outside of the database?
Jan 17	comment	PostgreSQL vs. MySQL - Advantages / Disadvantages with a spatial component In case you have not seen it, there is a similar question on slashdot that will probably get more attention.
Jan 7	comment	Configuring PostgreSQL for write performance Another excellent resource for these kind of questions is Gregory Smith's book PostgreSQL 9.0 High Performance.
Dec 20	comment	PostgreSQL and query planner Also +1 for PostgreSQL High Performance. Great book, thanks!
Oct 20	comment	Pioneers in Database Research I wonder why the Codd's entry on wikipedia does not contain "The Key, The Whole Key, and Nothing but the Key, so help me Codd".
Sep 15	comment	Writing a simple bank schema: How should I keep my balances in sync with their transaction history? If you choose option 2 (which I think is cleaner), take a look at pgcon.org/2008/schedule/attachments/… how to implement "materialized views" efficiently. For option 1, chapter 11 of Haan's and Koppelaars' Applied Mathematics for Database Professionals (don't worry about the title) would be helpful to get an idea how to implement "transition constraints" efficiently. The first link is for PostgreSQL and the second for Oracle, but the techniques should work for any reasonable database system.
May 15	comment	Indexing to improve performance of range queries @JackPDouglas: Your first sentence is not correct. Postgresql (at least Version 8.1 and up) is able to combine two different indexes using a bitmap index scan (see en.wikipedia.org/wiki/Bitmap_index#In-memory_bitmaps)
Mar 16	comment	Non-unique multicolumn foreign key Or just add the line unique (id, topic_id), in your "create table"-command for comments.
Mar 16	comment	Non-unique multicolumn foreign key Try create unique index how_you_want_to_name_this_index on comments (id, topic_id); before issuing the alter table command adding the foreign key.
Feb 19	comment	How to design databases? If you don't know much about it, I can strongly recommend to read the book "Beginning Database Design" by Clare Churcher.
Feb 16	comment	BLOBs or references in PostgreSQL Yes, I deleted it, as the performance problems with bytea I wrote about can be avoided. The comments can be summarized by "Everything is fine with bytea, you should just make sure that you don't escape non-printable characters in the database and then un-escape them again in your application. As araqnid commented, you should instead use hex escaping that is supported by libpq."
Feb 15	comment	Where can I find good literature for beginners? @Nebbs: Sorry about your lecturer. You should definitely edit your question to include some topics mentioned in the syllabus of the course to get some useful answers here.
Feb 15	comment	Where can I find good literature for beginners? Sorry, I can't find the button to convert a question to community wiki either. I think it used to be below the text box for the question, but it isn't any more.
Feb 14	comment	Where can I find good literature for beginners? As your question doesn't have a definite answer, you should convert it to community wiki.