am Tue, dem 25.11.2008, um 16:44:34 +0800 mailte Guillaume Bog folgendes:
> It seems that you are right. By further testing I found that a WHERE condition
> in the subquery was making the query hundred times slower. As I'm not very
> familiar with explain analyze, I paste them below. Why do I have "merge join"
> and "merge cond" in one case and "subplan" in the other case? Note that
> "u_xref_ug_id" is a reference and therefore b-tree indexed.
>
>
> vf_cn2fr=# EXPLAIN ANALYZE SELECT ug_id AS id, ug_en AS name, ug_type AS type,
> (SELECT count(*) FROM forms_groups JOIN users ON fg_xref_u_id = u_id WHERE
> u_xref_ug_id = ug_id) as groupes
> FROM users_groups ORDER BY "ug_type","ug_en" LIMIT 5;
The were-condition contains both inner and outer tables, because of that
the subselect performs for every record of the outer table.
Maybe someone else can tell you an advice how to rewrite the query for
better performance.
Regards, Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net