From soporte@teksol.com.ar Mon Oct 3 17:49:14 2011 Received: from maia.hub.org (maia-5.hub.org [200.46.204.29]) by mail.postgresql.org (Postfix) with ESMTP id 3AD27B5DC1A for ; Mon, 3 Oct 2011 14:49:14 -0300 (ADT) 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 82347-07-2 for ; Mon, 3 Oct 2011 17:49:08 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.8.0-rc2 Received: from mail-yw0-f46.google.com (mail-yw0-f46.google.com [209.85.213.46]) by mail.postgresql.org (Postfix) with ESMTP id B9304B5DC0C for ; Mon, 3 Oct 2011 14:49:07 -0300 (ADT) Received: by ywb5 with SMTP id 5so3659949ywb.19 for ; Mon, 03 Oct 2011 10:49:07 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=sender:from:to:subject:date:organization:message-id:mime-version :content-type:x-mailer:thread-index:content-language; bh=8tPH4DlJKbBbAJS8kFC5UmuRgsmwhzolWN5majyMCkY=; b=VGbcmyCzk63zosaVOqog1LrMJkBr1Yy23Oe/0uP/zrVoYEzDRUVoXQYF+AN82YIgiX fhb+nLXHaLMv7Ht5THY8+Lkl0rQwhFWyPw9oJzAO/gijEchsMvm8WKJWn76FE3nq47ZY 9rNAwJ9FC+BpKIYaCa6he09Y0yMXwa+fCz4FA= Received: by 10.100.174.10 with SMTP id w10mr206903ane.55.1317664147412; Mon, 03 Oct 2011 10:49:07 -0700 (PDT) Received: from CristianTK ([190.104.157.91]) by mx.google.com with ESMTPS id r16sm18054852ani.9.2011.10.03.10.48.32 (version=SSLv3 cipher=OTHER); Mon, 03 Oct 2011 10:48:34 -0700 (PDT) From: "Soporte @ TEKSOL S.A." To: Subject: pkey is not used on productive database Date: Mon, 3 Oct 2011 14:48:10 -0300 Organization: TEKSOL S.A. Message-ID: <014c01cc81f4$a774e140$f65ea3c0$@teksol.com.ar> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_014D_01CC81DB.822893A0" X-Mailer: Microsoft Outlook 14.0 Thread-Index: AcyB9J1/CUQwdiyrQ9if7ybNpyZcHw== Content-Language: es-ar X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.757 tagged_above=-10 required=5 tests=BAYES_00=-1.9, FREEMAIL_FROM=0.001, HTML_MESSAGE=0.001, RCVD_IN_SBL=0.141 X-Spam-Level: X-Archive-Number: 201110/38 X-Sequence-Number: 45260 This is a multipart message in MIME format. ------=_NextPart_000_014D_01CC81DB.822893A0 Content-Type: text/plain; charset="US-ASCII" Content-Transfer-Encoding: 7bit Hi, I need help to understand the issue on a productive database for a select that takes more time than expected. 1- On a development database I ran the query (select) and I can see on Explain Analyze pgAdmin use all the indexes and primary keys defined. Dev db has almost 10% of productive data. 2- On productive database the same query on Explain Analyze from pgAdmin shows a diferent planning, not using a pkey index and instead uses the progressive scan on a million of rows. I can see Primary key is defined for the table on pgAdmin. What could be the issue on a productive for not use the pkey index for the SELECT? Cristian C. Bittel ------=_NextPart_000_014D_01CC81DB.822893A0 Content-Type: text/html; charset="US-ASCII" Content-Transfer-Encoding: quoted-printable

Hi, =

I need help to understand the issue = on a productive database for a select that takes more time than = expected.

1- = On a development = database I ran the query (select) and I can see on Explain Analyze = pgAdmin use all the indexes and primary keys defined. Dev db has almost = 10% of productive data.

2- = On productive database = the same query on Explain Analyze from pgAdmin shows a diferent = planning, not using a pkey index and instead uses the progressive scan = on a million of rows. I can see Primary key is defined for the table on = pgAdmin.

What could be the issue on a productive for not use the = pkey index for the SELECT?

Cristian C. Bittel

------=_NextPart_000_014D_01CC81DB.822893A0-- From depesz@depesz.com Tue Oct 4 13:27:38 2011 Received: from maia.hub.org (maia-5.hub.org [200.46.204.29]) by mail.postgresql.org (Postfix) with ESMTP id 4CA6EB5F4DB for ; Tue, 4 Oct 2011 10:27:38 -0300 (ADT) 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 15088-02 for ; Tue, 4 Oct 2011 13:27:31 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.8.0-rc2 Received: from depesz.com (andy.depesz.com [88.198.47.100]) by mail.postgresql.org (Postfix) with ESMTP id 876FDB5DF16 for ; Tue, 4 Oct 2011 09:55:38 -0300 (ADT) Received: from andy.depesz.com ([88.198.47.100] helo=depesz.com) by depesz.com with esmtpa (Exim 4.76) (envelope-from ) id 1RB4X3-0006OG-3X; Tue, 04 Oct 2011 14:55:37 +0200 Date: Tue, 4 Oct 2011 14:55:36 +0200 From: hubert depesz lubaczewski To: "Soporte @ TEKSOL S.A." Cc: pgsql-performance@postgresql.org Subject: Re: pkey is not used on productive database Message-ID: <20111004125536.GA21733@depesz.com> Reply-To: depesz@depesz.com References: <014c01cc81f4$a774e140$f65ea3c0$@teksol.com.ar> MIME-Version: 1.0 Content-Type: text/plain; charset=utf-8 Content-Disposition: inline In-Reply-To: <014c01cc81f4$a774e140$f65ea3c0$@teksol.com.ar> User-Agent: Mutt/1.5.21 (2010-09-15) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-2.404 tagged_above=-10 required=5 tests=BAYES_00=-1.9, RP_MATCHES_RCVD=-0.504 X-Spam-Level: X-Archive-Number: 201110/40 X-Sequence-Number: 45262 On Mon, Oct 03, 2011 at 02:48:10PM -0300, Soporte @ TEKSOL S.A. wrote: > Hi, > > > > I need help to understand the issue on a productive database for a select > that takes more time than expected. > > > > 1- On a development database I ran the query (select) and I can see on > Explain Analyze pgAdmin use all the indexes and primary keys defined. Dev db > has almost 10% of productive data. > > 2- On productive database the same query on Explain Analyze from > pgAdmin shows a diferent planning, not using a pkey index and instead uses > the progressive scan on a million of rows. I can see Primary key is defined > for the table on pgAdmin. > > > > What could be the issue on a productive for not use the pkey index for the > SELECT? general answer: http://www.depesz.com/index.php/2010/09/09/why-is-my-index-not-being-used/ but for more details, we'd need to see the query and \d of the table. Best regards, depesz -- The best thing about modern society is how easy it is to avoid contact with it. http://depesz.com/ From scott.marlowe@gmail.com Tue Oct 4 13:28:26 2011 Received: from maia.hub.org (maia-3.hub.org [200.46.204.243]) by mail.postgresql.org (Postfix) with ESMTP id EA584B5DEFE for ; Tue, 4 Oct 2011 10:28:26 -0300 (ADT) 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 65995-01-4 for ; Tue, 4 Oct 2011 13:28:19 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.8.0-rc2 Received: from mail-bw0-f46.google.com (mail-bw0-f46.google.com [209.85.214.46]) by mail.postgresql.org (Postfix) with ESMTP id BBDB6B5DC3E for ; Tue, 4 Oct 2011 10:01:34 -0300 (ADT) Received: by bkbzt4 with SMTP id zt4so691617bkb.19 for ; Tue, 04 Oct 2011 06:01:34 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=mime-version:in-reply-to:references:date:message-id:subject:from:to :cc:content-type:content-transfer-encoding; bh=zyysCdpqgOOyHLQ6WDOjN2ghr70Yn0a2g0N+nBC8Y4g=; b=HBq62idOzZV9ESQk1iQWZjPc++LBWV6KKDFI228Z6I70l298lXv2fBPa4jLM55xlPE Z1MqCrYcTydTJbWS81NFRNcbad55rwPv/6js04yPhLhL2k5i1wgX3lnaDsJcXuimvdRK ALbKFHtvbpz6eijvoMXi8CVezgskJ21vJ+knk= MIME-Version: 1.0 Received: by 10.204.137.80 with SMTP id v16mr645355bkt.150.1317733294156; Tue, 04 Oct 2011 06:01:34 -0700 (PDT) Received: by 10.205.82.195 with HTTP; Tue, 4 Oct 2011 06:01:34 -0700 (PDT) In-Reply-To: <014c01cc81f4$a774e140$f65ea3c0$@teksol.com.ar> References: <014c01cc81f4$a774e140$f65ea3c0$@teksol.com.ar> Date: Tue, 4 Oct 2011 07:01:34 -0600 Message-ID: Subject: Re: pkey is not used on productive database From: Scott Marlowe To: "Soporte @ TEKSOL S.A." Cc: pgsql-performance@postgresql.org Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-0.552 tagged_above=-10 required=5 tests=BAYES_00=-1.9, FREEMAIL_FROM=0.001, RCVD_IN_BL_SPAMCOP_NET=1.347 X-Spam-Level: X-Archive-Number: 201110/41 X-Sequence-Number: 45263 On Mon, Oct 3, 2011 at 11:48 AM, Soporte @ TEKSOL S.A. wrote: > Hi, > > I need help to understand the issue on a productive database for a select > that takes more time than expected. > 1-=A0=A0=A0=A0=A0 On a development database I ran the query (select) and = I can see on > Explain Analyze pgAdmin use all the indexes and primary keys defined. Dev= db > has almost 10% of productive data. > > 2-=A0=A0=A0=A0=A0 On productive database the same query on Explain Analyz= e from > pgAdmin shows a diferent planning, not using a pkey index and instead use= s > the progressive scan on a million of rows. I can see Primary key is defin= ed > for the table on pgAdmin. > > What could be the issue on a productive for not use the pkey index for th= e > SELECT? Please post the schema, query and explain analyze output of the runs on both machines.