Received: from magus.postgresql.org (magus.postgresql.org [87.238.57.229]) by mail.postgresql.org (Postfix) with ESMTP id 4F5761288B2F for ; Wed, 1 Feb 2012 15:05:23 -0400 (AST) Received: from nm3.bullet.mail.sp2.yahoo.com ([98.139.91.73]) by magus.postgresql.org with smtp (Exim 4.72) (envelope-from ) id 1RsfUd-0007zM-ST for pgsql-performance@postgresql.org; Wed, 01 Feb 2012 19:05:22 +0000 Received: from [98.139.91.63] by nm3.bullet.mail.sp2.yahoo.com with NNFMP; 01 Feb 2012 19:05:05 -0000 Received: from [98.139.91.55] by tm3.bullet.mail.sp2.yahoo.com with NNFMP; 01 Feb 2012 19:04:05 -0000 Received: from [127.0.0.1] by omp1055.mail.sp2.yahoo.com with NNFMP; 01 Feb 2012 19:04:05 -0000 X-Yahoo-Newman-Property: ymail-3 X-Yahoo-Newman-Id: 347061.94896.bm@omp1055.mail.sp2.yahoo.com Received: (qmail 50376 invoked by uid 60001); 1 Feb 2012 19:04:04 -0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s1024; t=1328123044; bh=7i9HzDWM9jl3IAQoTpPfRolB/7+wAvjt7OEOqMPz6lw=; h=X-YMail-OSG:Received:X-Mailer:References:Message-ID:Date:From:Reply-To:Subject:To:In-Reply-To:MIME-Version:Content-Type; b=hCf04S99PYRjDMtEubJ3Hqr6gd15IrBV/9fUldmIM8+r8DS0d+Ztk3BbMa+debDuZp7G+YTR+yiTq+hQomdN4s3AgUagOc4KUi+ys94E28e/U3ZQdCnPCw7g/OP15iFjgUzzF3dsJ9YLDpL4rPDshrx34A9V2p9iDmBnnVEHB/0= 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=C93h2Ibxo09LXxMRUZy3S7TMeI+nxW5UrtefWyOyUABojrOzIhQUAnb6FiJn+5FjONfxEr0OPw0wBhmGL6XgGfL3bGocimgoG/U44A1CJ2wyAcBBDKU5WLGiT3N3gQgyrdNZILzfTxSWK0qmrPMfMER1QKDrxc8q8YEzXIsUx6M=; X-YMail-OSG: D_YdMAkVM1kgDYQ8mMSvesKZ7tCzBt7dHSn9MgSjWdYsaEz WWboDybOMoYHza4h_QgUEkSqzkXIG7uBVLOrcxiA7fQbcrpDcSX9kEC9AAsW W16SojQ4ws50vi8u2aJETfAaegq8ub5X67PzA9tEaKNHU5c4C2giBhKV0sIq pK3fdE6hLlBGabDgj_utkTUcrirmG9uWnOQauiCE4JftUj.aDASIVpz6u_Jv axgcKvF5Pk7t83ZO7O2aDp7x4.oeN7YwLAfBg043vV3bkxHo8JJduA2AXTHD yXkAOmYt4S4G2Hy3nyFmg.GFmjJFIMwWbwbokQR0vi7w3KqMQo30lgXLVI7i QoY2pAovxKbjadaFyOI5cGR07NsnJrNmMzNFBQ20LLY_ZIiejC4whA9HOvOv fTnpaxH0Kx89EM0B11zasxbgQ7.l.d4BNsXAQP4BWdAs4kRgYBB8Nx9RPwdv 1PQOommLN_PO9Qd4z7VbZPW7HpCQs6MKuRdxuD93Ewu7Q_JrjvsfUAxV.gu9 59znU4Ceh_wxYkOmIJgTG2vYjJ8XbB.VJAA-- Received: from [64.126.29.122] by web39707.mail.mud.yahoo.com via HTTP; Wed, 01 Feb 2012 11:04:04 PST X-Mailer: YahooMailWebService/0.8.116.331537 References: Message-ID: <1328123044.31389.YahooMailNeo@web39707.mail.mud.yahoo.com> Date: Wed, 1 Feb 2012 11:04:04 -0800 (PST) From: Bob Lunney Reply-To: Bob Lunney Subject: Re: From Simple to Complex To: Alessandro Gagliardi , "pgsql-performance@postgresql.org" In-Reply-To: MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="2066115703-593146939-1328123044=:31389" X-Pg-Spam-Score: -2.0 (--) X-Archive-Number: 201202/8 X-Sequence-Number: 46180 --2066115703-593146939-1328123044=:31389 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: quoted-printable Possibly. =A0What does=0A=0A=A0 psql > show work_mem;=0A=0Asay?=0A=0ABob Lu= nney=0A=0A=0A________________________________=0A From: Alessandro Gagliardi= =0ATo: pgsql-performance@postgresql.org =0ASent: Wedn= esday, February 1, 2012 12:19 PM=0ASubject: Re: [PERFORM] From Simple to Co= mplex=0A =0A=0AFinal update on this thread: since it is only necessary for = me to get a rough ratio of the distribution (and not the absolute count), I= refactored the query to include a subquery that samples from the moments t= able thus:=A0SELECT moment_id, block_id=A0FROM moments=A0WHERE inserted BET= WEEN 'yesterday' AND 'today'=A0ORDER BY RANDOM() LIMIT 10000; I also took a= dvantage of another table called blocks that happens to contain the moment_= type as well (thus making it so I don't need to reference pg_class). The fi= nal query looks like:=0A=0ASELECT moment_type, emotion, COUNT(feedback_id)= =A0=0A=A0 FROM (SELECT moment_id, block_id=A0=0A=A0 =A0 =A0 =A0 =A0 FROM mo= ments=A0=0A=A0 =A0 =A0 =A0 =A0WHERE inserted BETWEEN 'yesterday' AND 'today= '=A0=0A=A0 =A0 =A0 =A0 =A0ORDER BY RANDOM() LIMIT 10000) AS sample_moments= =0A=A0 JOIN blocks USING (block_id)=0A=A0 JOIN emotions USING (moment_id)= =0A=A0GROUP BY moment_type, emotion=0A=A0ORDER BY moment_type, emotion=0A= =0AThe explain is at=A0http://explain.depesz.com/s/lYh=0A=0AInterestingly, = increasing the limit does not seem to increase the runtime in a linear fash= ion. When I run it with a limit of 60000 I get a runtime of=A014991 ms. But= if I run it with a limit of=A070000 I get a runtime of=A077744 ms. I assum= e that that's because I'm hitting a memory limit and paging out. Is that ri= ght?=0A=0AOn Tue, Jan 31, 2012 at 3:43 PM, Alessandro Gagliardi wrote:=0A=0AI just got a pointer on presenting EXPLAIN ANALYZE i= n a more human friendly fashion (thanks, Agent M!):=A0http://explain.depesz= .com/s/A9S=0A>=0A>=0A>From this it looks like the bottleneck happens when P= ostgres does an=A0Index Scan using emotions_moment_id_idx on emotions=A0bef= ore filtering on moments.inserted so I thought I'd try filtering on=A0emoti= ons.inserted instead but that only made it worse. At the same time,=A0I not= iced that "FROM pg_class, moments=A0WHERE=A0moments.tableoid =3D pg_class.o= id" tends to run a bit faster than "FROM pg_class JOIN moments=A0ON=A0momen= ts.tableoid =3D pg_class.oid". So I tried:=0A>=0A>=0A>SELECT relname, emoti= on, COUNT(feedback_id)=A0=0A>=A0 FROM pg_class, moments, emotions=0A>=A0WHE= RE moments.tableoid =3D pg_class.oid=A0=0A>=A0 =A0AND emotions.inserted > '= yesterday'=A0=0A>=A0 =A0AND moments.inserted BETWEEN 'yesterday' AND 'today= '=A0=0A>=A0 =A0AND emotions.moment_id =3D moments.moment_id=0A>=A0GROUP BY = relname, emotion=A0=0A>=A0ORDER BY relname, emotion;=0A>=0A>=0A>That was a = bit faster, but still very slow. Here's the EXPLAIN:=A0http://explain.depes= z.com/s/ZdF=0A>=0A>=0A>On Tue, Jan 31, 2012 at 2:53 PM, Alessandro Gagliard= i wrote:=0A>=0A>I changed the query a bit so the resu= lts would not change over the=0A>>course of the day to:=0A>>=0A>>=0A>>SELEC= T relname, emotion, COUNT(feedback_id) FROM pg_class, moments=0A>>JOIN emot= ions USING (moment_id)=0A>>WHERE moments.inserted BETWEEN 'yesterday' AND '= today' AND=0A>>=0A>>moments.tableoid =3D pg_class.oid=0A>>GROUP BY relname,= emotion ORDER BY relname, emotion;=0A>> --2066115703-593146939-1328123044=:31389 Content-Type: text/html; charset=iso-8859-1 Content-Transfer-Encoding: quoted-printable
Possibly.  What = does

  psql > sh= ow work_mem;

say?=

Bob Lunney
From:= Alessandro Gagliardi <alessandro@path.com>
To: pgsql-performance@postgresql.org
Sent: Wednesday, February 1, 20= 12 12:19 PM
Subject: R= e: [PERFORM] From Simple to Complex

=0A
Final update o= n this thread: since it is only necessary for me to get a rough ratio of th= e distribution (and not the absolute count), I refactored the query to incl= ude a subquery that samples from the moments table thus: SELECT moment= _id, block_id FROM moments WHERE inserted BETWEEN 'yesterday' AND= 'today' ORDER BY RANDOM() LIMIT 10000; I also took advantage of anoth= er table called blocks that happens to contain the moment_type as well (thu= s making it so I don't need to reference pg_class). The final query looks l= ike:
=0A
SELECT moment_type, emotion, COUNT(fee= dback_id) 
  FROM (SELECT moment_id, block_id 
          FROM moments 
&nbs= p;        WHERE inserted BETWEEN 'yesterday' AND 'today= ' 
=0A
         ORDER BY RANDOM() LI= MIT 10000) AS sample_moments
  JOIN blocks USING (block_id)<= /div>
  JOIN emotions USING (moment_id)
 GROUP BY m= oment_type, emotion
 ORDER BY moment_type, emotion
=0A<= /div>

The explain is at http://explain.depesz= .com/s/lYh

Interestingly, increasing the limit doe= s not seem to increase the runtime in a linear fashion. When I run it with = a limit of 60000 I get a runtime of 14991 ms. But if I run it with a l= imit of 70000 I get a runtime of 77744 ms. I assume that that's b= ecause I'm hitting a memory limit and paging out. Is that right?
=0A= =0A=0A=0A=0A=0A=0A=0A=0A=0A=0A=0A=0A=0A=0A=0A=0A

On Tue, Jan 31, 2012 at 3:43 PM, Alessandr= o Gagliardi <ale= ssandro@path.com> wrote:
=0AI just got a pointer on presenting EXPLAIN ANALYZE in a more= human friendly fashion (thanks, Agent M!): http://explain.depesz.com/= s/A9S

From this it looks like the bottleneck happens whe= n Postgres does an Index Scan using emotions_moment_id_idx on emotions=  before filtering on moments.inserted so I thought I'd try filtering o= n emotions.inserted instead but that only made it worse. At the same t= ime, I noticed that "FROM pg_class, moments WHERE moments.ta= bleoid =3D pg_class.oid" tends to run a bit faster than "FROM pg_class JOIN= moments ON moments.tableoid =3D pg_class.oid". So I tried:
= =0A=0A

SELECT relname, emotion, COUNT(feedback_id)&= nbsp;
  FROM pg_class, moments, emotions
 WHE= RE moments.tableoid =3D pg_class.oid 
   AND emoti= ons.inserted > 'yesterday' 
=0A=0A
   AND moment= s.inserted BETWEEN 'yesterday' AND 'today' 
   AND= emotions.moment_id =3D moments.moment_id
 GROUP BY relname, emotion 
 ORDER BY reln= ame, emotion;
=0A
=0A

That was a bit fas= ter, but still very slow. Here's the EXPLAIN: http://explain.depesz.co= m/s/ZdF
=0A

On Tue, Jan 31, 201= 2 at 2:53 PM, Alessandro Gagliardi <alessandro@path.com> wrote:
=0A=0AI changed the query a bit so the r= esults would not change over the
=0Acourse of the day to:
=0A
=0ASELECT relname, emotion, COUNT(feedback_id) FROM pg_class, moments
= =0AJOIN emotions USING (moment_id)
=0A
WHERE moments.inserted BETWE= EN 'yesterday' AND 'today' AND
=0A
moments.tableoid =3D pg_class.oid=
=0AGROUP BY relname, emotion ORDER BY relname, emotion;
=0A
=0A

=0A
=

=
--2066115703-593146939-1328123044=:31389--