Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 8A3D164FCE6 for ; Thu, 21 Aug 2008 17:21:59 -0300 (ADT) Received: from postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 80297-04 for ; Thu, 21 Aug 2008 17:21:44 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from mail-gx0-f17.google.com (mail-gx0-f17.google.com [209.85.217.17]) by postgresql.org (Postfix) with ESMTP id 92A0164FCBB for ; Thu, 21 Aug 2008 17:21:46 -0300 (ADT) Received: by gxk10 with SMTP id 10so801104gxk.19 for ; Thu, 21 Aug 2008 13:21:45 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:received:received:message-id:date:from:to :subject:in-reply-to:mime-version:content-type:references; bh=to582xkAWxhT6eQG6lbZ9gp+BzbkBScjOBCP7pkC+YE=; b=ORC4Q5CP/5ePi1Mk3sSSzLN5jhwRb6Qwm8l4kjW8WkjIn33ajxA1mkyky76J/awvQd gxJsqC/U8oLI46lrq7QoLYmXgdqNzgTF6L7esQ5DyN6JGIM7fHBLR+qceiYM/Wzpmb41 fJvv6MD0koD8yvMmqmaJ0a49ULAFD0IDrWaio= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=message-id:date:from:to:subject:in-reply-to:mime-version :content-type:references; b=mV+0J+VeJJ6nQtT3iP5i+9KOwz44m508xB9C9ZeNUOr5sUV8/OdXtPm47X8nmbq6rB y9Bsg6v1H5IU3io5JtVgWF0ttREuigyepri/6KFt+uW4Td1rV1sqShdkhSRZB8cx9fLY dP4u//PjqBDfpGKo7cGYD/DbwGPJonl/w815c= Received: by 10.151.156.1 with SMTP id i1mr401776ybo.117.1219350105575; Thu, 21 Aug 2008 13:21:45 -0700 (PDT) Received: by 10.65.200.5 with HTTP; Thu, 21 Aug 2008 13:21:45 -0700 (PDT) Message-ID: Date: Fri, 22 Aug 2008 01:51:45 +0530 From: "c k" To: "Travis Smith" , pgsql-general@postgresql.org Subject: Re: [ADMIN] Query performance difference In-Reply-To: MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_30642_27857358.1219350105516" References: X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.001 tagged_above=0 required=5 tests=HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 200808/771 X-Sequence-Number: 136601 ------=_Part_30642_27857358.1219350105516 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline What do you want to say? Please give details. I got an explain from PGAdmin, as follows "GroupAggregate (cost=28927.37..35594.39 rows=156871 width=24)" " -> Sort (cost=28927.37..29319.55 rows=156871 width=24)" " Sort Key: docacctransactions.accgroupid, docs.docid, docs.docdate" " -> Hash Join (cost=3337.89..12170.02 rows=156871 width=24)" " Hash Cond: (docacctransactions.docid = docs.docid)" " -> Seq Scan on docacctransactions (cost=0.00..3665.71 rows=156871 width=20)" " -> Hash (cost=2545.06..2545.06 rows=48306 width=8)" " -> Seq Scan on docs (cost=0.00..2545.06 rows=48306 width=8)" Can you please give why there is a time difference? CPK On Fri, Aug 22, 2008 at 1:44 AM, Travis Smith wrote: > It may be worth-wile to generate an explain plan > > > > http://www.postgresql.org/docs/8.3/interactive/sql-explain.html > > > > > > > > > > *From:* pgsql-admin-owner@postgresql.org [mailto: > pgsql-admin-owner@postgresql.org] *On Behalf Of *c k > *Sent:* Thursday, August 21, 2008 2:49 PM > *To:* pgsql-general@postgresql.org; pgsql-admin@postgresql.org > *Subject:* [ADMIN] Query performance difference > > > > Hello, > I have a question regarding query performance from two pgsql applications. > PGadmin III 1.8 and Navicat for postgresql 8. Both connected to same server > on local machine 8.3 and runs a same query for appox. 1,60,000 rows with one > min, one max and addition of the min and max values on two tables joined on > an integer column gives different query execution times with noticible > difference. PGAdmin gives 9350ms and Navicat gives 2110ms. Why? What will be > the difference? > Is it in their query execution methods, with/without indexes etc?. > Thanks > > CPK > ------------------------------ > > The information in this email or in any file attached hereto is intended > only for the personal and confidential use of the individual or entity to > which it is addressed and may contain information that is proprietary and > confidential. If you are not the intended recipient of this message you are > hereby notified that any review, dissemination, distribution or copying of > this message is strictly prohibited. This communication is for information > purposes only and should not be regarded as an offer to sell or as a > solicitation of an offer to buy any financial product. Email transmission > cannot be guaranteed to be secure or error-free. P6070214 > ------=_Part_30642_27857358.1219350105516 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline
What do you want to say?
Please give details. I got an explain from PGAdmin,
as follows
"GroupAggregate  (cost=28927.37..35594.39 rows=156871 width=24)"
"  ->  Sort  (cost=28927.37..29319.55 rows=156871 width=24)"
"        Sort Key: docacctransactions.accgroupid, docs.docid, docs.docdate"
"        ->  Hash Join  (cost=3337.89..12170.02 rows=156871 width=24)"
"              Hash Cond: (docacctransactions.docid = docs.docid)"
"              ->  Seq Scan on docacctransactions  (cost=0.00..3665.71 rows=156871 width=20)"
"              ->  Hash  (cost=2545.06..2545.06 rows=48306 width=8)"
"                    ->  Seq Scan on docs  (cost=0.00..2545.06 rows=48306 width=8)"

Can you please give why there is a time difference?

CPK

On Fri, Aug 22, 2008 at 1:44 AM, Travis Smith <tsmith@optionshouse.com> wrote:

It may be worth-wile  to generate an explain plan

 

http://www.postgresql.org/docs/8.3/interactive/sql-explain.html

 

 

 

 

From: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin-owner@postgresql.org] On Behalf Of c k
Sent: Thursday, August 21, 2008 2:49 PM
To: pgsql-general@postgresql.org; pgsql-admin@postgresql.org
Subject: [ADMIN] Query performance difference

 

Hello,
I have a question regarding query performance from two pgsql applications. PGadmin III 1.8 and Navicat for postgresql 8. Both connected to same server on local machine 8.3 and runs a same query for appox. 1,60,000 rows with one min, one max and addition of the min and max values on two tables joined on an integer column gives different query execution times with noticible difference. PGAdmin gives 9350ms and Navicat gives 2110ms. Why? What will be the difference?
Is it in their query execution methods, with/without indexes etc?.
Thanks

CPK


The information in this email or in any file attached hereto is intended only for the personal and confidential use of the individual or entity to which it is addressed and may contain information that is proprietary and confidential. If you are not the intended recipient of this message you are hereby notified that any review, dissemination, distribution or copying of this message is strictly prohibited. This communication is for information purposes only and should not be regarded as an offer to sell or as a solicitation of an offer to buy any financial product. Email transmission cannot be guaranteed to be secure or error-free. P6070214

------=_Part_30642_27857358.1219350105516--