Reputation
841
Top tag
Next privilege 1,000 Rep.
See votes, expandable usercard
Badges
4 9
Newest
 Caucus
Impact
~102k people reached

  • 0 posts edited
  • 0 helpful flags
  • 7 votes cast
Jul
28
awarded  Caucus
May
27
comment How to change the data directory on Postgresql 9.1 on Ubuntu
Hi @GaneshKrishnan, I tried with only bit "x" on all parent directories and that worked. Did you find a different behaviour? Is bit "r" really compulsory for all parent directories?
Dec
5
awarded  Good Answer
Nov
30
awarded  Yearling
Sep
24
awarded  Autobiographer
May
24
awarded  Nice Answer
Apr
27
comment constraint in oracle
I think you have to write a trigger that check the number of members when inserting or updating on the member table.
Mar
11
awarded  Student
Jan
31
comment How to change table/indexes structure for a query with two aggregated colums
@ypercube I just checked, as you suggested, and they give the exact same timing. Plan show and "Index Scan" when using the DESC ordered index, or a "Index Scan Backward" when using an ASC ordered index.
Jan
31
comment How to change table/indexes structure for a query with two aggregated colums
@ypercube as shown in the answer, having created both indexes with ASC and DESC, Postgresql selected the DESC ones, so I assume it is the best/fastest index.
Jan
30
comment How to change table/indexes structure for a query with two aggregated colums
@Colin'tHart I still have to try with 9, but on 8.3 it was faster with DESC.
Jan
30
revised How to change table/indexes structure for a query with two aggregated colums
typo
Jan
30
comment How to change table/indexes structure for a query with two aggregated colums
I just added the reverse order for second field in index, so that MAX function just get the first record indexed.
Jan
30
revised How to change table/indexes structure for a query with two aggregated colums
Index ordered DESC
Jan
30
comment How to change table/indexes structure for a query with two aggregated colums
@Mihai, I believe the index is ordered on these colums, so the max(third columns) is not directly accessible in the index and it require a scan. The two different indexes solve the problem
Jan
30
answered How to change table/indexes structure for a query with two aggregated colums
Jan
30
comment How to change table/indexes structure for a query with two aggregated colums
@Mihai, you are right and I didn't check the plan details. The new index is used in the first part of plan!
Jan
30
revised How to change table/indexes structure for a query with two aggregated colums
Added "addendum 1" results
Jan
30
comment How to change table/indexes structure for a query with two aggregated colums
@dezso, your comment about the table structure is sadly not acceptable since this schema is used across many different DBMS. Let's say this is the schema that works everywhere (informix, oracle, sql-server, mysql, postgresql). About these limits (like 50 chars in length), they are required by the application.
Jan
30
comment How to change table/indexes structure for a query with two aggregated colums
@Mihai, your suggestion does not work since even with that new index, the plan does not change.