Place of subselect

From: "Guillaume Bog" <guibog(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Place of subselect
Date: 2008-11-25 07:34:57
Message-ID: bc5951d00811242334i4c5b124fi77a76beacaad5266@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi dear Postgres users.

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. As I don't "ORDER BY c", there is no need to compute c for every
row. I know I can (or should ?) work with joins or with a subquery in the
from clause, but I'd like to make sure there is no other way before changing
my sqls.

A subjective reason for me to prefer subqueries in fields instead of joins
of sub tables is that, when it only relates to the text displayed, it is
easyer to read and to change, and I mess less with agregate functions.

Thanks.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message A. Kretschmer 2008-11-25 07:56:25 Re: Place of subselect
Previous Message A. Kretschmer 2008-11-25 07:26:55 Re: Serial/sequence problem