pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Christopher Kings-Lynne <chriskl@familyhealth.com.au>
To: amol <amol@mithi.com>
To: pgsql-performance@postgresql.org
Subject: Re: nested select query failing
Date: Thu, 15 May 2003 12:24:08 +0800
Message-ID: <063501c31a99$d4425020$6500a8c0@fhp.internal> (raw)
References: <003c01c31a96$1d1703d0$2e00a8c0@amol>

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
>




view thread (8+ messages)  latest in thread

Message-ID: <063501c31a99$d4425020$6500a8c0@fhp.internal>
Permalink:  ../063501c31a99$d4425020$6500a8c0@fhp.internal/
Also on:    postgresql.org/message-id/063501c31a99$d4425020$6500a8c0@fhp.internal

 · 

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: chriskl@familyhealth.com.au, amol@mithi.com
  Subject: Re: nested select query failing
  In-Reply-To: <063501c31a99$d4425020$6500a8c0@fhp.internal>
  References: <003c01c31a96$1d1703d0$2e00a8c0@amol> <063501c31a99$d4425020$6500a8c0@fhp.internal>

* 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