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
– Twelfth, Commented Jun 25, 2012 at 22:54

Community · Accepted Answer · 2017-05-23 12:20:05Z

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, 2017 at 12:20

CommunityBot

11 silver badge

answered Jun 25, 2012 at 16:29

Erwin Brandstetter

668k159 gold badges1.2k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

ferson2020 Over a year ago

But isn't this giving me precomputed queries, rather than precomputed columns?

Erwin Brandstetter Over a year ago

@StevenMcPherson: Sorry, I don't follow. What is "this"?

ferson2020 Over a year ago

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.

Erwin Brandstetter Over a year ago

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.

Chris Travers · Accepted Answer · 2013-03-18 02:03:36Z

1

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.

answered Mar 18, 2013 at 2:03

Chris Travers

26.6k6 gold badges70 silver badges191 bronze badges

Collectives™ on Stack Overflow

precomputing user-defined functions in postgresql

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related