Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1YazJn-0005pw-Ev for pgsql-admin@arkaria.postgresql.org; Thu, 26 Mar 2015 04:22:55 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1YazJm-0007BJ-Dw for pgsql-admin@arkaria.postgresql.org; Thu, 26 Mar 2015 04:22:54 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:DHE_RSA_AES_256_CBC_SHA256:256) (Exim 4.80) (envelope-from ) id 1YazJl-0007BC-0p for pgsql-admin@postgresql.org; Thu, 26 Mar 2015 04:22:53 +0000 Received: from eu1sys200aog127.obsmtp.com ([207.126.144.176]) by makus.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1YazJh-0007Wk-6E for pgsql-admin@postgresql.org; Thu, 26 Mar 2015 04:22:51 +0000 Received: from mail-qg0-f50.google.com ([209.85.192.50]) (using TLSv1) by eu1sys200aob127.postini.com ([207.126.147.11]) with SMTP ID DSNKVROJlJHvgAT3QCTVem9xn4xACNOX9MUQ@postini.com; Thu, 26 Mar 2015 04:22:48 UTC Received: by qgf60 with SMTP id 60so63425398qgf.3 for ; Wed, 25 Mar 2015 21:22:44 -0700 (PDT) X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20130820; h=x-gm-message-state:from:references:in-reply-to:mime-version :thread-index:date:message-id:subject:to:cc:content-type; bh=zWFKc5MsHBkra/8KIJ91+oPdFlI+dnySKV/ic4Nb9OU=; b=D7r1jWT8gWF1/6L4a3ubFXztPJViGgh2D0wI9TTJERnUuTCK/G09c8XD9LQ7VveSPr KK8ErhRsPtyzac1BBbXqxdeNPk3DinDVb0mrpeP8UcYK1nQ7nOJTAG1sR4GbjI6rn/Zc tYIlx9nNm8YV0VIc2sKBpW9IRBAmf2XTCsEybrmRvaOZKAAE/OH6Ade6qb61rcD/sLSD nm3fWTR/qLoUi954TUy2i5UeUQd7KTh4CGl321tKoKlL8LJ+2sP0tqnNLW1iBsX+BVj3 bcsp/Uh8IUWHCVvqohdbnFyx9FEMqCII0oms4WUjEolkIMvSbAdUDcLFzhzmbnmraz52 G9hw== X-Gm-Message-State: ALoCoQmozugx1awO5ssi1F+FiYmqgSE7TEoOC2Kk5hK7vBO05FTFlY79QcNLUrVShWO5VIhJ3nnBI8DO1owlYj8/AdDX4PqTLB+8pf/9Is0qzRd6O5hBMRGklWgPHf2CxUoVIipKOE6YYSDUprzdhYIBbWvldfhLww== X-Received: by 10.140.234.142 with SMTP id f136mr16456749qhc.13.1427343764228; Wed, 25 Mar 2015 21:22:44 -0700 (PDT) X-Received: by 10.140.234.142 with SMTP id f136mr16456741qhc.13.1427343764126; Wed, 25 Mar 2015 21:22:44 -0700 (PDT) From: Khangelani Gama References: In-Reply-To: MIME-Version: 1.0 X-Mailer: Microsoft Outlook 14.0 Thread-Index: AQHIZmirUWy6fLbq5d+yfQlWk3kQOwHgReGbnS8p2pA= Date: Thu, 26 Mar 2015 06:22:42 +0200 Message-ID: <53a085a60f456c87589e4b1224eaf369@mail.gmail.com> Subject: Re: Postgres 9 : - How to interpret the EXPLAIN query results To: Venkata Balaji N Cc: pgsql-admin@postgresql.org Content-Type: multipart/alternative; boundary=001a1135cad40896b70512295e13 X-Pg-Spam-Score: -3.6 (---) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-admin Precedence: bulk Sender: pgsql-admin-owner@postgresql.org --001a1135cad40896b70512295e13 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable *From:* Venkata Balaji N [mailto:nag1010@gmail.com] *Sent:* 26 March 2015 05:00 AM *To:* Khangelani Gama *Cc:* pgsql-admin@postgresql.org *Subject:* Re: [ADMIN] Postgres 9 : - How to interpret the EXPLAIN query results On Wed, Mar 25, 2015 at 7:00 PM, Khangelani Gama wrote= : Hi Please help, how can I interpret the following results of my EXPLAIN query ?, I can see that the query will through 18586018 rows, but what=E2=80=99s = the meaning of cost and width? GroupAggregate (cost=3D762381141.96..768375132.77 rows=3D18586018 width=3D= 32) cost=3D762381141.96..768375132.77 means, cost of retrieving the first row i= s 762381141.96 and the cost of retrieving all the rows is 768375132.77. rows=3D18586018 means, expected number of rows as an output. width=3D32 means, width of each row scanned/retrieved is 32 bytes. Cost calculation formula for PostgreSQL is as follows : cost =3D (disk pages read * seq_page_cost) + (rows scanned * cpu_tuple_cost= ) The above formula may help you analyze/reduce the cost of the query. If you do an EXPLAIN ANALYZE (sample output is below), that will further help you get more information like *query completion time in milli-seconds* *(actual time)*, *number of rows returned (rows)* and *how many iterations the query went through to get the output (loop)* GroupAggregate (cost=3Dxxxx.xx..yyyyy.yy rows=3Dxxxnx width=3Dxx) (actual time=3D(actual time=3Dxxx.xx rows=3Dxx loops=3Dx) Thanks, Venkata Balaji N Thank you very much !! CONFIDENTIALITY NOTICE The contents of and attachments to this e-mail are intended for the addre= ssee only, and may contain the confidential information of Argility (Proprietary) Limited and/or its subsidiaries. An= y review, use or dissemination thereof by anyone other than the intended addressee is prohibited.If you are not the intend= ed addressee please notify the writer immediately and destroy the e-mail. Argility (Proprietary) Limited and its subsidiari= es distance themselves from and accept no liability for unauthorised use of their e-mail facilities or e-mails sent other tha= n strictly for business purposes. =0D --001a1135cad40896b70512295e13 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable

= =C2=A0

=C2=A0

From:<= /b> Venkata Balaji N [mailto:nag1010@gmail.com]
Sent: 26 March 2015 05:= 00 AM
To: Khangelani Gama
Cc: pgsql-admin@postgresql.org
Subject: Re: = [ADMIN] Postgres 9 : - How to interpret the EXPLAIN query results

=C2=A0

=C2=A0

=C2=A0

On Wed, Mar = 25, 2015 at 7:00 PM, Khangelani Gama <kgama@argility.com> wrote:

Hi

=C2=A0

Please help, how can I interpret the following resul= ts of my EXPLAIN query ?, I can see that the query will through 18586018 ro= ws, but what=E2=80=99s the meaning of cost and width?

=C2=A0

GroupAggregate=C2=A0 (co= st=3D762381141.96..768375132.77 rows=3D18586018 width=3D32)

=

=C2=A0

cost=3D76= 2381141.96..768375132.77 means, cost of retrieving the first row is 7623811= 41.96 and the cost of retrieving all the rows is 768375132.77.

rows=3D18586018 means, expected number of rows as= an output.

width=3D32 means, width of each row scanned/retrieved is 32 bytes.

=

Cost calcu= lation formula for PostgreSQL is as follows :

cost =3D (disk pages read * seq_page= _cost) + (rows scanned * cpu_tuple_cost)

The above formula may help you analyze/reduce the cost of the query.

=

=C2=A0

If you do an EXPLAIN ANALYZE (sample outp= ut is below), that will further help you get more information like query= completion time in milli-seconds (actual time), number of ro= ws returned (rows) and how many iterations the query went through to= get the output (loop)

 GroupAggregate (cost=3Dxxxx.xx..y=
yyyy.yy rows=3Dxxxnx width=3Dxx) (actual time=3D(actual time=3Dxxx.xx rows=
=3Dxx loops=3Dx)

=C2=A0

<= /div>

Thanks,

Ve= nkata Balaji N

=C2=A0<= /span>

=C2=A0

=C2=A0

=C2=A0

=C2=A0

=C2=A0

=C2=A0

=C2=A0

=C2=A0

Than= k you very much !!


CONFIDENTIALITY NOTICE
The contents of and attachments to this e-mail are intended for the addre=
ssee only, and may contain the confidential
information of Argility (Proprietary) Limited and/or its subsidiaries. An=
y review, use or dissemination thereof by anyone
other than the intended addressee is prohibited.If you are not the intend=
ed addressee please notify the writer immediately
and destroy the e-mail. Argility (Proprietary) Limited and its subsidiari=
es distance themselves from and accept no liability
for unauthorised use of their e-mail facilities or e-mails sent other tha=
n strictly for business purposes.
=0D
--001a1135cad40896b70512295e13--