Received: from makus.postgresql.org (makus.postgresql.org [98.129.198.125]) by mail.postgresql.org (Postfix) with ESMTP id 775A48904BA for ; Mon, 6 Aug 2012 15:37:01 -0300 (ADT) Received: from nm22.access.bullet.mail.mud.yahoo.com ([66.94.237.223]) by makus.postgresql.org with smtp (Exim 4.72) (envelope-from ) id 1SySAm-0004Ul-En for pgsql-performance@postgresql.org; Mon, 06 Aug 2012 18:37:01 +0000 Received: from [66.94.237.196] by nm22.access.bullet.mail.mud.yahoo.com with NNFMP; 06 Aug 2012 18:36:46 -0000 Received: from [98.139.244.50] by tm7.access.bullet.mail.mud.yahoo.com with NNFMP; 06 Aug 2012 18:36:46 -0000 Received: from [127.0.0.1] by smtp112.sbc.mail.bf1.yahoo.com with NNFMP; 06 Aug 2012 18:36:46 -0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=sbcglobal.net; s=s1024; t=1344278206; bh=BHCqUJ8G3A6zDlReMyZ4bJ7Vt9RSl+jSQzIttTmbCAg=; h=X-Yahoo-Newman-Id:X-Yahoo-Newman-Property:X-YMail-OSG:X-Yahoo-SMTP:Received:Message-ID:From:To:Cc:References:Subject:Date:MIME-Version:Content-Type:X-Priority:X-MSMail-Priority:X-Mailer:X-MimeOLE; b=REKoz0GgBi1h6xZn7GZVzvMwKomZYxJuAhtoKcMyZ6ZiNfC+IfqRE/zTWGsBnlcZfYoCusif9n9GUNNKzCjw5lrcaCs0vZSnALXDX3sIvcIjIk80hAmee2b/ya2HhY0lDBVPo1sNnPXJQUpiX+9txOcAmW9mC16qbxWQk+EndBs= X-Yahoo-Newman-Id: 501536.48705.bm@smtp112.sbc.mail.bf1.yahoo.com X-Yahoo-Newman-Property: ymail-3 X-YMail-OSG: fwP59O4VM1lXM2Uoo8LMTJJOICy6ygw.hQZJw6_cmA1UXTN GyXLRtymH4TWKeWBmSrKs0ZN_WLiEWUNF4Q2pEtZ9mKoJNU1P2zbkX09.JW1 TcpoXL79Ss758aOef7I9p8FuVxjuc9CjcQ8v_8aAQJNoiz4ofO6QEeT_rnev lLEYmpB_IaIxmVaY5gpCfTBpPyCcPfy0hpfF1ehBemHSg2PkwYri6evHeM2E PbA8xmGtG3drdx0QwzoMJDT7CDlSCONG8SBrQBmHpAwF993mfJadmx8qZkMz TEZVuRcVB_MC8G8tqDtlkZDoFf6_uTUR7vFegLZ2rgge8umE91srCoqQQNP. NiMP9R04ZqjF0vVj0Ql01nRSWU7x5pVrIKVxZhapvB001JbHAiy_vAPI_TyM 3z42Vc68nIrq77hZWsETmm_wR2zaL8e7y05Eq35TL4qM58npXICWqxyzCqKy z_cplS0n2UZYmjIQUjxl8ArlHASkSUg-- X-Yahoo-SMTP: EtaTb4uswBBSx2FARGS8Y34d19mAO_hWIqN6lfRu55otbg-- Received: from BERNICE (midgems@71.135.44.53 with login) by smtp112.sbc.mail.bf1.yahoo.com with SMTP; 06 Aug 2012 11:36:46 -0700 PDT Message-ID: <6553A486C90148479A9908834FD513DC@BERNICE> From: "Midge Brown" To: "Tom Lane" Cc: References: <9CF5A680782142D794A085320949FED4@BERNICE> <25289.1344061602@sss.pgh.pa.us> Subject: Re: slow query, different plans Date: Mon, 6 Aug 2012 11:36:43 -0700 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0545_01CD73C7.C1055250" X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2900.5931 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.6157 X-Pg-Spam-Score: 0.0 (/) X-Archive-Number: 201208/29 X-Sequence-Number: 47567 This is a multi-part message in MIME format. ------=_NextPart_000_0545_01CD73C7.C1055250 Content-Type: text/plain; charset="Windows-1252" Content-Transfer-Encoding: quoted-printable ----- Original Message -----=20 From: Tom Lane=20 To: Midge Brown=20 Cc: pgsql-performance@postgresql.org=20 Sent: Friday, August 03, 2012 11:26 PM Subject: Re: [PERFORM] slow query, different plans "Midge Brown" writes: > I'm having a problem with a query on our production server, but not on = a laptop running a similar postgres version with a recent backup copy of th= e same table. I tried reindexing the table on the production server, but it= didn't make any difference. Other queries on the same table are plenty fas= t.=20 Reindexing won't help that. The problem is a bad statistical estimate; it thinks there are about 700 rows with applies2 =3D 256, when there's really only one. That means the "fast" plan is a lot faster than the planner gives it credit for, and conversely the "slow" plan is a lot slower than the planner is expecting. Their estimated costs end up nearly the same, which makes it a bit of a chance matter which one is picked --- but the true costs are a lot different. So you need to fix that rowcount estimate. Raising the stats target for the table might help. regards, tom lane --=20 I added "and ts is not null" to the query and the planner came back with = .102 ms. The problem area in production went from a 10 second response to <= 1 second.=20 Thanks for the responses. -Midge= ------=_NextPart_000_0545_01CD73C7.C1055250 Content-Type: text/html; charset="Windows-1252" Content-Transfer-Encoding: quoted-printable
----- Original Message -----
Fro= m:=20 Tom Lane
Cc: pgsql-performance@postgr= esql.org=20
Sent: Friday, August 03, 2012 11:2= 6=20 PM
Subject: Re: [PERFORM] slow query,= =20 different plans

"Midge Brown" <midgems@sbcglobal.net>=20 writes:
> I'm having a problem with a query on our production serve= r,=20 but not on a laptop running a similar postgres version with a recent back= up=20 copy of the same table. I tried reindexing the table on the production se= rver,=20 but it didn't make any difference. Other queries on the same table are pl= enty=20 fast.

Reindexing won't help that.  The problem is a bad=20 statistical estimate;
it thinks there are about 700 rows with applies2= =3D=20 256, when there's
really only one.  That means the "fast" plan is= a=20 lot faster than the
planner gives it credit for, and conversely the "s= low"=20 plan is a lot
slower than the planner is expecting.  Their estima= ted=20 costs end up
nearly the same, which makes it a bit of a chance matter = which=20 one is
picked --- but the true costs are a lot different.  So you= need=20 to fix
that rowcount estimate.  Raising the stats target for the = table=20 might
help.

regards, tom lane

--

I added "and ts is not null" to th= e query=20 and the planner came back with .102 ms. The problem area in production we= nt=20 from a 10 second response to < 1 second.
 
Thanks for the responses.
 
-Midge
------=_NextPart_000_0545_01CD73C7.C1055250--