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

Re: nested select query failing

From: "Victor Yegorov" <viy(at)nordlb(dot)lv>
To: "amol" <amol(at)mithi(dot)com>
Cc: <pgsql-performance(at)postgresql(dot)org>
Subject: Re: nested select query failing
Date: 2003-05-15 03:51:46
Message-ID: 20030515035146.GH1549@nordlb.lv (view raw or flat)
Thread:
Lists: pgsql-performance
* amol <amol(at)mithi(dot)com> [15.05.2003 06:47]:
> Hi everybody,
> I am new to this mailing list, so please let me know if I am not posting
> queries the way you are expecting.
> 
> -  We are porting a web based application from MSSQL to postgres as a
> backend.
> This is a database intensive application. I am facing a problem in some
> queries like this :
> 
> select distinct attached_info.id, ownerid ,attached_info.modified_date from
> attached_info where attached_info.id in (  select distinct
> attached_tag_list.id from attached_tag_list where attached_tag_list.id in
>  select attached_info.id from attached_info where
> ttached_info.deleted='0'  )  and attached_tag_list.id  in ( select id from
> attached_tag_list where attached_tag = 262 )  and
> attached_tag_list.attached_tag in ( select tags.id from tags where tags.id
> in ( select tag_id from tag_classifier, tag_classifier_association where
> classifier_tag_id in ( 261, 4467, 1894, 1045, 1087, 1355, 72, 1786, 1179,
> 3090, 871, 3571, 3565, 3569, 3567, 1043, 2535, 1080, 3315, 87, 1041, 2343,
> 2345, 1869, 3088, 3872, 2651, 2923, 2302, 1681, 3636, 3964, 2778, 2694,
> 1371, 2532, 2527, 3742, 3740, 1761, 4530, 4671, 4503, 4512, 3700 ) and
> association_id='1566' and
> tag_classifier.uid=tag_classifier_association.uid )  and
> tags.isdeleted='0' )  )  order by attached_info.modified_date desc,
> attached_info.id desc;

IN () constructs isn't a good part of postgres (from the performance point
of view). Try to rewrite your query using joins or EXISTS/NOT EXISTS
constructs.

Search archives for more details, there were a discussion of this topic
lately.

-- 

Victor Yegorov

In response to

pgsql-performance by date

Next:From: amolDate: 2003-05-15 03:57:33
Subject: nested select query failing
Previous:From: Rudi StarcevicDate: 2003-05-15 03:46:05
Subject: Re: PERFORMANCE and SIZE

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