Reputation
8,292
Top tag
Next privilege 10,000 Rep.
Access moderator tools
Badges
3 18 43
Newest
 Informed
Impact
~405k people reached

Nov
6
comment Improve SQL query
@TomasGreif and how about work_mem?
Nov
6
comment How do I install postgresql in Ubuntu?
After sudo updatedb, does locate initdb return something?
Nov
5
comment How do I install postgresql in Ubuntu?
You may want to have a look at wiki.postgresql.org/wiki/…
Nov
5
comment Automatic conversion of anonymous records returned from an UDF to well known table-type
Technically, I think, what you suggest is not possible.
Nov
5
comment Automatic conversion of anonymous records returned from an UDF to well known table-type
Or you could just as well wrap these into functions in an other schema but an identical signature. Or if you know the source, create your own, usable set of them.
Nov
5
comment PgPool is pushing Postgres to 100% cpu usage
Do you really need this much connections? If 70% is idle, it means that 90 (or 100 with some buffer) would be enough. On the other hand, less than 7 MB of work_mem can cause all sorts of problems with bigger queries. As you are quite low on memory, this will be a problem even if you reduce the connection numbers... For 500 to 700 connections we usually have more than 100 GB of memory, just for comparison.
Nov
4
comment How to do a minor upgrade postgresql 9.3.0 windows to 9.3.1?
As per the documentation: stop the server , install the new binaries, and restart. It is always a good idea to take a backup, and generally, to have a plan B.
Nov
4
comment PostgreSQL 9.2 - Partitioning: Constraint Exclusion on SELECT not kicking in
This question appears to be off-topic because it is already answered on StackOverflow
Nov
1
comment How can I run a PostgreSQL outside the standard directories on Linux, ie outside /var/run/postgresql etc?
How about setting up some virtual machines? For example, we have a VirtualBox image available for every dev, with PostgreSQL installed from packages (which are basically the standard ones). This way I have about 7 instances, only one locally and the others virtualized. When I kill one of these (as it happened already due to pg_xlog diskspace problems), I just reimport the image and that it is.
Nov
1
comment How can I run a PostgreSQL outside the standard directories on Linux, ie outside /var/run/postgresql etc?
Why would you do this?
Oct
30
comment Improve SQL query
It looks like raising the work_mem by some MBs (say 30) would speed things up significantly. You could also remove the ORDER BY clause from the aggregation CTE.
Oct
30
comment Partitioning MySQL vs PostgreSQL
Do you plan to partition both tables?
Oct
30
comment Improve SQL query
As the query is not exactly trivial, could you add some explanation about what you try to achieve?
Oct
30
comment Query optimization on a large table in PostgreSQL
The plan you posted here and the one the link points to are different. Which one is the real one? Also, I think there is no need obfuscating table and column names if you post them here openly...
Oct
29
comment Add a column contains a photo
Very interesting question - if only I knew what you are talking about :)
Oct
29
comment Add a column contains a photo
Or (in my view this is usually the best) store the images in the filesystem and put only some link, path or anything pointing to them into the DB.
Oct
29
comment set up proper role/group acces on databases and schemes
Furthermore, it is very much unclear how your DB and schema setup looks like. Could you please list the schemas in a given DB?
Oct
29
comment How to change temporary directory for pg_dump?
Can you mount the temp dir (is it /tmp?) on an other disk?
Oct
29
comment Return ResultSet with column names from postgresql Stored procedures
Call your function as SELECT * FROM message(). I bet it would solve your problem.
Oct
29
comment How to add FK on a column of composite type in PostgreSQL?
@Eonil This abstraction is not a bad idea, I use it myself sometimes. However, I've never seen a case where a FK would have had to be defined on these. Probably a good example from you could help us understand your problem.