From waruna@nirmani.com Wed Sep 25 14:50:32 2002 Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 93D4E47683A for ; Wed, 25 Sep 2002 10:50:32 -0400 (EDT) Received: from kumudu.nslk.com (kumudu.nslk.com [64.247.55.254]) by postgresql.org (Postfix) with ESMTP id D9EBE47680D for ; Wed, 25 Sep 2002 10:50:31 -0400 (EDT) Received: from [203.94.94.112] (helo=W) by kumudu.nslk.com with asmtp (Exim 3.36 #1) id 17uDUl-0006yk-00 for pgsql-php@postgresql.org; Wed, 25 Sep 2002 10:50:32 -0400 Message-ID: <00a701c264a3$05bb4100$705e5ecb@A.GEEKIYANAGE> Reply-To: "Waruna Geekiyanage" From: "Waruna Geekiyanage" To: Subject: Fw: Sub SELECTS Date: Wed, 25 Sep 2002 20:51:20 +0600 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_00A4_01C264D5.4D40CFE0" X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 5.00.2919.6600 X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2919.6600 X-AntiAbuse: This header was added to track abuse, please include it with any abuse report X-AntiAbuse: Primary Hostname - kumudu.nslk.com X-AntiAbuse: Original Domain - postgresql.org X-AntiAbuse: Originator/Caller UID/GID - [0 0] / [0 0] X-AntiAbuse: Sender Address Domain - nirmani.com X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200209/53 X-Sequence-Number: 1508 This is a multi-part message in MIME format. ------=_NextPart_000_00A4_01C264D5.4D40CFE0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable I have a select query with 2 sub selects. When I run that query CPU usage goes to 98% for postgres. Does sub selects eat more resources and slower the queries? Is there any alternatives to sub selects? ------=_NextPart_000_00A4_01C264D5.4D40CFE0 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
 

I have a select query with 2 sub=20 selects.
When I run that query CPU usage goes = to 98%=20 for postgres.
Does sub selects eat more resources a= nd slower=20 the queries?
Is there any alternatives to sub=20 selects?
------=_NextPart_000_00A4_01C264D5.4D40CFE0-- From hierophant@pcisys.net Wed Sep 25 15:08:43 2002 Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id E9726475EE1 for ; Wed, 25 Sep 2002 11:08:43 -0400 (EDT) Received: from marsha.pcisys.net (marsha.pcisys.net [216.229.32.146]) by postgresql.org (Postfix) with ESMTP id 1594C475D64 for ; Wed, 25 Sep 2002 11:08:43 -0400 (EDT) Received: from [216.229.45.29] (moe.rbe45-29.cos.pcisys.net [216.229.45.29]) by marsha.pcisys.net (8.12.5/8.12.5) with ESMTP id g8PF8kOf006677 for ; Wed, 25 Sep 2002 09:08:46 -0600 (MDT) User-Agent: Microsoft-Outlook-Express-Macintosh-Edition/5.0.4 Date: Wed, 25 Sep 2002 09:08:46 -0600 Subject: Re: Fw: Sub SELECTS From: Keary Suska To: Postgres-PHP Message-ID: In-Reply-To: <00a701c264a3$05bb4100$705e5ecb@A.GEEKIYANAGE> Mime-version: 1.0 Content-type: multipart/alternative; boundary="MS_Mac_OE_3115789726_2518518_MIME_Part" X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200209/55 X-Sequence-Number: 1510 > This message is in MIME format. Since your mail reader does not understand this format, some or all of this message may not be legible. --MS_Mac_OE_3115789726_2518518_MIME_Part Content-type: text/plain; charset="US-ASCII" Content-transfer-encoding: 7bit on 9/25/02 8:51 AM, waruna@nirmani.com purportedly said: I have a select query with 2 sub selects. When I run that query CPU usage goes to 98% for postgres. Does sub selects eat more resources and slower the queries? Is there any alternatives to sub selects? That high CPU usage seems odd. I wouldn't think that even extremely complex queries would effect CPU that much, unless there are other factors such as low memory that would contribute. If any case, if you post the query and table schemas we might be able to make suggestions. Sub-selects are generally fairly efficient unless the results from the sub-selects are particularly large. Postgres may not be able to use indexes on sub-select results, I am not sure. Keary Suska Esoteritech, Inc. "Leveraging Open Source for a better Internet" --MS_Mac_OE_3115789726_2518518_MIME_Part Content-type: text/html; charset="US-ASCII" Content-transfer-encoding: quoted-printable Re: [PHP] Fw: Sub SELECTS on 9/25/02 8:51 AM, waruna@nirmani.com purportedly said:

I have a select query wit= h 2 sub selects.
When I run that query CPU usage goes to 98% for postgres.
Does sub selects eat more resources and slower the queries?
Is there any alternatives to sub selects?

That high CPU usage seems odd. I wouldn't think that even extremely complex= queries would effect CPU that much, unless there are other factors such as= low memory that would contribute.

If any case, if you post the query and table schemas we might be able to ma= ke suggestions. Sub-selects are generally fairly efficient unless the resul= ts from the sub-selects are particularly large. Postgres may not be able to= use indexes on sub-select results, I am not sure.

Keary Suska
Esoteritech, Inc.
"Leveraging Open Source for a better Internet"
--MS_Mac_OE_3115789726_2518518_MIME_Part-- From roland@astrofoto.org Fri Sep 27 17:34:04 2002 Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 1839647673E for ; Fri, 27 Sep 2002 13:34:04 -0400 (EDT) Received: from mail.astrofoto.org (dsl092-102-177.nyc2.dsl.speakeasy.net [66.92.102.177]) by postgresql.org (Postfix) with ESMTP id 4A7FB4762D8 for ; Fri, 27 Sep 2002 13:34:02 -0400 (EDT) Received: from kuiper.rlent.pnet (IDENT:root@kuiper.rlent.pnet [192.168.3.50]) by mail.astrofoto.org (8.12.3/8.12.3) with ESMTP id g8RHY1UZ013357 for ; Fri, 27 Sep 2002 13:34:01 -0400 Received: (from roland@localhost) by kuiper.rlent.pnet (8.11.6/8.11.6) id g8RHY0807019; Fri, 27 Sep 2002 13:34:00 -0400 X-Authentication-Warning: kuiper.rlent.pnet: roland set sender to roland@astrofoto.org using -f To: Subject: Re: Fw: Sub SELECTS References: <00a701c264a3$05bb4100$705e5ecb@A.GEEKIYANAGE> From: Roland Roberts Date: 27 Sep 2002 13:34:00 -0400 In-Reply-To: <00a701c264a3$05bb4100$705e5ecb@A.GEEKIYANAGE> Message-ID: Lines: 18 User-Agent: Gnus/5.09 (Gnus v5.9.0) Emacs/21.2.90 MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200209/57 X-Sequence-Number: 1512 >>>>> "Waruna" == Waruna Geekiyanage writes: Waruna> I have a select query with 2 sub selects. When I run that Waruna> query CPU usage goes to 98% for postgres. Does sub Waruna> selects eat more resources and slower the queries? Is Waruna> there any alternatives to sub selects? You are going to have to give us a clue as to what those sub-selects are and what they are doing. If there is an "order by" clause anywhere, that could cause high CPU. If the query is not using indicies, that might cause a problem. Have you tried EXPLAIN? roland -- PGP Key ID: 66 BC 3B CD Roland B. Roberts, PhD RL Enterprises roland@rlenter.com 76-15 113th Street, Apt 3B roland@astrofoto.org Forest Hills, NY 11375