Received: from magus.postgresql.org (magus.postgresql.org [87.238.57.229]) by mail.postgresql.org (Postfix) with ESMTP id 43E4B1314BFD for ; Fri, 3 Aug 2012 22:31:17 -0300 (ADT) Received: from nm36-vm5.bullet.mail.ne1.yahoo.com ([98.138.229.117]) by magus.postgresql.org with smtp (Exim 4.72) (envelope-from ) id 1SxTCx-0003MN-Ha for pgsql-performance@postgresql.org; Sat, 04 Aug 2012 01:31:15 +0000 Received: from [98.138.90.54] by nm36.bullet.mail.ne1.yahoo.com with NNFMP; 04 Aug 2012 01:30:57 -0000 Received: from [98.138.87.2] by tm7.bullet.mail.ne1.yahoo.com with NNFMP; 04 Aug 2012 01:30:57 -0000 Received: from [127.0.0.1] by omp1002.mail.ne1.yahoo.com with NNFMP; 04 Aug 2012 01:30:57 -0000 X-Yahoo-Newman-Property: ymail-3 X-Yahoo-Newman-Id: 212596.32289.bm@omp1002.mail.ne1.yahoo.com Received: (qmail 61191 invoked by uid 60001); 4 Aug 2012 01:30:57 -0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s1024; t=1344043857; bh=lYKavnX/UqYPxM6CSA7ciy5W5EZzMEHwaH6CdMX0x6g=; h=X-YMail-OSG:Received:X-Mailer:References:Message-ID:Date:From:Reply-To:Subject:To:In-Reply-To:MIME-Version:Content-Type; b=bO/vt9FiVs60ZzJSb40XvVMfeqdimUWHJqcB4QAJMNQkrs8/ygICecAtGVS7cUXUaacsIw2G2EVE7YX6mieOlVTRaItbPfVLm95jhg4JKW87ItcOmTAc0oOApkpAXzRb7m/yExZe7NCHS5oEo+qvbGkcuohjRUR+V7k56q5Q2vY= DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com; h=X-YMail-OSG:Received:X-Mailer:References:Message-ID:Date:From:Reply-To:Subject:To:In-Reply-To:MIME-Version:Content-Type; b=Woj6PSHkHwRpNOXR7A6LenjviAaCEHXGEFrv7lp3zPtgmC9Df9iZf3cSBprqRJ/v3GqD67bXg1Ihx99esfDKDq2TCUIPelDOxc8NCzFz5oJg+sP7C7vhMhl7jnS07dK9WTTsfm+t+V2p9WNFHvgqjdzlsTNxhtdY37nxlUvaMwA=; X-YMail-OSG: n.gUWaQVM1lgjO9kJdGh1PIdOgzTRuRTrE6dYij5GU7ric8 tNQpl6oGmuhE7mYHaVdFlDBqU8EqWLs1c7PUC6OdDN.op_lDH3_0HKGhDyuZ o6_iogcnQs0v_K0IYeFoRQZDdmpS0YEnGQyt6.fbuyi6xzZbQTVg7UoPpZdH 5fXykgEYudMTuUEvSVpVocGAAYP0yfjx_HH_iLYCLj_LsOY_rY433XbTArgf 65e09hVPhgz4lz3FvJeVoidWP1T5JQr2Vhs9LJ8VaSCqxCC_pAcXcv3hDogW EI1YlTG9hvAyJ7FQJze5pheRfUGFShRavKD.KC6h4kCH3cPnXZh6gApmb4So tjxtygd_neVjhq8pf_cyO8wzzdBPnAjq6bE62wX1rRwaH85aQ.WCRPLeUl8s pNivDXuCzWgvhRNJA3LksDWFO._pPUVbVkaonoxK3Xsrr8T1jigR79FBbA27 eel2ODvaCJ60_arptWQfB8mwAg7P4D7zwDe.AYCQ5sh7jGa61fvKWArpl4LY CSxgh16RNYnHcT6miIpWwUSPxTm2U Received: from [199.9.251.45] by web125905.mail.ne1.yahoo.com via HTTP; Fri, 03 Aug 2012 18:30:56 PDT X-Mailer: YahooMailWebService/0.8.120.356233 References: <9CF5A680782142D794A085320949FED4@BERNICE> Message-ID: <1344043856.44633.YahooMailNeo@web125905.mail.ne1.yahoo.com> Date: Fri, 3 Aug 2012 18:30:56 -0700 (PDT) From: Greg Williamson Reply-To: Greg Williamson Subject: Re: slow query, different plans To: "pgsql-performance@postgresql.org" In-Reply-To: <9CF5A680782142D794A085320949FED4@BERNICE> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="-2119685684-1687611081-1344043856=:44633" X-Pg-Spam-Score: -1.5 (-) X-Archive-Number: 201208/18 X-Sequence-Number: 47556 ---2119685684-1687611081-1344043856=:44633 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: quoted-printable Midge -- Sorry for top-quoting -- challenged mail. Perhaps a difference in the stats estimates --=A0default_statistics_target ? Can you show us a diff between the postgres config files for each instance = ? Maybe something there ... Greg Williamson >________________________________ > From: Midge Brown >To: pgsql-performance@postgresql.org=20 >Sent: Friday, August 3, 2012 5:38 PM >Subject: [PERFORM] slow query, different plans >=20 > >=20 >I'm having a=20 problem with a query on our production server, but not on a laptop running = a=20 similar postgres version=A0with a recent backup copy of the same table. I t= ried reindexing the table on the production=20 server, but it didn't make any difference. Other queries on the same table = are=20 plenty fast.=20 >=A0 >This query has been slow, but never like this,=20 particularly during a period when there are only a couple of connections in= use.=20 >=A0 >Vacuum and analyze are run nightly (and=20 show=A0as such in pg_stat_user_tables)=A0in addition to autovacuum during= =20 the day. Here are my autovacuum=A0settings,=A0but when I checked=20 last_autovacuum &=A0last_autoanalyze in pg_stat_user_tables those fields=20 were blank.=20 >=A0 >autovacuum =3D=20 on=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =20 >log_autovacuum_min_duration =3D 10=A0=A0=A0=A0=A0=A0=A0=20 >autovacuum_max_workers =3D=20 3=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=20 >autovacuum_naptime =3D=20 1min=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=20 >autovacuum_vacuum_threshold =3D 50=A0=A0=A0=A0=A0=A0=A0=20 >autovacuum_analyze_threshold =3D 50=A0=A0=A0=A0=A0=A0=20 >autovacuum_vacuum_scale_factor =3D 0.2=A0=A0=A0=20 >autovacuum_analyze_scale_factor =3D 0.1=A0=A0=20 >autovacuum_freeze_max_age =3D 200000000=A0=A0=20 >autovacuum_vacuum_cost_delay =3D 10ms=A0(changed earlier today from=20 1000ms)=A0=20 >autovacuum_vacuum_cost_limit =3D -1 >=A0 >wal_level =3D minimal >wal_buffers =3D 16MB >=A0 >The only recent change was moving the 3 databases=20 we have from=A0multiple raid 1 drives with tablespaces spread all over to o= ne=20 large raid10 with indexes and data in pg_default. WAL for this table was mo= ved=20 as well. >=A0 >Does anyone have any suggestions on where to look=20 for the problem?=A0=20 >=A0 >clientlog table info: >=A0 >Size: 1.94G >=A0 >=A0 Column=A0=20 |=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=20 Type=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 |=20 Modifiers=20 >----------+-----------------------------+----------- >=A0pid0=A0=A0=A0=A0=20 |=20 integer=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=20 | not null >=A0rid=A0=A0=A0=A0=A0 |=20 integer=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=20 | not null >=A0verb=A0=A0=A0=A0 | character=20 varying(32)=A0=A0=A0=A0=A0=A0 | not=20 null >=A0noun=A0=A0=A0=A0 | character=20 varying(32)=A0=A0=A0=A0=A0=A0 | not=20 null >=A0detail=A0=A0 |=20 text=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =20 |=20 >=A0path=A0=A0=A0=A0 | character=20 varying(256)=A0=A0=A0=A0=A0 | not=20 null >=A0ts=A0=A0=A0=A0=A0=A0 | timestamp without time=20 zone |=20 >=A0applies2 |=20 integer=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=20 |=20 >=A0toname=A0=A0 | character=20 varying(128)=A0=A0=A0=A0=A0 |=20 >=A0byname=A0=A0 |=20 character varying(128)=A0=A0=A0=A0=A0 |=20 >Indexes: >=A0=A0=A0 "clientlog_applies2" btree=20 (applies2) >=A0=A0=A0 "clientlog_pid0_key" btree=20 (pid0) >=A0=A0=A0 "clientlog_rid_key" btree=20 (rid) >=A0=A0=A0 "clientlog_ts" btree (ts) > >The query, hardware info,=A0and links to both=20 plans: >=A0 >explain analyze select max(ts) as ts=A0from=20 clientlog where applies2=3D256; >=A0 >Production server: >- 4 dual-core AMD Opteron 2212 processors,=20 2010.485 MHz >- 64GB RAM >- 464GB RAID10 drive=20 >- Linux 2.6.18-164.el5 #1=20 SMP Thu Sep 3 03:28:30 EDT 2009 x86_64 x86_64 x86_64 GNU/Linux >=A0PostgreSQL 9.0.4 on=20 x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC) 4.1.2 20080704 (Red Hat= =20 4.1.2-46), 64-bit > >http://explain.depesz.com/s/8R4 >=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=A0=A0=A0 >=A0 From laptop running Linux 2.6.34.9-69.fc13.868=20 with 3G ram against a copy of the same table: >PostgreSQL 9.0.2 on i686-pc-linux-gnu, compiled=20 by GCC gcc (GCC) 4.4.4 20100630 (Red Hat 4.4.4-10), 32-bit >=A0 >http://explain.depesz.com/s/NQl >=A0 >Thank you, >Midge >=A0 > >= ---2119685684-1687611081-1344043856=:44633 Content-Type: text/html; charset=iso-8859-1 Content-Transfer-Encoding: quoted-printable
Midge --

Sorry for top-quoting -- challenged mail.

P= erhaps a difference in the stats estimates -- default_statistics_targe= t ?

Can you show us a diff between the postgres co= nfig files for each instance ? Maybe something there ...

Greg Williamson

=
From: Mid= ge Brown <midgems@sbcglobal.net>
To: pgsql-performance@postgresql.org
Sent: Friday, August 3, 2012 5:38 PM
= Subject: [PERFORM] slow q= uery, different plans

=20 =20
I'm having a=20 problem with a query on our production server, but not on a laptop running = a=20 similar postgres version with a recent backup copy of the same table.= =20 I tried reindexing the table on th= e production=20 server, but it didn't make any difference. Other queries on the same table = are=20 plenty fast.
 
This query has been slow, but never = like this,=20 particularly during a period when there are only a couple of connections in= use.=20
 
Vacuum and analyze are run nightly (= and=20 show as such in pg_stat_user_tables) in addition to autovacuum du= ring=20 the day. Here are my autovacuum settings, but when I checked=20 last_autovacuum & last_autoanalyze in pg_stat_user_tables those fi= elds=20 were blank.
 
autovacuum =3D=20 on            &= nbsp;           =20
log_autovacuum_min_duration =3D 10      &= nbsp;=20
autovacuum_max_workers =3D=20 3            &n= bsp;=20
autovacuum_naptime =3D=20 1min            = ;  =20
autovacuum_vacuum_threshold =3D 50      &= nbsp;=20
autovacuum_analyze_threshold =3D 50      = =20
autovacuum_vacuum_scale_factor =3D 0.2   =20
autovacuum_analyze_scale_factor =3D 0.1  =20
autovacuum_freeze_max_age =3D 200000000  =20
autovacuum_vacuum_cost_delay =3D 10ms (changed earlier today from= =20 1000ms) 
autovacuum_vacuum_cost_limit =3D -1
 
wal_level =3D minimal
wal_buffers =3D 16MB
 
The only recent change was moving th= e 3 databases=20 we have from multiple raid 1 drives with tablespaces spread all over t= o one=20 large raid10 with indexes and data in pg_default. WAL for this table was mo= ved=20 as well.
 
Does anyone have any suggestions on = where to look=20 for the problem? 
 
clientlog table info:
 
Size: 1.94G
 
  Column =20 |           =20 Type            = ; |=20 Modifiers=20
----------+-----------------------------+-----------
 pid0 = ;   =20 |=20 integer           &n= bsp;        =20 | not null
 rid      |=20 integer           &n= bsp;        =20 | not null
 verb     | character=20 varying(32)       | not=20 null
 noun     | character=20 varying(32)       | not=20 null
 detail   |=20 text            = ;           =20 |
 path     | character=20 varying(256)      | not=20 null
 ts       | timestamp without ti= me=20 zone |
 applies2 |=20 integer           &n= bsp;        =20 |
 toname   | character=20 varying(128)      |
 byname   |= =20 character varying(128)      |=20
Indexes:
    "clientlog_applies2" btree=20 (applies2)
    "clientlog_pid0_key" btree=20 (pid0)
    "clientlog_rid_key" btree=20 (rid)
    "clientlog_ts" btree (ts)
The query, hardware info, and l= inks to both=20 plans:
 
explain analyze select max(ts) as ts=  from=20 clientlog where applies2=3D256;
 
Production server:
- 4 dual-core AMD Opteron 2212 proce= ssors,=20 2010.485 MHz
- 64GB RAM
- 464GB RAID10 drive
- Linux 2.6.18-164.e= l5 #1=20 SMP Thu Sep 3 03:28:30 EDT 2009 x86_64 x86_64 x86_64 GNU/Linux
 PostgreSQL 9.0.4 on=20 x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC) 4.1.2 20080704 (Red Hat= =20 4.1.2-46), 64-bit
http://explain.depesz.com/s/8= R4
           &nb= sp;            =             &nb= sp;            =             &nb= sp;           
 
From laptop running Linux 2.6.34.9-6= 9.fc13.868=20 with 3G ram against a copy of the same table:
PostgreSQL 9.0.2 on i686-pc-linux-gn= u, compiled=20 by GCC gcc (GCC) 4.4.4 20100630 (Red Hat 4.4.4-10), 32-bit
 
http://explain.depesz.com/s/NQl
 
Thank you,
Midge
 


= ---2119685684-1687611081-1344043856=:44633--