Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1YaiYT-0002vL-Vn for pgsql-admin@arkaria.postgresql.org; Wed, 25 Mar 2015 10:28:58 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1YaiYT-0001PI-Fh for pgsql-admin@arkaria.postgresql.org; Wed, 25 Mar 2015 10:28:57 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:DHE_RSA_AES_256_CBC_SHA256:256) (Exim 4.80) (envelope-from ) id 1YaiYR-0001O0-Pn for pgsql-admin@postgresql.org; Wed, 25 Mar 2015 10:28:55 +0000 Received: from eu1sys200aog111.obsmtp.com ([207.126.144.131]) by magus.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1YaiYM-0002k9-G0 for pgsql-admin@postgresql.org; Wed, 25 Mar 2015 10:28:54 +0000 Received: from mail-qg0-f49.google.com ([209.85.192.49]) (using TLSv1) by eu1sys200aob111.postini.com ([207.126.147.11]) with SMTP ID DSNKVRKN3dcICL1fuHv1w4r5dMdBatiX/xQ3@postini.com; Wed, 25 Mar 2015 10:28:50 UTC Received: by qgep97 with SMTP id p97so33567317qge.1 for ; Wed, 25 Mar 2015 03:28: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=OopC0fOadhBOYkXLu0ceopKXEN37S2j2hFU+clo4gqc=; b=Ca6MpFvQoYLsJ0/oNdyE8kem9y2DUimDV2EVW9iS4vNbF1IvNRhG1VdhwI03+QxcNv GW0KNSxwjdT4t6W4tx3BoRGbNM5679RWKDQgILT9YHN+kWr+IYlT0r7bBCbNX8LBCpdS jorxuUW9ie3VMPrs494uMi5IG5i14NIUbt5QITuPFxZC+0SuO2D8+p81BWChHbGIWtic FirJ45J1NUCXh8GEYgnXCv4A7nqWEUGpx2pAIS1aq6AzlEYImYLf/UCqhQSpPIQWe/ad XPsKNMEvAogibtN5BDSCjYFyVRXQ9ByhrEVdL4tFt9oMAOjbilZwYPW+Rax5p3U9OxnG RNFw== X-Gm-Message-State: ALoCoQnWBwxsXCngYJG8AOJmUCs/+UoyyGxzo5Vmn95a+PIMzhkSYVKI7oDOuMTPoE2+IyjfCTAUY5xLqED4tTjAd3KJ1UH2/IKT3V9kIDBIUsPaUtPM8TAkxpIndIX3tUDE3ohKHJP2R0gfr4imNhFFowcTfSOB1w== X-Received: by 10.140.94.242 with SMTP id g105mr10629855qge.2.1427279324919; Wed, 25 Mar 2015 03:28:44 -0700 (PDT) X-Received: by 10.140.94.242 with SMTP id g105mr10629848qge.2.1427279324777; Wed, 25 Mar 2015 03:28:44 -0700 (PDT) From: Khangelani Gama References: In-Reply-To: MIME-Version: 1.0 X-Mailer: Microsoft Outlook 14.0 thread-index: AQHIZmirUWy6fLbq5d+yfQlWk3kQOwJAd42DnSr6JmA= Date: Wed, 25 Mar 2015 12:28:43 +0200 Message-ID: <80b319b3f6696a7f1672a4640f55e803@mail.gmail.com> Subject: Re: Postgres 9 : - How to interpret the EXPLAIN query results To: Raghavendra Cc: pgsql-admin Content-Type: multipart/alternative; boundary=001a113acd5026263d05121a5db1 X-Pg-Spam-Score: -4.2 (----) 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 --001a113acd5026263d05121a5db1 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable *From:* Raghavendra [mailto:raghavendra.rao@enterprisedb.com] *Sent:* 25 March 2015 12:04 PM *To:* Khangelani Gama *Cc:* pgsql-admin *Subject:* Re: [ADMIN] Postgres 9 : - How to interpret the EXPLAIN query results On Wed, Mar 25, 2015 at 1:30 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) =E2=80=8BIts like .. =E2=80=8BNodeType (cost=3Dstartup-cost..total-cost rows=3Dplan rows width= =3Dbytes per-row) Documentation has briefed well on the explain output. Here's example. Thanks to all, but if this total cost is the total time it will take to retrieve the output then is it in minutes or milliseconds? If it=E2=80=99s = in milliseconds does it mean that it take about 213 hrs for a total of 768375132.77 postgres=3D# explain select * from foo; QUERY PLAN ----------------------------------------------------------- Seq Scan on foo (cost=3D0.00..5770.01 rows=3D400001 width=3D4) (1 row) postgres=3D# EXPLAIN (FORMAT JSON) SELECT * FROM foo; QUERY PLAN -------------------------------- [ + { + "Plan": { + "Node Type": "Seq Scan",+ "Relation Name": "foo", + "Alias": "foo", + "Startup Cost": 0.00, + "Total Cost": 5770.01, + "Plan Rows": 400001, + "Plan Width": 4 + } + } + ] (1 row) http://www.postgresql.org/docs/9.3/static/sql-explain.html --- Regards, Raghavendra EnterpriseDB Corporation Blog: http://raghavt.blogspot.com/=E2=80=8B CONFIDENTIALITY NOTICE The contents of and attachments to this e-mail are intended for the addressee only, and may contain the confidential information of Argility (Proprietary) Limited and/or its subsidiaries. Any review, use or dissemination thereof by anyone other than the intended addressee is prohibited.If you are not the intended addressee please notify the writer immediately and destroy the e-mail. Argility (Proprietary) Limited and its subsidiaries distance themselves from and accept no liability for unauthorised use of their e-mail facilities or e-mails sent other than strictly for business purposes. 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 --001a113acd5026263d05121a5db1 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable

= =C2=A0

=C2=A0

From:<= /b> Raghavendra [mailto:raghavendra.rao@enterprisedb.com]
Se= nt: 25 March 2015 12:04 PM
To: Khangelani Gama
Cc: = pgsql-admin
Subject: Re: [ADMIN] Postgres 9 : - How to interpret = the EXPLAIN query results

=C2=A0

<= div>

On Wed, Mar 25, 2015 at 1:30 PM, Khangelani Gama <<= a href=3D"mailto:kgama@argility.com" target=3D"_blank">kgama@argility.com> wrote:

Hi

=C2=A0

Please help= , how can I interpret the following results of my EXPLAIN query ?, I can se= e that the query will through 18586018 rows, but what=E2=80=99s the meaning= of cost and width?=C2=A0

=C2= =A0=C2=A0

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

=C2=A0

=E2=80=8BIts like ..

=C2=A0

=E2=80=8BNodeType (= cost=3Dstartup-cost..total-cost rows=3Dplan rows width=3Dbytes per-row)

=C2=A0

Documentation has briefed well on the explain output. He= re's example.

=C2=A0

=C2= =A0

Thanks =C2= =A0to all, but if this total cost is the total time it will take to retriev= e the output then is it in minutes or milliseconds? If it=E2=80=99s in mill= iseconds does it mean that it take about 213 hrs for a total of 7683= 75132.77

=C2=A0

=C2=A0<= /p>

=C2=A0

=C2=A0

=C2=A0

=C2=A0

=C2=A0

postgres=3D# explain select * from foo;

=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 QUERY PLAN

----------------------------------------------------------= -

=C2=A0Seq Scan on foo =C2=A0= (cost=3D0.00..5770.01 rows=3D400001 width=3D4)

(1 row)

=C2= =A0

=C2=A0

postgres=3D# EXPLAIN (FORMAT JSON) SEL= ECT * FROM foo;

=C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0QUERY PLAN

--------------------------------

=C2=A0[ =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 +

=C2=A0 =C2=A0{ =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 +

=C2=A0 =C2=A0 =C2=A0"Plan": { =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 +

=C2=A0 =C2=A0 =C2=A0 =C2=A0"Node Type": "Seq= Scan",+

=C2=A0 =C2=A0 = =C2=A0 =C2=A0"Relation Name": "foo", +

=

=C2=A0 =C2=A0 =C2=A0 =C2=A0"Alias": = "foo", =C2=A0 =C2=A0 =C2=A0 =C2=A0 +

=C2=A0 =C2=A0 =C2=A0 =C2=A0"Startup Cost": 0.00,= =C2=A0 +

=C2=A0 =C2=A0 =C2=A0= =C2=A0"Total Cost": 5770.01, =C2=A0+

=C2=A0 =C2=A0 =C2=A0 =C2=A0"Plan Rows": 400001,= =C2=A0 =C2=A0+

=C2=A0 =C2=A0 = =C2=A0 =C2=A0"Plan Width": 4 =C2=A0 =C2=A0 =C2=A0 =C2=A0 +=

=C2=A0 =C2=A0 =C2=A0} =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 +

=C2=A0 =C2=A0} =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 +=

=C2=A0]

=

(1 row)

=C2=A0

---

Regards,

Raghavendra

Enterpr= iseDB Corporation

Blog:=C2= =A0http://raghav= t.blogspot.com/=E2=80=8B

=C2=A0

=C2=A0

=C2=A0

= =C2=A0

=C2=A0

=C2=A0

CONFIDENTIALITY NOTICE
The c=
ontents of and attachments to this e-mail are intended for the addressee on=
ly, and may contain the confidential
information of Argility (Pro=
prietary) Limited and/or its subsidiaries. Any review, use or dissemination=
 thereof by anyone
other than the intended addressee is prohibite=
d.If you are not the intended addressee please notify the writer immediatel=
y
and destroy the e-mail. Argility (Proprietary) Limited and its =
subsidiaries distance themselves from and accept no liability
for=
 unauthorised use of their e-mail facilities or e-mails sent other than str=
ictly for business purposes.
=C2=A0

=C2=A0


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
--001a113acd5026263d05121a5db1--