6,619 reputation
31436
bio website
location Berlin, Germany
age 38
visits member for 2 years, 6 months
seen yesterday

Jan
29
comment postgresql out-of-memory
Check the maintenance_work_mem description in the documentation. This size (relative to the remaining portion) seems overkill. How do your typical queries look like? How much memory do they consume?
Jan
29
comment postgresql out-of-memory
Operating system? Size of physical memory? Swap? Other software running on the same box? Without knowing these we could blame the aliens as well :)
Jan
28
comment PostgreSQL 8.3: Slow GROUP BY on large table
In PostgreSQL 9.2 it could use an index (and presumably be faster). Your version is quite old, maybe this is the time for upgrading?
Jan
27
comment Query optimization or missing indexes?
Have you ANALYZEd your tables recently? The estimated and real row counts differ significantly. And an other observation: the topmost row indicates that there are no rows affected (actual rows=1). How does the plan look like if it really does something? What would be the desirable execution time?
Jan
25
comment Using a Select in a Where clause for another Select
Possibly yes, together with the JOIN part.
Jan
25
comment Using a Select in a Where clause for another Select
postgresql.org/docs/9.1/static/…
Jan
25
comment Database design of Ledger Account and Balance sheet generation
AFAIK ledgers are quite special in a few ways (storing decimal values comes to mind first). Are you sure you want to develop an _n+1_st ledger having all the other _n_s around? Since this is a rather sensitive field (an error could cost someone much money), with no DB design experience I would not dive into this. Maybe you'd better hire someone with sufficient experience?
Jan
23
comment How to partition the table, week-wise?
What is your table definition? Primary key, other indexes, typical queries, expected workload (read/write)?
Jan
23
comment Interactive INSERT / UPDATE function to implement UPSERT
Have you read the article Erwin linked in your previous question? It explains all these things.
Jan
23
comment Factoring out subselect (with clause) ends in ORA-00904
I think those subselects are unnecessary complications only (could be solved with simply joining those txt tables as you select * from them), but at the moment I don't have the time for proving it.
Jan
23
comment Factoring out subselect (with clause) ends in ORA-00904
The problem is in SELECT txt0.* FROM txt txt0 WHERE txt0.spr_id = spr0.id where there is no spr0 in the FROM clause. If you check Leigh's answer carefully, there is a line CROSS JOIN spr0 whixh is missing from your version.
Jan
22
comment Casting issue when calling a function with composite type parameter
Please don't cross post the same question on stackoverflow and here.
Jan
18
comment PostgreSQL How do I convert struct text to plain *char in C functions?
Furthermore, please don't use short URLs since nobody knows in advance where it points. Please consult the help link just to the right of the comment box about how to place links in comments. Thanks!
Jan
18
comment Postgresql nested row_to_json not working
I'm thinking about making the subobjects first and then plug them into their final place. Not sure this can work yet.
Jan
17
comment Postgres 9.1 query plan not using check_constraints on partitions after large data load
It's quite picky on the conditions for constraint exclusion, or at least won't try to translate the conditions into those used for partitioning.
Jan
17
comment Postgres 9.1 query plan not using check_constraints on partitions after large data load
What do you get if you use/add "left"(zip::text, 1) = '1'::text in the WHERE clause?
Jan
17
comment Speed up data transfer from db to application
@Eric How many are there?
Jan
17
comment Postgres DB - Commit xlog files after server crash
Is the database mostly in idle (I mean no DML going on) state? That 14 files are not very many for a few months... Have you read this? postgresql.org/docs/8.0/static/backup-online.html
Jan
17
comment Speed up data transfer from db to application
Two (unrelated) things I don't understand: why don't you use meaningful table aliases so that you know at once what belongs where? And do you really need to query a - let me count - 14 table join without a single WHERE condition? That may be a huge amount of data, therefore network traffic...
Jan
16
comment Performance of different precedence pseudocolumn solutions with LIMIT
Exactly this is why I posted this: to collect criticism from the more experienced people around here. Let me correct you on only one thing: in the original question the best result was not necessary the one which matches the most conditions. Regarding the other points, I'll have to rerun the corrected queries. And again, thanks for pointing these out!