0

I'm creating a web app that displays a pie chart. In order to get all the data for the chart in a single HTTP request, I'm combining multiple SELECT statements with UNION ALL. However, each SELECT statement must perform the exact same JOIN operations. Is there a way to perform the JOINs once up front, then reuse that result in each new SELECT (e.g. something like LATERAL, but across SELECTs)?

Rob Johansen
  • 5,076
  • 10
  • 40
  • 72
  • Do the select first and then join: `select ... from (select ... union all select ... union all select ...) dt join tab on ...` – dnoeth Sep 12 '15 at 21:48
  • 2
    I would suggest that you ask another question with the query you are using. There might be simpler way to write it. – Gordon Linoff Sep 12 '15 at 21:55
  • @GordonLinoff: Good suggestion. I've created a new question here: http://stackoverflow.com/questions/32544322/postgresql-better-way-than-multiple-select-statements – Rob Johansen Sep 12 '15 at 22:20

1 Answers1

0

Sounds to me like you're looking at this from a somewhat narrow point of view. Why do you need the data to come out of Postgres already UNIONed and sliced and diced? You could do some administrative work on the server side once it gets back from Postgres, or on the JavaScript side.

So maybe run a simpler query in PG, then do some rearranging of the data in a programming language, PHP or JS.

I'm not saying it's impossible to do what you're talking about with a big fancy UNION query, but it doesn't sound terribly efficient. Alternatively, if you want it to come out of PG all sliced and diced, you could do it in a store procedure.

I mistrust big fancy queries: they often end up being unable to respond to circumstances. What if you need to adjust the data in a way that just CAN'T be done with one query?

I guess if you need a more specific answer that does exactly what you're saying, you'll probably need to give more details.

AngularNewbie
  • 63
  • 10
  • I don't disagree that there could be a better way to do what I want (that's why I asked the question), but there are multiple ways to define efficiency. In my case, I simply need the database server to return counts of my data points so I can populate the pie chart. There are 12 data points, with counts ranging from 30,000 to 150,000 each. It wouldn't be efficient to use a simpler query that returns all 1.2 million rows over HTTP and then have to count it on the web server, or transfer it all again from the web server to the client and count it in JavaScript. – Rob Johansen Sep 12 '15 at 22:04
  • Well, I guess I'd again need to see a bit more detail. Can you have the database count things up in some raw form, resulting in a short list of output rows, that you can then massage to get the exact ones you need? – AngularNewbie Sep 12 '15 at 22:14
  • I've created a new question here: http://stackoverflow.com/questions/32544322/postgresql-better-way-than-multiple-select-statements – Rob Johansen Sep 12 '15 at 22:21