Postgresql is this 1 query or multiple?

Question

I am new to postgresql (and SQL in general) and my biggest concern right now is optimization. Below I have a simple query. However I believe that it is just one query. However, it could also be the case that it is just 3 queries (the ones that exist new the union all). Or it might actually be 6 queries since there are actually 6 select statements.

with foo as (
    select * from tableA
), bar as (
    select * from tableB
), zeta as (
    select * from tableC
)
select * from foo
union all
select * from bar
union all
select * from zeta

How many queries is the above query?

also...

The reason this question is important is because, I do not know whether I should write my query like the above or to simply just make all the queries separate.

select * from tableA; --query 1
select * from tableB; --query 2
select * from tableC; --query 3

This might be helpful: http://stackoverflow.com/questions/1067016/join-queries-vs-multiple-queries — Byron Claiborne, May 28 '14 at 21:35
in psql, "explain " will show you how many sequence scans, index scans, etc. Try it against your composite query, and against the three individual queries, and compare them. — Wayne Conrad, May 28 '14 at 22:02

score 1 · Accepted Answer · answered May 28 '14 at 22:06

Formally it is one complex query (as 'complex sentence'), which contains six 'simple' queries. You do not need these with statements though, as in fact they do nothing. Your first query should look like this:

select * from tableA
union all
select * from tableB
union all
select * from tableC;

In this case you'll obtain one set of rows as a result. If you execute

select * from tableA;
select * from tableB;
select * from tableC;

you will have three separate sets of rows.

Postgresql is this 1 query or multiple?

1 Answers1