421 reputation
26
bio website eppesuigoccas.homedns.org/…
location Italy
age
visits member for 1 year, 4 months
seen 2 days ago

SysAdmin on large Unix machines (HP-UX, AIX, Linux, and lately solaris). I mainly work on Performance analisys and Tuning for problems related to Infor ERP LN and database (manily oracle). I also design applications and program them, currently in Java. I am absolutely unhappy with Java and I will start my next work probably in Python or Django.


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.
Jan
30
asked How to change table/indexes structure for a query with two aggregated colums
Dec
10
comment Internal query on CDEF$ and CON$ very CPU bound
Statistics are gathered by oracle default job. I just removed these statistics from the tables in this query and now it is really faster.
Dec
10
comment Internal query on CDEF$ and CON$ very CPU bound
@JustinCave Yes, there are may reasons, even if those only apply to this specific system.
Dec
10
comment Internal query on CDEF$ and CON$ very CPU bound
@JustinCave This is 10.2.0.1.0 on linux. I just found a note in oracle support about the same problem when the database is an upgrade from 9i, but this is not my case. The solution suggested there is to remove statistics on user$, con$ and cdef$.
Dec
10
asked Internal query on CDEF$ and CON$ very CPU bound
Nov
30
awarded  Yearling
Jun
20
comment trouble creating a database with postgreql
You should check the postgresql error log since the source of your error is not clear: it may be that you created/changed the pg_hba.conf file with a different user and now the server cannot eve access it; or it may be in a wrong format (like: did you insert a new line after the text you typed into it?)