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

Re: nested select query failing

From: SZUCS Gábor <surrano(at)mailbox(dot)hu>
To: <pgsql-performance(at)postgresql(dot)org>
Subject: Re: nested select query failing
Date: 2003-05-15 11:56:10
Message-ID: 00a401c31ad8$fa234760$0a03a8c0@fejleszt2 (view raw or flat)
Thread:
Lists: pgsql-performance
It's a rather nasty query format, but wrapped it to readable form.
Looks like you could make a good join from all these IN's.

Another question: does EXPLAIN (without ANALYZE) work for this query?
Could you send its output, and table defs? maybe a minimal dump in private
email?


QUESTION TO PRO'S:

Basically, is it true that IN's can be converted to RIGHT JOIN's quite
simply? Is it always worth?

G.
--
while (!asleep()) sheep++;

---------------------------- cut here ------------------------------
----- Original Message -----
From: "amol" <amol(at)mithi(dot)com>
Sent: Thursday, May 15, 2003 5:57 AM


> 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 attached_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 response to

Responses

pgsql-performance by date

Next:From: Andrew SullivanDate: 2003-05-15 12:38:22
Subject: Re: FW: [ADMIN] Out of disk space- error code
Previous:From: Anagha JoshiDate: 2003-05-15 10:39:44
Subject: FW: [ADMIN] Out of disk space- error code

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