From azajac@ang.com.pl Mon Nov 15 08:42:15 2010 Received: from maia.hub.org (maia-5.hub.org [200.46.204.29]) by mail.postgresql.org (Postfix) with ESMTP id 66D521337B93 for ; Mon, 15 Nov 2010 04:42:15 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.29]) (amavisd-maia, port 10024) with ESMTP id 29787-03-8 for ; Mon, 15 Nov 2010 08:42:08 +0000 (UTC) X-Greylist: delayed 00:20:28.929718 by SQLgrey-1.7.6 Received: from wentylator.ang.com.pl (wentylator.ang.com.pl [89.174.215.202]) by mail.postgresql.org (Postfix) with ESMTP id 7922B1337BB2 for ; Mon, 15 Nov 2010 04:42:06 -0400 (AST) Received: from Artur7 (cfidsl [83.18.105.226]) by wentylator.ang.com.pl (Postfix) with ESMTP id 9D7A52DD260 for ; Mon, 15 Nov 2010 09:21:36 +0100 (CET) From: =?iso-8859-2?Q?Artur_Zaj=B1c?= To: Subject: Difference between explain analyze and real execution time Date: Mon, 15 Nov 2010 09:21:34 +0100 Message-ID: <000001cb849e$1dce2720$596a7560$@ang.com.pl> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0001_01CB84A6.7F983470" X-Mailer: Microsoft Outlook 14.0 Thread-Index: AcuEnNGVXWIXNqA1T76Mn9kDg4FPTg== Content-Language: pl X-wentylator-MailScanner-Information: Please contact the ISP for more information X-wentylator-MailScanner: Found to be clean X-wentylator-MailScanner-SpamCheck: not spam, SpamAssassin (not cached, score=0, required 6, autolearn=not spam) X-wentylator-MailScanner-From: azajac@ang.com.pl X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-0.509 tagged_above=-10 required=5 tests=BAYES_05=-0.5, HTML_MESSAGE=0.001, T_RP_MATCHES_RCVD=-0.01 X-Spam-Level: X-Archive-Number: 201011/242 X-Sequence-Number: 41266 This is a multipart message in MIME format. ------=_NextPart_000_0001_01CB84A6.7F983470 Content-Type: text/plain; charset="iso-8859-2" Content-Transfer-Encoding: 7bit I have some simple query (executed with time command): time psql -c 'explain analyze SELECT te.idt FROM t_positions AS te JOIN t_st AS stm ON (te.idt=stm.idt AND 4=stm.idm) WHERE te.idtr IN (347186)' QUERY PLAN ---------------------------------------------------------------------------- ---------------------------------------------------------------------------- Nested Loop (cost=0.00..33.33 rows=2 width=4) (actual time=0.297..0.418 rows=3 loops=1) -> Index Scan using t_positions_index1 on t_positions te (cost=0.00..8.43 rows=3 width=4) (actual time=0.140..0.148 rows=3 loops=1) Index Cond: (idtr = 347186) -> Index Scan using t_st_index4 on t_st stm (cost=0.00..8.29 rows=1 width=4) (actual time=0.078..0.079 rows=1 loops=3) Index Cond: ((stm.idt = te.idt) AND (4 = stm.idm)) Total runtime: 0.710 ms (6 rows) real 0m3.309s user 0m0.002s sys 0m0.002s Why there is so big difference between explain analyze (0.710 ms) and real execution time (3309 ms)? Any suggestions? Psql only execution time: time psql -c 'explain analyze SELECT blabla()' ERROR: function blabla() does not exist real 0m0.011s user 0m0.001s sys 0m0.004s SELECT version(); PostgreSQL 9.0.1 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.1.2 20070502 (Red Hat 4.1.2-12), 32-bit ------------------------------------------- Artur Zajac ------=_NextPart_000_0001_01CB84A6.7F983470 Content-Type: text/html; charset="iso-8859-2" Content-Transfer-Encoding: quoted-printable

I have some simple query (executed with time = command):

 

 

time psql =A0-c 'explain analyze SELECT te.idt FROM = t_positions AS te=A0 JOIN t_st AS stm ON (te.idt=3Dstm.idt AND = 4=3Dstm.idm)=A0=A0 WHERE te.idtr IN (347186)'

 

 

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

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

Nested Loop=A0 (cost=3D0.00..33.33 rows=3D2 width=3D4) = (actual time=3D0.297..0.418 rows=3D3 loops=3D1)

=A0=A0 ->=A0 Index Scan using = t_positions_index1 on t_positions te=A0 (cost=3D0.00..8.43 rows=3D3 = width=3D4) (actual time=3D0.140..0.148 rows=3D3 = loops=3D1)

=A0=A0=A0=A0=A0=A0=A0=A0 Index Cond: (idtr =3D = 347186)

=A0=A0 ->=A0 Index Scan using t_st_index4 on t_st stm=A0 = (cost=3D0.00..8.29 rows=3D1 width=3D4) (actual time=3D0.078..0.079 = rows=3D1 loops=3D3)

=A0=A0=A0=A0=A0=A0=A0=A0 Index Cond: ((stm.idt =3D te.idt) = AND (4 =3D stm.idm))

Total runtime: 0.710 ms

(6 rows)

 

 

real=A0=A0=A0 = 0m3.309s

user=A0=A0=A0 0m0.002s

sys=A0=A0=A0=A0 = 0m0.002s

 

Why there is so big difference between explain analyze = (0.710 ms) and real execution time (3309 ms)? Any = suggestions?

 

Psql only execution time:

 

time psql -c 'explain analyze = SELECT blabla()'

ERROR:=A0 function blabla() does not = exist

 

real=A0=A0=A0 0m0.011s

user=A0=A0=A0 = 0m0.001s

sys=A0=A0=A0=A0 0m0.004s

 

SELECT = version();

 

PostgreSQL 9.0.1 on i686-pc-linux-gnu, compiled by GCC gcc = (GCC) 4.1.2 20070502 (Red Hat 4.1.2-12), 32-bit

 

-------------------------------------------<= /p>

Artur Zajac

 

 

------=_NextPart_000_0001_01CB84A6.7F983470-- From tgl@sss.pgh.pa.us Mon Nov 15 15:13:08 2010 Received: from maia.hub.org (maia-3.hub.org [200.46.204.243]) by mail.postgresql.org (Postfix) with ESMTP id 05C631337B66 for ; Mon, 15 Nov 2010 11:13:08 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia, port 10024) with ESMTP id 82997-01 for ; Mon, 15 Nov 2010 15:13:00 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by mail.postgresql.org (Postfix) with ESMTP id 6CFD01335B36 for ; Mon, 15 Nov 2010 11:13:00 -0400 (AST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.2/8.14.2) with ESMTP id oAFFCxj3013815; Mon, 15 Nov 2010 10:12:59 -0500 (EST) To: =?iso-8859-2?Q?Artur_Zaj=B1c?= cc: pgsql-performance@postgresql.org Subject: Re: Difference between explain analyze and real execution time In-reply-to: <000001cb849e$1dce2720$596a7560$@ang.com.pl> References: <000001cb849e$1dce2720$596a7560$@ang.com.pl> Comments: In-reply-to =?iso-8859-2?Q?Artur_Zaj=B1c?= message dated "Mon, 15 Nov 2010 09:21:34 +0100" Date: Mon, 15 Nov 2010 10:12:59 -0500 Message-ID: <13814.1289833979@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.91 tagged_above=-10 required=5 tests=BAYES_00=-1.9, T_RP_MATCHES_RCVD=-0.01 X-Spam-Level: X-Archive-Number: 201011/244 X-Sequence-Number: 41268 =?iso-8859-2?Q?Artur_Zaj=B1c?= writes: > Why there is so big difference between explain analyze (0.710 ms) and real > execution time (3309 ms)? EXPLAIN ANALYZE doesn't account for all of the runtime involved. In this case, I'd bet that session startup/shutdown is a big part of the difference. regards, tom lane From azajac@ang.com.pl Mon Nov 15 15:24:39 2010 Received: from maia.hub.org (maia-3.hub.org [200.46.204.243]) by mail.postgresql.org (Postfix) with ESMTP id C0CA31337B4E for ; Mon, 15 Nov 2010 11:24:39 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia, port 10024) with ESMTP id 92599-05 for ; Mon, 15 Nov 2010 15:24:32 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from wentylator.ang.com.pl (wentylator.ang.com.pl [89.174.215.202]) by mail.postgresql.org (Postfix) with ESMTP id 7EAED1337B3E for ; Mon, 15 Nov 2010 11:24:32 -0400 (AST) Received: from Artur7 (cfidsl [83.18.105.226]) by wentylator.ang.com.pl (Postfix) with ESMTP id EF3582DD260 for ; Mon, 15 Nov 2010 16:24:34 +0100 (CET) From: =?iso-8859-2?Q?Artur_Zaj=B1c?= To: References: <000001cb849e$1dce2720$596a7560$@ang.com.pl> <13814.1289833979@sss.pgh.pa.us> In-Reply-To: <13814.1289833979@sss.pgh.pa.us> Subject: Re: Difference between explain analyze and real execution time Date: Mon, 15 Nov 2010 16:24:30 +0100 Message-ID: <008e01cb84d9$33797a80$9a6c6f80$@ang.com.pl> MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-2" Content-Transfer-Encoding: 7bit X-Mailer: Microsoft Outlook 14.0 Thread-Index: AQFsDnYhYIkerMawLWBzle0OUvs2zwKi9oQ0lBx9oCA= Content-Language: pl X-wentylator-MailScanner-Information: Please contact the ISP for more information X-wentylator-MailScanner: Found to be clean X-wentylator-MailScanner-SpamCheck: not spam, SpamAssassin (not cached, score=0, required 6, autolearn=not spam) X-wentylator-MailScanner-From: azajac@ang.com.pl X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.91 tagged_above=-10 required=5 tests=BAYES_00=-1.9, T_RP_MATCHES_RCVD=-0.01 X-Spam-Level: X-Archive-Number: 201011/245 X-Sequence-Number: 41269 > EXPLAIN ANALYZE doesn't account for all of the runtime involved. In this case, I'd bet that session startup/shutdown is a big part of the difference. > > regards, tom lane Does session startup/shutdown depend on tables used in query? Some simpler query: time psql -c 'explain analyze SELECT te.idt FROM t_positions AS te WHERE te.idtr IN (347186)' QUERY PLAN ---------------------------------------------------------------------------- --------------------------------------------------------- Index Scan using tg_transelem_index1 on tg_transelem te (cost=0.00..8.43 rows=3 width=4) (actual time=0.130..0.134 rows=3 loops=1) Index Cond: (tr_idtrans = 347186) Total runtime: 0.211 ms real 0m0.017s user 0m0.002s sys 0m0.004s ------------------------------------------- Artur Zajac From tobixen@gmail.com Mon Nov 15 15:25:46 2010 Received: from maia.hub.org (maia-3.hub.org [200.46.204.243]) by mail.postgresql.org (Postfix) with ESMTP id 2E5641337B43 for ; Mon, 15 Nov 2010 11:25:46 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia, port 10024) with ESMTP id 93250-06 for ; Mon, 15 Nov 2010 15:25:39 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from mail-vw0-f46.google.com (mail-vw0-f46.google.com [209.85.212.46]) by mail.postgresql.org (Postfix) with ESMTP id D87C91337B3E for ; Mon, 15 Nov 2010 11:25:38 -0400 (AST) Received: by vws13 with SMTP id 13so1596655vws.19 for ; Mon, 15 Nov 2010 07:25:39 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:received:mime-version:received:in-reply-to :references:from:date:message-id:subject:to:cc:content-type :content-transfer-encoding; bh=MSKQQH3qOECEB+I3uPy1QLy1lqJpqTStICVyp/waH1Q=; b=gXpXhcPRSz/V0XLIFqSBDsd0XxC2pLa/1AQjIdSHkYSUhsO8nzKfo98ICaVDiDWkiW G+TP23saBfuskQ8/HQwxk6IeND5yDnfmT2c6NG2Erwk5TQsD10a3zlGmzf7RFUavTo6i 6C+KSr25WxYjQPOfRjS9j/P7jaG+XqexXTAi0= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=mime-version:in-reply-to:references:from:date:message-id:subject:to :cc:content-type:content-transfer-encoding; b=MkwawYEgBEYtrGafaNmpXOlql6wV0w1576HS3bVq3Lgtj50oRwxRSPRVuMtcAk/Z/x Ti2FAMEoNgyaiTRgL3I77jkuMlwJbluv/T8kVt2J91+jduUZtVTLhlDGwM0HWZoj9gyF tGudn9YqDbM38YRlcjzfS20IuGFHO8uA2iol8= Received: by 10.229.240.213 with SMTP id lb21mr5287109qcb.185.1289834738957; Mon, 15 Nov 2010 07:25:38 -0800 (PST) MIME-Version: 1.0 Received: by 10.229.49.83 with HTTP; Mon, 15 Nov 2010 07:25:18 -0800 (PST) In-Reply-To: <13814.1289833979@sss.pgh.pa.us> References: <000001cb849e$1dce2720$596a7560$@ang.com.pl> <13814.1289833979@sss.pgh.pa.us> From: Tobias Brox Date: Mon, 15 Nov 2010 16:25:18 +0100 Message-ID: Subject: Re: Difference between explain analyze and real execution time To: Tom Lane Cc: =?ISO-8859-2?Q?Artur_Zaj=B1c?= , pgsql-performance@postgresql.org Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.9 tagged_above=-10 required=5 tests=BAYES_00=-1.9, RCVD_IN_DNSWL_NONE=-0.0001 X-Spam-Level: X-Archive-Number: 201011/246 X-Sequence-Number: 41270 [Tom Lane] > EXPLAIN ANALYZE doesn't account for all of the runtime involved. =C2=A0In > this case, I'd bet that session startup/shutdown is a big part of the > difference. The session startup/shutdown should be the same for the real SQL and the broken SQL, shouldn't it? [Artur Zaj=C4=85c] > time psql -c 'explain analyze SELECT te.idt FROM t_positions AS te > JOIN t_st AS stm ON (te.idt=3Dstm.idt AND 4=3Dstm.idm) WHERE te.idtr IN > (347186)' Is this weidness only observed for this query? What happens with other queries? "explain analyze select 1"? "explain analyze select * from t_positions where idtr=3D347816"? plain select without "explain analyze"? etc? From robertmhaas@gmail.com Mon Nov 15 19:39:13 2010 Received: from maia.hub.org (maia-5.hub.org [200.46.204.29]) by mail.postgresql.org (Postfix) with ESMTP id EA7361337B79 for ; Mon, 15 Nov 2010 15:39:13 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.29]) (amavisd-maia, port 10024) with ESMTP id 82732-01-3 for ; Mon, 15 Nov 2010 19:39:05 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from mail-iw0-f174.google.com (mail-iw0-f174.google.com [209.85.214.174]) by mail.postgresql.org (Postfix) with ESMTP id 9741A1337B69 for ; Mon, 15 Nov 2010 15:39:04 -0400 (AST) Received: by iwn35 with SMTP id 35so488867iwn.19 for ; Mon, 15 Nov 2010 11:39:04 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:mime-version:received:received:in-reply-to :references:date:message-id:subject:from:to:cc:content-type :content-transfer-encoding; bh=OK4XU1Ma/9wQdVmR5PyY2qbsbdyofWXr4A30u+L3BOg=; b=AgTRb8gFofWg/w6xnKSyZ3JJL4jJFY0ZpvDHeOJ1vHlVszsBSEI75TaErZdyzYcZ5A MqSjhpTvAPIPPDheIYFZ5TcXozEDd89OMfS/9E7dVa/SOc8I5s2hCzlrA0yasfIpuT7d 0gIGXLC1NCHD7Itnh1obER8chdEcCRDFXad1Q= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=mime-version:in-reply-to:references:date:message-id:subject:from:to :cc:content-type:content-transfer-encoding; b=YvXBI/pjeNTVJoKhT+/65JCcLL74y5QkJuBNYdgcgTtr2tXeU99aOn+IuxOTGBfLSx DVAdVb+F9xMZAgi78ATsD8eebZIkEhVKWFwJ7rTyolrIaQvUVkyLDXXHJVI9dLYKFo9D 7T+zPBiYxTMbr36+9qgAbGKa0ACdaTnk4VKtE= MIME-Version: 1.0 Received: by 10.231.36.204 with SMTP id u12mr4669097ibd.149.1289849943152; Mon, 15 Nov 2010 11:39:03 -0800 (PST) Received: by 10.231.10.135 with HTTP; Mon, 15 Nov 2010 11:39:03 -0800 (PST) In-Reply-To: <000001cb849e$1dce2720$596a7560$@ang.com.pl> References: <000001cb849e$1dce2720$596a7560$@ang.com.pl> Date: Mon, 15 Nov 2010 14:39:03 -0500 Message-ID: Subject: Re: Difference between explain analyze and real execution time From: Robert Haas To: =?ISO-8859-2?Q?Artur_Zaj=B1c?= Cc: pgsql-performance@postgresql.org Content-Type: text/plain; charset=ISO-8859-2 Content-Transfer-Encoding: quoted-printable X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.9 tagged_above=-10 required=5 tests=BAYES_00=-1.9, RCVD_IN_DNSWL_NONE=-0.0001 X-Spam-Level: X-Archive-Number: 201011/249 X-Sequence-Number: 41273 2010/11/15 Artur Zaj=B1c : > Why there is so big difference between explain analyze (0.710 ms) and rea= l > execution time (3309 ms)? Any suggestions? Could it be that it takes a long time to plan for some reason? How fast is a plain EXPLAIN? What happens if you start up psql, turn on \timing, and then run EXPLAIN ANALYZE from within an interactive session? That's usually a better way to test, as it avoids counting the session-startup overhead. --=20 Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company From azajac@ang.com.pl Mon Nov 15 20:44:03 2010 Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id 525F91337990 for ; Mon, 15 Nov 2010 16:44:03 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia, port 10024) with ESMTP id 62289-03 for ; Mon, 15 Nov 2010 20:43:56 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from wentylator.ang.com.pl (wentylator.ang.com.pl [89.174.215.202]) by mail.postgresql.org (Postfix) with ESMTP id E42CA1337B51 for ; Mon, 15 Nov 2010 16:43:55 -0400 (AST) Received: from Artur7 (cfidsl [83.18.105.226]) by wentylator.ang.com.pl (Postfix) with ESMTP id 11D922DD260; Mon, 15 Nov 2010 21:43:54 +0100 (CET) From: =?iso-8859-2?Q?Artur_Zaj=B1c?= To: "'Robert Haas'" Cc: References: <000001cb849e$1dce2720$596a7560$@ang.com.pl> In-Reply-To: Subject: Re: Difference between explain analyze and real execution time Date: Mon, 15 Nov 2010 21:43:48 +0100 Message-ID: <009d01cb8505$ce738900$6b5a9b00$@ang.com.pl> MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-2" Content-Transfer-Encoding: quoted-printable X-Mailer: Microsoft Outlook 14.0 Thread-Index: AQFsDnYhYIkerMawLWBzle0OUvs2zwGxik20lCRiAyA= Content-Language: pl X-wentylator-MailScanner-Information: Please contact the ISP for more information X-wentylator-MailScanner: Found to be clean X-wentylator-MailScanner-SpamCheck: not spam, SpamAssassin (not cached, score=0, required 6, autolearn=not spam) X-wentylator-MailScanner-From: azajac@ang.com.pl X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.91 tagged_above=-10 required=5 tests=BAYES_00=-1.9, T_RP_MATCHES_RCVD=-0.01 X-Spam-Level: X-Archive-Number: 201011/250 X-Sequence-Number: 41274 2010/11/15 Artur Zaj=B1c : > Why there is so big difference between explain analyze (0.710 ms) and=20 > real execution time (3309 ms)? Any suggestions? > Could it be that it takes a long time to plan for some reason? How = fast is a plain EXPLAIN? Yes! That is it :) Planning is painful. I'm so stupid. I didn't check = VACUUM without ANALYZE :) time psql -c 'explain SELECT te.idt FROM t_positions AS te WHERE = te.idtr IN (347186)' real 0m1.087s user 0m0.004s sys 0m0.001s I've changed default_statistics_target to 10000 and I think that is a reason. When I changed it to 1000 everything seems to be ok: time psql -c 'explain analyze SELECT te.idt FROM t_positions AS te = WHERE te.idtr IN (347186)' real 0m0.062s user 0m0.003s sys 0m0.004s Thanks. ------------------------------------------- Artur Zajac From tgl@sss.pgh.pa.us Mon Nov 15 21:04:05 2010 Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id 1C0171335B36 for ; Mon, 15 Nov 2010 17:04:05 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia, port 10024) with ESMTP id 00555-03 for ; Mon, 15 Nov 2010 21:03:58 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by mail.postgresql.org (Postfix) with ESMTP id A679B1337B2F for ; Mon, 15 Nov 2010 17:03:57 -0400 (AST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.2/8.14.2) with ESMTP id oAFL3tBq007144; Mon, 15 Nov 2010 16:03:55 -0500 (EST) To: =?iso-8859-2?Q?Artur_Zaj=B1c?= cc: "'Robert Haas'" , pgsql-performance@postgresql.org Subject: Re: Difference between explain analyze and real execution time In-reply-to: <009d01cb8505$ce738900$6b5a9b00$@ang.com.pl> References: <000001cb849e$1dce2720$596a7560$@ang.com.pl> <009d01cb8505$ce738900$6b5a9b00$@ang.com.pl> Comments: In-reply-to =?iso-8859-2?Q?Artur_Zaj=B1c?= message dated "Mon, 15 Nov 2010 21:43:48 +0100" Date: Mon, 15 Nov 2010 16:03:55 -0500 Message-ID: <7142.1289855035@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.91 tagged_above=-10 required=5 tests=BAYES_00=-1.9, T_RP_MATCHES_RCVD=-0.01 X-Spam-Level: X-Archive-Number: 201011/251 X-Sequence-Number: 41275 =?iso-8859-2?Q?Artur_Zaj=B1c?= writes: > I've changed default_statistics_target to 10000 and I think that is a > reason. That's certainly going to cost you something, but this seems like a mighty large slowdown, especially for a non-join query. What datatype is te.idtr, anyway? regards, tom lane From azajac@ang.com.pl Mon Nov 15 21:30:41 2010 Received: from maia.hub.org (maia-3.hub.org [200.46.204.243]) by mail.postgresql.org (Postfix) with ESMTP id 596851337B7E for ; Mon, 15 Nov 2010 17:30:41 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia, port 10024) with ESMTP id 36982-10 for ; Mon, 15 Nov 2010 21:30:34 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from wentylator.ang.com.pl (wentylator.ang.com.pl [89.174.215.202]) by mail.postgresql.org (Postfix) with ESMTP id 14DC91337B2F for ; Mon, 15 Nov 2010 17:30:34 -0400 (AST) Received: from Artur7 (cfidsl [83.18.105.226]) by wentylator.ang.com.pl (Postfix) with ESMTP id 9398F2DD260; Mon, 15 Nov 2010 22:30:38 +0100 (CET) From: =?iso-8859-2?Q?Artur_Zaj=B1c?= To: "'Tom Lane'" Cc: "'Robert Haas'" , References: <000001cb849e$1dce2720$596a7560$@ang.com.pl> <009d01cb8505$ce738900$6b5a9b00$@ang.com.pl> <7142.1289855035@sss.pgh.pa.us> In-Reply-To: <7142.1289855035@sss.pgh.pa.us> Subject: Re: Difference between explain analyze and real execution time Date: Mon, 15 Nov 2010 22:30:32 +0100 Message-ID: <009f01cb850c$55f962e0$01ec28a0$@ang.com.pl> MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-2" Content-Transfer-Encoding: 7bit X-Mailer: Microsoft Outlook 14.0 Thread-Index: AQFsDnYhYIkerMawLWBzle0OUvs2zwGxik20AqW4E5YBY3cWEpQEJciA Content-Language: pl X-wentylator-MailScanner-Information: Please contact the ISP for more information X-wentylator-MailScanner: Found to be clean X-wentylator-MailScanner-SpamCheck: not spam, SpamAssassin (not cached, score=0, required 6, autolearn=not spam) X-wentylator-MailScanner-From: azajac@ang.com.pl X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.91 tagged_above=-10 required=5 tests=BAYES_00=-1.9, T_RP_MATCHES_RCVD=-0.01 X-Spam-Level: X-Archive-Number: 201011/252 X-Sequence-Number: 41276 > I've changed default_statistics_target to 10000 and I think that is a > reason. > That's certainly going to cost you something, but this seems like a mighty large slowdown, especially for a non-join query. What datatype is te.idtr, anyway? Integer not null and primary key of t_positions table. Table t_positions has about 1500000 records, table t_st about 130000 records. ------------------------------------------- Artur Zajac