Someone asked recently on Slack, whether one should build tsvector data in a field, and index it, or make index on expression.
We talked about it for a while, and I thought I'll reformat my thoughts into blogpost to avoid retyping it next time.
Tag: function
Waiting for 9.5 – Implement UPDATE tab SET (col1,col2,…) = (SELECT …), …
On 18th of June, Tom Lane committed patch:
Implement UPDATE tab SET (col1,col2,...) = (SELECT ...), ... This SQL-standard feature allows a sub-SELECT yielding multiple columns (but only one row) to be used to compute the new values of several columns to be updated. While the same results can be had with an independent sub-SELECT per column, such a workaround can require a great deal of duplicated computation. The standard actually says that the source for a multi-column assignment could be any row-valued expression. The implementation used here is tightly tied to our existing sub-SELECT support and can't handle other cases; the Bison grammar would have some issues with them too. However, I don't feel too bad about this since other cases can be converted into sub-SELECTs. For instance, "SET (a,b,c) = row_valued_function(x)" could be written "SET (a,b,c) = (SELECT * FROM row_valued_function(x))".
Continue reading Waiting for 9.5 – Implement UPDATE tab SET (col1,col2,…) = (SELECT …), …
Explaining the unexplainable – part 3
In previous post in the series I wrote about how to interpret single line in explain analyze output, it's structure, and later on described all basic data-getting operations (nodes in explain tree).
Today, we'll move towards more complicated operations.
Waiting for 9.1 – Removed autocast footgun
On 8th of November, Tom Lane committed patch, which doesn't provide any new features, but removes one of the more annoying footguns in PostgreSQL:
Prevent invoking I/O conversion casts via functional/attribute notation. PG 8.4 added a built-in feature for casting pretty much any data type to string types (text, varchar, etc). We allowed this to work in any of the historically-allowed syntaxes: CAST(x AS text), x::text, text(x), or x.text. However, multiple complaints have shown that it's too easy to invoke such casts unintentionally in the latter two styles, particularly field selection. To cure the problem with the narrowest possible change of behavior, disallow use of I/O conversion casts from composite types to string types via functional/attribute syntax. The new functionality is still available via cast syntax. In passing, document the equivalence of functional and attribute syntax in a more visible place.
Grouping data into time ranges
Today some guy on IRC asked question, which I didn't fully understand, but which could (probably) be summarized: how to group data into 5 minute intervals, based on some timestamp column.
Well, it seems trivial (as long as you know how to do it), but since he clearly didn't know how to do it (or I misunderstood his problem), here's the explanation:
Profiling stored procedures/functions
One database that I am monitoring uses a lot of stored procedures. Some of them are fast, some of them are not so fast. I thought – is there a sensible way to diagnose which part of stored procedure take the most time?
I mean – I could just put the logic into application, and then every query would have it's own timing in Pg logs, but this is not practical. And I also believe that using stored procedures/functions is way better than using plain SQL due to a number of reasons.
So, I'm back to question – how to check which part of function takes most of the time?