6,424 reputation
31435
bio website
location Berlin, Germany
age 38
visits member for 2 years, 5 months
seen 4 hours ago

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!
Jan
16
comment Execution plan in simple query in PgSQL depends on offset clause, why?
As I see, changing work_mem clearly had its effect, nearly halving the execution time. However, it did not change the estimated costs and nor the plan, since this setting has no effect on the planner. If you had spinning disks, the difference would be much greater, I think. As per setting random_page_cost, it may depend on the type of your particular disk, see Greg Smith's answers here. Anyway, you can safely lower the value to 2, and with certain disks even to 1.
Jan
15
comment ERROR: could not find array type for datatype information_schema.sql_identifier
You don't need to make arrays for this. Just do ... WHERE column_name NOT IN (SELECT column_name ... )
Jan
15
comment ERROR: could not find array type for datatype information_schema.sql_identifier
And your question? What is your plan with those column names?
Jan
15
comment Finding similar values in columns
What RDBMS do you use? And I wouldn't call a name column id.
Jan
15
comment Improve sort performance in PostgreSQL?
When a sort does not fit into work_mem, it spills out to the disk. If happens, this is shown in the query plan. On the other hand, the sorting here took 876 kB of memory. It is very unlikely that this is bigger than the work_mem of any relatively recent PG instances.
Jan
15
comment tuning feed item's table query with multiple condition
Why is the mismatch between the plan and the text (133534 vs 400000 rows)?
Jan
15
comment tuning feed item's table query with multiple condition
Hpw do you measure those 4 seconds? Pushing so much data through a network connection can be slow. A query plan would help anyway.
Jan
14
comment MAX function and group by usage - postgresql
If you don't use GROUP BY, you will get the aggregates for all the rows selected (as specified by the WHERE clause). In this case you can't specify other columns in the SELECT list. If you say GROUP BY id, aggregates will be computed for any values of id, in your case for single rows - that's useless.
Jan
14
comment Shrink database operation in maintenance plan failed
Please consult the 'Best practices' part of the docs
Jan
11
comment What is the difference between a “record” and a “row” in SQL Server?
A related post on SO shows clearly that this is an important question.