Reputation
238
Top tag
Next privilege 250 Rep.
View close votes
Badges
4 11
Newest
 Constituent
Impact
~22k people reached

Aug
4
awarded  Constituent
Aug
4
awarded  Caucus
Oct
28
awarded  Notable Question
Aug
9
comment Complex constraint across all data in a table
Does a unique partial functional index work? (partial: only for status 'P' or 'G'; functional (or "index on expression"): map both characters 'P' and 'G' on the same value, e.g., true)
May
19
awarded  Caucus
Feb
14
awarded  Yearling
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.
Sep
14
awarded  Popular Question
Aug
17
awarded  Citizen Patrol
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?
Jun
19
awarded  Enthusiast
Jun
8
awarded  Caucus
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!