Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1dcfRG-0000dX-5L for pgsql-admin@arkaria.postgresql.org; Tue, 01 Aug 2017 22:14:54 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1dcfRF-00010U-6D for pgsql-admin@arkaria.postgresql.org; Tue, 01 Aug 2017 22:14:53 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1dcfRD-0000xv-KO; Tue, 01 Aug 2017 22:14:51 +0000 Received: from mail-oi0-x241.google.com ([2607:f8b0:4003:c06::241]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.84_2) (envelope-from ) id 1dcfRA-0001QU-8V; Tue, 01 Aug 2017 22:14:50 +0000 Received: by mail-oi0-x241.google.com with SMTP id b130so3878051oii.3; Tue, 01 Aug 2017 15:14:48 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20161025; h=mime-version:in-reply-to:references:from:date:message-id:subject:to :cc; bh=T4XqKakcYfimy45CZ5iHgDUIliV5TQS/2sAFdhtgOvI=; b=Fogu0is6PV5Ss/OTGhk4dcWnE/w2Xf/Utiec1cDm0LfH9YacIddQ3GwD46wU181Vuy O+SiyjpKA+zpaba/ytPRK4UfDW4UNw5vlveeORa3Quzweo8Mz5ky0tLz2/u0p+OoVVw3 szKAvHbEW7KG7VFvVU5phpNfQrQ2kjBJJCW8mze2pLwUn7PvNJgBYdQRou2XYFaGKZBc 4FpFXSEjsgPqiuPYqdHqcnCcd1xlnJEbBkGrELFrAW8R2GXy8qfds9O7k4MPcb2Dv7Vl MqIg2DOCSv2itMwIlVy1FB2jj+i7eyOH4qRxav1OiW22kt1sGfMCZflrcyqFoP7P0L2k Wj+g== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:mime-version:in-reply-to:references:from:date :message-id:subject:to:cc; bh=T4XqKakcYfimy45CZ5iHgDUIliV5TQS/2sAFdhtgOvI=; b=DjpCyKLgxtbIWZeFHnwUocOmBedaBCh0Hvu2+WpSFQsEN2kXxuakNt/V87GlKF7v90 wsZ2Qo31QfqgvC0or4NT1t66bGFx3qtv6wbdLoX7CvfQZUViDo7SOA07MIgoDmiXaAdY Cip5aehWVeXWEgp8eV1+IZul51l6dY0X2LkrIRDjAHEONwzTl9R11VD1ADOO+aCanHh+ 93ZCgx3oiB75+AY26NEo7AwUX2zucdL44x4wyj74zntm6lgUyPcWRLYOBYgkEdwtf/v9 +o8/vEfx3COPEl6LpMdt36r/ZHYng+ccevo5hlslXFke0Mm8R9w9PDHgX6HafNxMD35+ w/3g== X-Gm-Message-State: AIVw112D1uDorQ8qbwlIpxhlDqdMWVe5LAEpVAJ+zIYABMvC97ln0LQa 0f33qjvQUI6+KiLSU4xGMPm7kqdXdA== X-Received: by 10.202.179.85 with SMTP id c82mr8206979oif.5.1501625687368; Tue, 01 Aug 2017 15:14:47 -0700 (PDT) MIME-Version: 1.0 Received: by 10.74.172.3 with HTTP; Tue, 1 Aug 2017 15:14:46 -0700 (PDT) In-Reply-To: References: From: Naveen Kumar Date: Wed, 2 Aug 2017 03:44:46 +0530 Message-ID: Subject: Re: [PERFORM] 2 server with same configuration but huge difference in performance To: Sumeet Shukla Cc: pgsql-admin , "pgsql-performance@postgresql.org" , Scott Marlowe Content-Type: multipart/alternative; boundary="001a113cdffeae6de40555b87907" 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 --001a113cdffeae6de40555b87907 Content-Type: text/plain; charset="UTF-8" Hello Sumeet, Compare schema on both database to make sure there is no mismatches. And verify LOCKs. if all looks good, do analyze on newly created database before start execution. This will help you. New database doesn't have any stats for generate execution plan. Thanks & Regards, Naveen Kumar .M, Sr. PostgreSQL Database Administrator, Mobile: 7755929449. *My attitude will always be based on how you treat me. * On Tue, Aug 1, 2017 at 9:43 PM, Scott Marlowe wrote: > On Tue, Aug 1, 2017 at 8:45 AM, Sumeet Shukla > wrote: > > It seems that it is happening because of the way the database is > created. On > > an old database it runs perfectly fine or if I use the old DB as > template to > > create the new one, it runs fine. But if I create a new DB with same > > settings and permissions it hangs. I'm now trying to find the difference > > between these 2 databases. > > Likely a difference in encoding or collation. What does \l show you > (that's a lower case L btw) > > smarlowe=> \l > List of databases > Name | Owner | Encoding | Collate | Ctype | > Access privileges > -----------+----------+----------+-------------+------------ > -+----------------------- > postgres | smarlowe | UTF8 | en_US.UTF-8 | en_US.UTF-8 | > smarlowe | smarlowe | UTF8 | en_US.UTF-8 | en_US.UTF-8 | > > UTF8 and en_US are much more expensive than SQL_ASCII and C would be > for text and such. Basically indexes either don't work or work as well > under en_US if you're comparing or sorting text. > > > -- > Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-admin > --001a113cdffeae6de40555b87907 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Hello Sumeet,

Compare schema on both da= tabase to make sure there is no mismatches. And verify LOCKs. if all looks = good,
do analyze on newly created database before start execution= . This will help you. New database doesn't have any stats for generate = execution plan.=C2=A0

=C2=A0

=
Thanks & = Regards,
Nav= een Kumar .M,
Sr. PostgreSQL Database Administrator,
Mobile: 7755929449.
M= y attitude will always be based on how you treat me.=C2=A0


On Tue, Aug 1, 2017 at 9:43 PM, Scott Marlow= e <scott.marlowe@gmail.com> wrote:
On Tue, Aug 1, 2017 at 8:45 AM, Sumeet Sh= ukla <sumeet.k.shukla@gmail= .com> wrote:
> It seems that it is happening because of the way the database is creat= ed. On
> an old database it runs perfectly fine or if I use the old DB as templ= ate to
> create the new one, it=C2=A0 runs fine. But if I create a new DB with = same
> settings and permissions it hangs. I'm now trying to find the diff= erence
> between these 2 databases.

Likely a difference in encoding or collation. What does \l show you<= br> (that's a lower case L btw)

smarlowe=3D> \l
=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 List of databases
=C2=A0 =C2=A0Name=C2=A0 =C2=A0 |=C2=A0 Owner=C2=A0 =C2=A0| Encoding |=C2=A0= =C2=A0Collate=C2=A0 =C2=A0|=C2=A0 =C2=A0 Ctype=C2=A0 =C2=A0 |
Access privileges
-----------+----------+----------+-------------+-------------+---= --------------------
=C2=A0postgres=C2=A0 | smarlowe | UTF8=C2=A0 =C2=A0 =C2=A0| en_US.UTF-8 | e= n_US.UTF-8 |
=C2=A0smarlowe=C2=A0 | smarlowe | UTF8=C2=A0 =C2=A0 =C2=A0| en_US.UTF-8 | e= n_US.UTF-8 |

UTF8 and en_US are much more expensive than SQL_ASCII and C would be
for text and such. Basically indexes either don't work or work as well<= br> under en_US if you're comparing or sorting text.


--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

--001a113cdffeae6de40555b87907--