Re: Place of subselect

am Tue, dem 25.11.2008, um 15:34:57 +0800 mailte Guillaume Bog folgendes:
> 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.

Please check your presumption with explain analyse <your query>.

For example:

test=*# explain analyse select t1.*, (select count(1) from t2) from t1 order by 1 limit 5;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------
Limit (cost=186.54..186.55 rows=5 width=4) (actual time=0.087..0.104 rows=3 loops=1)
InitPlan
-> Aggregate (cost=36.75..36.76 rows=1 width=0) (actual time=0.022..0.024 rows=1 loops=1)
-> Seq Scan on t2 (cost=0.00..31.40 rows=2140 width=0) (actual time=0.004..0.008 rows=1 loops=1)
-> Sort (cost=149.78..155.13 rows=2140 width=4) (actual time=0.082..0.088 rows=3 loops=1)
Sort Key: i
-> Seq Scan on t1 (cost=0.00..31.40 rows=2140 width=4) (actual time=0.046..0.056 rows=3 loops=1)
Total runtime: 0.197 ms
(8 rows)

Both tables executes only one scan.

Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net