current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 1 down vote favorite

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.

share|improve this question
    
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
add comment

2 Answers

up vote 1 down vote

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.

share|improve this answer
    
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
add comment
up vote 0 down vote

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.