Skip site navigation (1) Skip section navigation (2)

Re: Place of subselect

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "Guillaume Bog" <guibog(at)gmail(dot)com>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: Place of subselect
Date: 2008-11-25 13:19:40
Message-ID: 14704.1227619180@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-general
"Guillaume Bog" <guibog(at)gmail(dot)com> writes:
> I have performance issues if I do the following pseudo-query:

> SELECT a, b, (SELECT count(*) FROM t2 WHERE something) AS c
> FROM t1 ORDER BY a LIMIT 10;

> After some tests, it seems to me that the subquery on t2 is computed for all
> rows of t1.

Yeah. The SQL specification says that ORDER BY happens after computing
the SELECT output-list.  In some cases that'll get optimized but you
can't count on it.

You can probably improve matters by using a sub-select:

SELECT a, b, (SELECT count(*) FROM t2 WHERE something) AS c
FROM ( SELECT a, b, ... FROM t1 ORDER BY a LIMIT 10 ) ss;

			regards, tom lane

In response to

pgsql-general by date

Next:From: Siddharth ShahDate: 2008-11-25 13:44:18
Subject: Effect of stopped status collector process
Previous:From: Dr.ONEDate: 2008-11-25 13:19:27
Subject: PostgreSQL 8.3.5 client_encoding WIN1251 trouble

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group