precomputing user-defined functions in postgresql

Question

I'm trying to precompute a user-defined function on a per row basis. The idea is I have JSON object as a text object in one of the fields, and I want to parse out some other 'fields' from it, which can be returned in queries just like any other true field. However, the overhead of parsing the JSON is significant. Is there any way to precompute this parsing function in a way that speeds up queries?

Please refrain from arguing that there shouldn't be JSON as text on the database in the first place; I am aware of the pros and cons.

Almost feels like you are wanting to setup an ETL process (extract transform load) on your data. Are you opposed to an ETL tool (or a series of scripts) that are executed nightly to read the JSON field and convert it into something a little more database friendly? — Twelfth, Jun 25 '12 at 22:54

score 2 · Answer 1 · edited May 23 '17 at 12:20

2

First off, you may be interested in the upcoming JSON data type of PostgreSQL 9.2 (to be released soon, now).

As to your question, you are looking for a materialized view (or the simpler form: a redundant precomputed column in your table). "Materialized View" is just the established term, not a special object in a PostgreSQL database. Basically you create a redundant table with precomputed values, that you refresh at certain events or on a timely basis.

A search for the term will give you some answers.

edited May 23 '17 at 12:20

Community

1
1

answered Jun 25 '12 at 16:29

Erwin Brandstetter

605,456
145
1,078
1,228

But isn't this giving me precomputed queries, rather than precomputed columns? – ferson2020 Jun 25 '12 at 20:26
@StevenMcPherson: Sorry, I don't follow. What is "this"? – Erwin Brandstetter Jun 25 '12 at 21:25
Using a materialized view. My understanding of it after reading about it a bit is that you precompute queries and store them in a table. I need to be able to store per-row values in a redundant table. – ferson2020 Jun 25 '12 at 21:35
The whole point of a materialized view is to store the *result* of a query. Of course, you store the query, too - you want to reuse it. – Erwin Brandstetter Jun 25 '12 at 21:42

score 1 · Answer 2 · answered Mar 18 '13 at 02:03

In addition to a materialized view, keep in mind that PostgreSQL can also index functions' output so you can do something like:

CREATE INDEX my_foo_bar_udf_idx ON foo (bar(baz));

This works only if the UDF is marked as immutable meaning output only depends on arguments. This gives you an option to run your function against the query arguments and then scan the index instead of the table. It doesn't meet all use cases, but it does meet many of them and it can often save you the headaches of materializing views.

precomputing user-defined functions in postgresql

2 Answers2