7,044 reputation
31640
bio website
location Berlin, Germany
age 39
visits member for 2 years, 11 months
seen 2 days ago

Nov
6
comment Optimizing SELECT queries
Well, your table and the index on tag are very close in their size. Postgres simply decided that the sequential scan would be the cheaper. In the aggregate case, it's no wonder that aggregating 100M+ rows takes time. At the same time, it would be interesting to see the EXPLAIN (ANALYZE, BUFFERS) output to see the ratio between pages read from disk and pages cached.
Nov
5
comment sorting by partial key name in json column
I'd store the integer part separately. Then you may be able to have a slightly different JSON structure that can be searched to your liking.
Nov
5
comment Deadlock detected on CREATE TABLE in PostgreSQL
@khorvat you should probably flag your post on SO for migration to here, then the two posts will be merged and all collected information seen in one place.
Nov
5
comment Deadlock detected on CREATE TABLE in PostgreSQL
May be related (and unanswered, too): dba.stackexchange.com/questions/66445/…
Nov
5
comment modeling data dimensions as columns - some dimensions are highly variable while others have low variability
Please add here the main points of the document you linked. This could make your answer a good/excellent one.
Nov
3
answered Order specific columns in json_agg
Nov
3
comment Order specific columns in json_agg
'Does not work' means exactly what? Is it an error, unexpected output, unexpected pizza delivery or something other? My guess is you get ERROR: in an aggregate with DISTINCT, ORDER BY expressions must appear in argument list - meaning that the expression for ORDER BY must be (colA, colB) (omitted the ASC as noise).
Nov
3
comment PostgreSQL: schema diff/patch tool
There is no problem with resurrecting old questions (there are even badges to achieve from that). And a question: is it possible to compare whole databases (I mean, at least, all tables in them)?
Nov
1
comment Postgresql 9.3: Add Unique constraint using an existing unique index
I tend not to add a unique constraint at all. This way, it is somewhat easier to solve issues with bloated indexes on heavily used tables.
Oct
30
comment Slow index scans in large table (PostgreSQL 9.2)
If you refer by 'the cost estimates seem to be a factor 4x larger' to the fact that the cost numbers are about 4 times those of the actual time, then please notice that the two has nothing to do with each other. The cost is only an estimate, helping the query optimizer to choose the best looking plan. Outside of this context, it is usually a meaningless value.
Oct
30
comment Postgresql and UTF8 to Latin1 conversion?
Yeah, this used to be (or maybe still is) a problem for Hungarian texts. For a time, you could see many websites with the same misinterpreted characters (well, the browsers were also confused...) Once I could relatively easily read all these ä and similar vowels :)
Oct
30
revised Postgres: modify existing search_path (preserve current values)
adding the bonus answer
Oct
30
comment Different kinds of pricing PostgreSQL
Well, 'while stocks last' is definitely not a price, so you are right, it would be silly to store these texts as a price. It would be useful to describe the use case a bit more. Until that, my guess is that you have only one of these text per product at any given time - you can just store them in a separate table and add a new column to the pricing that references these. As the pricing usually keeps changing, it's usually a good idea to separate it from the basic product data.
Oct
29
revised Why does 'plpgsql' not appear in the pg_extension table?
added 72 characters in body
Oct
29
awarded  Revival
Oct
29
answered Why does 'plpgsql' not appear in the pg_extension table?
Oct
28
comment Insert New Foreign Key Row for new Strings
Yeah, that's true. What this shows is that a CREATE TABLE would be much more understandable...
Oct
28
comment Insert New Foreign Key Row for new Strings
Just add the referenced value in the same transaction? Also, your example is not very clear. We don't know what sort of type someval_str is, but it is certainly not an integer. Probably some actual CREATE TABLE statements would help.
Oct
28
answered Can i have one foreign key to both parent and it's childs in postgresql?
Oct
27
revised Why does a select statement dirty cache bufferes in postgres
added the explanation from the comment plus cleaned up HTH