Re: nested select query failing - Mailing list pgsql-performance

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
>