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

Re: Place of subselect

From: "A(dot) Kretschmer" <andreas(dot)kretschmer(at)schollglas(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Re: Place of subselect
Date: 2008-11-25 09:02:35
Message-ID: 20081125090235.GC10443@a-kretschmer.de (view raw or flat)
Thread:
Lists: pgsql-general
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

In response to

pgsql-general by date

Next:From: Sam MasonDate: 2008-11-25 09:38:11
Subject: Re: Serial/sequence problem
Previous:From: Guillaume BogDate: 2008-11-25 08:44:34
Subject: Re: Place of subselect

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