CODEWITHSUNDEEP
postgresqloptimization
Jayant
Jayant

Reputation: 323

SQL NESTED Query Optimization

I am running two sql queries say, 

select obname from table1 where obid = 12

select modname from table2 where modid = 12

Both are taking very less time, say 300 ms each.

But when I am running:

select obname, modname 
from (select obname from table1 where obid = 12) as alias1, 
(select modname from table2 where modid = 12) as alias2

It is taking 3500ms. Why is it so?

Upvotes: 0

Views: 44

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271111

In general, putting two scalar queries in the from clause is not going to affect performance. In fact, from an application perspective, one query may be faster because there is less overhead going back and forth to the database. A scalar query returns one column and one row.

However, if the queries are returning multiple rows, then your little comma is doing a massive Cartesian product (which is why I always use CROSS JOIN rather than a comma in a FROM clause). In that case, all bets are off, because the data has to be processed after the results start returning.

Upvotes: 1

Related Questions