jeudi 26 mars 2015

Is there a way to improve this query further?


I've got a query that over time I've been improving. Originally it simply selected * from the records table, and the data was formatted on the webapp side. But that was slow and I wanted to make things faster. I found that I could format the output into the correct json in postgres very quickly but the query was UGLY as sin. Then postgres 9.4 came out and brought json_object_agg which made the query nice and simple, and even faster than before, and all I had to do was change the data type of the data column to be json (it had been storing json-formatted data anyway).


The current version of the query is: select json_object_agg(day,data) as jsobj from records


The records table is:



\d records
Table "public.records"
Column | Type | Modifiers
--------+---------+------------------------------------------------------
id | integer | not null default nextval('records_id_seq'::regclass)
day | date |
data | json |
Indexes:
"records_pkey" PRIMARY KEY, btree (id)
"records_day_key" UNIQUE CONSTRAINT, btree (day)


What I'm wondering is this: is there a way to improve the query further? Either making it more readable or more performant?





Aucun commentaire:

Enregistrer un commentaire