pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: SZUCS Gábor <surrano@mailbox.hu>
To: pgsql-performance@postgresql.org
Subject: Re: nested select query failing
Date: Thu, 15 May 2003 13:56:10 +0200
Message-ID: <00a401c31ad8$fa234760$0a03a8c0@fejleszt2> (raw)
References: <003c01c31a96$1d1703d0$2e00a8c0@amol>

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@mithi.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;




view thread (8+ messages)  latest in thread

Message-ID: <00a401c31ad8$fa234760$0a03a8c0@fejleszt2>
Permalink:  ../00a401c31ad8$fa234760$0a03a8c0@fejleszt2/
Also on:    postgresql.org/message-id/00a401c31ad8$fa234760$0a03a8c0@fejleszt2

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-performance@postgresql.org
  Cc: surrano@mailbox.hu
  Subject: Re: nested select query failing
  In-Reply-To: <00a401c31ad8$fa234760$0a03a8c0@fejleszt2>
  References: <003c01c31a96$1d1703d0$2e00a8c0@amol> <00a401c31ad8$fa234760$0a03a8c0@fejleszt2>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox