Re: nested select query failing

* 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