Thread

1. Re: nested select query failing

   Victor Yegorov <viy@nordlb.lv> — 2003-05-15T03:51:46Z

   * amol <amol@mithi.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
   
   

2. nested select query failing

   Amol Bhutada <amol@mithi.com> — 2003-05-15T03:57:33Z

   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;
   
   When I fire this query in psql, it does not return back.
   
   - top command shows postgres above 95+% cpu usage consistantly
     PID USER     PRI  NI  SIZE  RSS SHARE STAT %CPU %MEM   TIME COMMAND
    1550 postgres  25   0 20268  19M 18904 R    95.3  5.2   6:31 postmaster
   
   - I am using RedHat 8 with following postgres rpms
   postgresql-libs-7.2.2-1
   postgresql-7.2.2-1
   postgresql-server-7.2.2-1
   
   - RAM size is 384 MB, SWAP size is 384 MB, but top shows that memory is free
   
   - I have done following changes after searching for performance realted
   information on the internet and postgres site
               - in /etc/rc.d/rc.local added following lines
                               echo "32768" >/proc/sys/fs/file-max
                               echo "98304" >/proc/sys/fs/inode-max
               - in /etc/init.d/postgresql file a pg_ctl call is changed to :
                   su -l postgres -s /bin/sh -c "/usr/bin/pg_ctl  -D $PGDATA -o
   '-i -N 1024 -B 2048 -d 5' -p /usr/bin/postmaster start  >>
   /var/log/pgsql.log 2>&1" < /dev/null
   
   - pgsql log shows :
   ......
   }) :lefttree <> :righttree <> :extprm () :locprm () :initplan <> :nprm 0
   :scanrelid 1 } :righttree <> :extprm () :locprm () :initplan <> :nprm 0
   :keycount 3 } :righttree <> :extprm () :locprm () :initplan <> :nprm 0
   :numCols 3 :uniqColIdx 3 1 2 }
   DEBUG:  ProcessQuery
   *********
   log stops here for 5/6 minuts
   *********
   DEBUG:  proc_exit(0)
   DEBUG:  shmem_exit(0)
   DEBUG:  exit(0)
   DEBUG:  reaping dead processes
   DEBUG:  child process (pid 1595) exited with exit code 0
   DEBUG:  proc_exit(0)
   DEBUG:  shmem_exit(0)
   DEBUG:  exit(0)
   DEBUG:  reaping dead processes
   DEBUG:  child process (pid 1598) exited with exit code 0
   DEBUG:  proc_exit(0)
   DEBUG:  shmem_exit(0)
   DEBUG:  exit(0)
   DEBUG:  reaping dead processes
   DEBUG:  child process (pid 1599) exited with exit code 0
   DEBUG:  proc_exit(0)
   DEBUG:  shmem_exit(0)
   DEBUG:  exit(0)
   DEBUG:  reaping dead processes
   DEBUG:  child process (pid 1600) exited with exit code 0
   
   
   - What should I do to get such queries working?
   - Is there any limit on query size?
   - Is there anything left in tuning postgres which is causing this problem ?
   
   If you want me to try anything, please let me know.
   
   thanks
   Amol
   
   
   
   
   
   
   

3. Re: nested select query failing

   Christopher Kings-Lynne <chriskl@familyhealth.com.au> — 2003-05-15T04:24:08Z

   Please post the EXPLAIN ANALYZE of that query...
   
   Chris
   
   ----- Original Message ----- 
   From: "amol" <amol@mithi.com>
   To: <pgsql-performance@postgresql.org>
   Sent: Thursday, May 15, 2003 11:57 AM
   Subject: [PERFORM] nested select query failing
   
   
   > 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;
   >
   > When I fire this query in psql, it does not return back.
   >
   > - top command shows postgres above 95+% cpu usage consistantly
   >   PID USER     PRI  NI  SIZE  RSS SHARE STAT %CPU %MEM   TIME COMMAND
   >  1550 postgres  25   0 20268  19M 18904 R    95.3  5.2   6:31 postmaster
   >
   > - I am using RedHat 8 with following postgres rpms
   > postgresql-libs-7.2.2-1
   > postgresql-7.2.2-1
   > postgresql-server-7.2.2-1
   >
   > - RAM size is 384 MB, SWAP size is 384 MB, but top shows that memory is
   free
   >
   > - I have done following changes after searching for performance realted
   > information on the internet and postgres site
   >             - in /etc/rc.d/rc.local added following lines
   >                             echo "32768" >/proc/sys/fs/file-max
   >                             echo "98304" >/proc/sys/fs/inode-max
   >             - in /etc/init.d/postgresql file a pg_ctl call is changed to :
   >                 su -l postgres -s /bin/sh -c "/usr/bin/pg_ctl  -D
   $PGDATA -o
   > '-i -N 1024 -B 2048 -d 5' -p /usr/bin/postmaster start  >>
   > /var/log/pgsql.log 2>&1" < /dev/null
   >
   > - pgsql log shows :
   > ......
   > }) :lefttree <> :righttree <> :extprm () :locprm () :initplan <> :nprm 0
   > :scanrelid 1 } :righttree <> :extprm () :locprm () :initplan <> :nprm 0
   > :keycount 3 } :righttree <> :extprm () :locprm () :initplan <> :nprm 0
   > :numCols 3 :uniqColIdx 3 1 2 }
   > DEBUG:  ProcessQuery
   > *********
   > log stops here for 5/6 minuts
   > *********
   > DEBUG:  proc_exit(0)
   > DEBUG:  shmem_exit(0)
   > DEBUG:  exit(0)
   > DEBUG:  reaping dead processes
   > DEBUG:  child process (pid 1595) exited with exit code 0
   > DEBUG:  proc_exit(0)
   > DEBUG:  shmem_exit(0)
   > DEBUG:  exit(0)
   > DEBUG:  reaping dead processes
   > DEBUG:  child process (pid 1598) exited with exit code 0
   > DEBUG:  proc_exit(0)
   > DEBUG:  shmem_exit(0)
   > DEBUG:  exit(0)
   > DEBUG:  reaping dead processes
   > DEBUG:  child process (pid 1599) exited with exit code 0
   > DEBUG:  proc_exit(0)
   > DEBUG:  shmem_exit(0)
   > DEBUG:  exit(0)
   > DEBUG:  reaping dead processes
   > DEBUG:  child process (pid 1600) exited with exit code 0
   >
   >
   > - What should I do to get such queries working?
   > - Is there any limit on query size?
   > - Is there anything left in tuning postgres which is causing this problem
   ?
   >
   > If you want me to try anything, please let me know.
   >
   > thanks
   > Amol
   >
   >
   >
   >
   >
   >
   > ---------------------------(end of broadcast)---------------------------
   > TIP 6: Have you searched our list archives?
   >
   > http://archives.postgresql.org
   >
   
   
   

4. Re: nested select query failing

   SZUCS Gábor <surrano@mailbox.hu> — 2003-05-15T11:56:10Z

   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;
   
   
   

5. Re: nested select query failing

   Stephan Szabo <sszabo@megazone23.bigpanda.com> — 2003-05-15T14:56:49Z

   On Thu, 15 May 2003, [iso-8859-1] SZUCS Gbor wrote:
   
   > Basically, is it true that IN's can be converted to RIGHT JOIN's quite
   > simply? Is it always worth?
   
   I'm not sure you want to convert to an outer join (since you want to throw
   away the rows on either side that don't match in an IN).  You also have to
   be careful not to get duplicate entries from what was the subquery.
   
   As for whether it's worth doing, in 7.3 and earlier, almost
   certainly, in 7.4 almost certainly not. :)
   
   
   
   
   

6. Re: nested select query failing

   Amol Bhutada <amol@mithi.com> — 2003-05-20T07:26:16Z

   thanks allot everybody for your mails,
   
   - It helped and now I have got down the query execution time allot. But I am
   facing problem in following query
   -----------
   explain analyze select attached_info.id from attached_tag_list,
   attached_info
   where
            attached_tag_list.attached_tag = 265
            and
            attached_tag_list.id = attached_info.id
   ----------
   
   - it's result is
   ----------
   NOTICE:  QUERY PLAN:
   
   Nested Loop  (cost=0.00..165349.50 rows=114 width=16) (actual
   time=117.14..8994.60 rows=15 loops=1)
     ->  Index Scan using ix_attached_tag_list_id on attached_tag_list
   (cost=0.00..111.13 rows=96 width=12) (actual time=0.12..0.66 rows=15
   loops=1)
     ->  Seq Scan on attached_info  (cost=0.00..1211.53 rows=33553 width=4)
   (actual time=3.67..197.98 rows=33553 loops=15)
   Total runtime: 8994.92 msec
   
   EXPLAIN
   ---------
   
   - I have already indexed attached_info on id using following query
   ------
   CREATE  INDEX attached_info_Index_1 ON attached_info(id) ;
   ------
   
   - But I am wondering why there is  "->Seq Scan on attached_info."
           After reading various documentation on the internet I am assuming it
   should have been an index scan. BTW I have done vaccume analyze also.
   
   Am I right?
   
   thanks,
   Amol
   
   
   
   ----- Original Message -----
   From: "Stephan Szabo" <sszabo@megazone23.bigpanda.com>
   To: "SZUCS Gábor" <surrano@mailbox.hu>
   Cc: <pgsql-performance@postgresql.org>
   Sent: Thursday, May 15, 2003 8:26 PM
   Subject: Re: [PERFORM] nested select query failing
   
   
   On Thu, 15 May 2003, [iso-8859-1] SZUCS Gábor wrote:
   
   > Basically, is it true that IN's can be converted to RIGHT JOIN's quite
   > simply? Is it always worth?
   
   I'm not sure you want to convert to an outer join (since you want to throw
   away the rows on either side that don't match in an IN).  You also have to
   be careful not to get duplicate entries from what was the subquery.
   
   As for whether it's worth doing, in 7.3 and earlier, almost
   certainly, in 7.4 almost certainly not. :)
   
   
   
   
   ---------------------------(end of broadcast)---------------------------
   TIP 4: Don't 'kill -9' the postmaster
   
   
   
   
   

7. Re: nested select query failing

   Tom Lane <tgl@sss.pgh.pa.us> — 2003-05-20T15:18:26Z

   "amol" <amol@mithi.com> writes:
   > explain analyze select attached_info.id from attached_tag_list,
   > attached_info
   > where
   >          attached_tag_list.attached_tag = 265
   >          and
   >          attached_tag_list.id = attached_info.id
   
   > NOTICE:  QUERY PLAN:
   
   > Nested Loop  (cost=0.00..165349.50 rows=114 width=16) (actual
   > time=117.14..8994.60 rows=15 loops=1)
   >   ->  Index Scan using ix_attached_tag_list_id on attached_tag_list
   > (cost=0.00..111.13 rows=96 width=12) (actual time=0.12..0.66 rows=15
   > loops=1)
   >   ->  Seq Scan on attached_info  (cost=0.00..1211.53 rows=33553 width=4)
   > (actual time=3.67..197.98 rows=33553 loops=15)
   > Total runtime: 8994.92 msec
   
   > - I have already indexed attached_info on id using following query
   > CREATE  INDEX attached_info_Index_1 ON attached_info(id) ;
   
   Hm.  I'd have expected an index scan too.  Maybe the two id columns are
   not of the same datatype?
   
   			regards, tom lane
   
   

8. Re: nested select query failing

   Amol Bhutada <amol@mithi.com> — 2003-05-21T10:16:00Z

   Hi Tom,
   U are great. As you have said, one item was numeric and another serial
    integer ) so it was applying seq scan. Thank you very much for your help
   everybody.
   
   regards,
   Amol
   
   ----- Original Message -----
   From: "Tom Lane" <tgl@sss.pgh.pa.us>
   To: "amol" <amol@mithi.com>
   Cc: <pgsql-performance@postgresql.org>
   Sent: Tuesday, May 20, 2003 8:48 PM
   Subject: Re: [PERFORM] nested select query failing
   
   
   > "amol" <amol@mithi.com> writes:
   > > explain analyze select attached_info.id from attached_tag_list,
   > > attached_info
   > > where
   > >          attached_tag_list.attached_tag = 265
   > >          and
   > >          attached_tag_list.id = attached_info.id
   >
   > > NOTICE:  QUERY PLAN:
   >
   > > Nested Loop  (cost=0.00..165349.50 rows=114 width=16) (actual
   > > time=117.14..8994.60 rows=15 loops=1)
   > >   ->  Index Scan using ix_attached_tag_list_id on attached_tag_list
   > > (cost=0.00..111.13 rows=96 width=12) (actual time=0.12..0.66 rows=15
   > > loops=1)
   > >   ->  Seq Scan on attached_info  (cost=0.00..1211.53 rows=33553 width=4)
   > > (actual time=3.67..197.98 rows=33553 loops=15)
   > > Total runtime: 8994.92 msec
   >
   > > - I have already indexed attached_info on id using following query
   > > CREATE  INDEX attached_info_Index_1 ON attached_info(id) ;
   >
   > Hm.  I'd have expected an index scan too.  Maybe the two id columns are
   > not of the same datatype?
   >
   > regards, tom lane
   >
   > ---------------------------(end of broadcast)---------------------------
   > TIP 6: Have you searched our list archives?
   >
   > http://archives.postgresql.org
   >
   >