3 ms·
We use them a lot where the join table could be over a million rows. Saves a ton of performance and a join. Were talking seconds here on a 3-4s query before, ~1
by timonv 11y ago
We use them a lot where the join table could be over a million rows. Saves a ton of performance and a join. Were talking seconds here on a 3-4s query before, ~1s after.
- pjungwir 11y agoYes, it can make a huge difference. I did this once when I had arrays of ~1 million floats and had to compute statistics on them. I wound up implementing a bunch of stats functions as C stored procedures that operate on arrays, and brought query times down from ~12 seconds to ~20 milliseconds: https://github.com/pjungwir/aggs_for_arrays/ https://github.com/pjungwir/aggs_for_arrays/ Another time arrays are handy is when you don't know how many "columns" you need to return. SQL can't do this, but a variable-length array can. Here is a writeup for one time that came in handy: http://illuminatedcomputing.com/posts/2013/03/fun-postgres-puzzle/ http://illuminatedcomputing.com/posts/2013/03/fun-postgres-p... Another time they are helpful is to throw an `array_agg` into an aggregate query to see what values are getting rolled up. This can be really useful if you're trying to debug weird behavior. Also `(array_agg(...))[1]` is a poor-man's `first` function. :-)
- Erwin 11y agoThanks -- I had a a query on 9.1 using MEDIAN (implemented in pl/psql) going from 12 seconds to 0.2 seconds by switching to array_to_median(array_agg(the_column)). I found it was faster to actually query a million values and calculate the median in Python than to use that pl/psql MEDIAN so it's nice this can be done easier with array_agg + your thing.