Received: from maia.hub.org (maia-3.hub.org [200.46.204.243]) by mail.postgresql.org (Postfix) with ESMTP id ECC6913381B6 for ; Wed, 15 Sep 2010 15:43:15 -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 48573-02-6 for ; Wed, 15 Sep 2010 18:43:08 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from fep16.mx.upcmail.net (fep16.mx.upcmail.net [62.179.121.36]) by mail.postgresql.org (Postfix) with ESMTP id 7CF4F13382A8 for ; Wed, 15 Sep 2010 15:40:18 -0300 (ADT) Received: from edge03.upcmail.net ([192.168.13.238]) by viefep16-int.chello.at (InterMail vM.8.01.02.02 201-2260-120-106-20100312) with ESMTP id <20100915184016.UBWY1503.viefep16-int.chello.at@edge03.upcmail.net>; Wed, 15 Sep 2010 20:40:16 +0200 Received: from wiesinger.com ([84.112.167.138]) by edge03.upcmail.net with edge id 76g71f00t2zVakc036g8sC; Wed, 15 Sep 2010 20:40:16 +0200 X-SourceIP: 84.112.167.138 Received: from bbs.intern (localhost [127.0.0.1]) by wiesinger.com (8.14.4/8.14.4) with ESMTP id o8FIdYk8023982; Wed, 15 Sep 2010 20:39:34 +0200 Received: from localhost (gerhard@localhost) by bbs.intern (8.14.4/8.14.4/Submit) with ESMTP id o8FIdXwv023969; Wed, 15 Sep 2010 20:39:33 +0200 Date: Wed, 15 Sep 2010 20:39:33 +0200 (CEST) From: Gerhard Wiesinger To: Merlin Moncure cc: pgsql-performance@postgresql.org, Tom Lane , Pavel Stehule , Andreas Kretschmer Subject: Re: Major performance problem after upgrade from 8.3 to 8.4 In-Reply-To: Message-ID: References: <24994.1283532424@sss.pgh.pa.us> <25560.1283534215@sss.pgh.pa.us> <26620.1283537362@sss.pgh.pa.us> <27253.1283539468@sss.pgh.pa.us> <29799.1283548250@sss.pgh.pa.us> User-Agent: Alpine 2.01 (LFD 1266 2009-07-14) MIME-Version: 1.0 X-MailScanner-Information-wiesinger-com: Please contact the ISP for more information X-wiesinger-com-MailScanner-ID: o8FIdYk8023982 X-MailScanner-wiesinger-com: Found to be clean X-MailScanner-SpamCheck-wiesinger-com: not spam, SpamAssassin (not cached, score=-4.399, required 4.5, autolearn=not spam, ALL_TRUSTED -1.80, BAYES_00 -2.60) X-MailScanner-From: lists@wiesinger.com X-Cloudmark-Analysis: v=1.1 cv=WPXthVDctC5K37chdIuWDfGL2h54X6uWtZ5SeJIYSNg= c=1 sm=0 a=-BBt41PQAQkA:10 a=wPDyFdB5xvgA:10 a=-ProOGmuAAAA:8 a=D6uRaMib2z9QCsxFW4AA:9 a=PLOC4yvISr-pJnHJaLhsS1U0W4oA:4 a=wPNLvfGTeEIA:10 a=fU-A4pz3-wIA:10 a=sOxU1vlw5o1UiGja:21 a=J0VKfyvZJhQ6j0x1:21 a=HpAAvcLHHh0Zw7uRqdWCyQ==:117 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.9 tagged_above=-10 required=5 tests=BAYES_00=-1.9, RCVD_IN_DNSWL_NONE=-0.0001 X-Spam-Level: Content-Type: TEXT/PLAIN; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 8BIT X-Archive-Number: 201009/88 X-Sequence-Number: 40291 On Wed, 15 Sep 2010, Merlin Moncure wrote: > On Wed, Sep 15, 2010 at 2:32 AM, Gerhard Wiesinger wrote: >> On Tue, 14 Sep 2010, Merlin Moncure wrote: >>> >>> np -- this felt particularly satisfying for some reason. btw, I think >>> you have some more low hanging optimization fruit.  I think (although >>> it would certainly have to be tested) hiding your attribute >>> description under keyid is buying you nothing but headaches.  If you >>> used natural key style, making description primary key of >>> key_description (or unique), and had log_details have a description >>> column that directly referenced that column, your subquery: >>> >>> ( >>>  SELECT value FROM log_details d WHERE l.id = d.fk_id AND d.fk_keyid = >>>  ( >>>   SELECT keyid FROM key_description WHERE description = >>> 'Kesselsolltemperatur' >>>  ) >>> ) AS Kesselsolltemperatur, >>> >>> would look like this: >>> ( >>>  SELECT value FROM log_details d WHERE l.id = d.fk_id AND >>> d.description = 'Kesselsolltemperatur' >>> ) AS Kesselsolltemperatur, >>> >>> your index on log_details(fk_id, description) is of course fatter, but >>> quite precise...does require rebuilding your entire dataset however. >>> food for thought. >> >> I think your suggestion might be slower because the WHERE clause and >> possible JOINS with BIGINT is much faster (especially when a lot of data is >> queried) than with a VARCHAR. With the latest query plan key_description is >> only queried once per subselect which is perfect. I've also chosen that >> indirection that I can change description without changing too much in data >> model and all data rows on refactoring. > > You're not joining -- you're filtering (and your assumption that > bigint is always going to be faster is quite debatable depending on > circumstances). The join is skipped because of the key (yes, it's > cheap lookup, but w/50 columns each doing it, nothing is cheap). I know that I'm not JOINing in that case - as discussed I ment possible JOINs in other query scenarios. BTW: Latest query plan is also optimal that only the used columns from the view are evaluated. With the full joined version all columns where used even when dropped in the result-set, e.g.: SELECT col1, col2 FROM view1; -- Equivalent to SELECT * FROM view1; as col1, col2 are all colums in that view SELECT col1 FROM view1; -- less effort with subselects when less columns are needed, joins have same "full view" effort here Ciao, Gerhard -- http://www.wiesinger.com/ From pgsql-performance-owner@postgresql.org Wed Sep 15 15:48:33 2010 Received: from maia.hub.org (maia-5.hub.org [200.46.204.29]) by mail.postgresql.org (Postfix) with ESMTP id 7F05F634580 for ; Mon, 13 Sep 2010 13:32:17 -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 29820-03 for ; Mon, 13 Sep 2010 16:32:09 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from mail-bw0-f46.google.com (mail-bw0-f46.google.com [209.85.214.46]) by mail.postgresql.org (Postfix) with ESMTP id 1253A634253 for ; Mon, 13 Sep 2010 13:32:08 -0300 (ADT) Received: by bwz11 with SMTP id 11so4631169bwz.19 for ; Mon, 13 Sep 2010 09:32:07 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:received:mime-version:received:in-reply-to :references:from:date:message-id:subject:to:cc:content-type; bh=SrL7EJgmKb4ZXmks4pzWfl5U/NaF4iQL9b5uuzAI9zY=; b=MbYVt2cy4SheQa3rY10fGMj2aAUSIM0QM0N+gDp9ubgBdqeIi0osny60Aq6y+9X3Pu 2LqxUkr0JFlvs4EdiqKoropSOzCQnlTmFovGKxeiShFz5xvzeIEaXZmb6D3rSzolNqYT xqwPAfUsSukZ4w9iznPM1qjrX+2eFJjTUtoFA= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=mime-version:in-reply-to:references:from:date:message-id:subject:to :cc:content-type; b=h8xCcEXv5qjPA38c6d7fceJbo1LpCSclqkEpZfI9pMxwEJTBHRCZPKiFTb8E7UTfto ImSZoiaTFZBF3caY23NEQPZh66+GbxF2nCmJW9SYS+jsWiZkZPGgIdBrEke+V/jiepn2 /l5VMbeqC85lFQCswN0ciUiU78radGvPsbvUc= Received: by 10.204.60.145 with SMTP id p17mr3407724bkh.56.1284395527479; Mon, 13 Sep 2010 09:32:07 -0700 (PDT) MIME-Version: 1.0 Received: by 10.204.10.11 with HTTP; Mon, 13 Sep 2010 09:31:47 -0700 (PDT) In-Reply-To: <20100911020337.GH26232@tamriel.snowman.net> References: <5323.1284170438@sss.pgh.pa.us> <20100911020337.GH26232@tamriel.snowman.net> From: Mason Harding Date: Mon, 13 Sep 2010 09:31:47 -0700 Message-ID: Subject: Re: Slow SQL lookup due to every field being listed in SORT KEY To: Stephen Frost Cc: Tom Lane , pgsql-performance@postgresql.org Content-Type: multipart/alternative; boundary=001636c5b4562aa80e049026a395 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.899 tagged_above=-10 required=5 tests=BAYES_00=-1.9, HTML_MESSAGE=0.001, RCVD_IN_DNSWL_NONE=-0.0001 X-Spam-Level: X-Archive-Number: 201009/89 X-Sequence-Number: 40292 --001636c5b4562aa80e049026a395 Content-Type: text/plain; charset=ISO-8859-1 Thanks all for your help. I didn't really understand why it was sorting on every field, but it now makes sense. What I ended up doing was replacing the SELECT DISTINCT * FROM .... JOIN ... WHERE ... ORDER BY... LIMIT ... with SELECT * FROM ... WHERE id in (SELECT DISTINCT id FROM .... JOIN ... WHERE ... ) ORDER BY... LIMIT ... This reduced the lookup time down to 19 ms, which is much faster than just upping the work_mem, as that still took 800ms Thanks all, Mason On Fri, Sep 10, 2010 at 7:03 PM, Stephen Frost wrote: > * Tom Lane (tgl@sss.pgh.pa.us) wrote: > > The reason it's sorting by all the columns is the DISTINCT > > You might also verify that you actually need/*should* have the DISTINCT, > if it's included today.. Often developers put that in without > understanding why they're getting dups (which can often be due to > missing pieces from the JOIN clause or misunderstanding of the database > schema...). > > Stephen > > -----BEGIN PGP SIGNATURE----- > Version: GnuPG v1.4.9 (GNU/Linux) > > iEYEARECAAYFAkyK43kACgkQrzgMPqB3kihX4ACfVboO4jRzFO3hkckdHfrSeAgF > sysAnjmeoV7BA7uClEY8gXT4nEYhSx0u > =y556 > -----END PGP SIGNATURE----- > > --001636c5b4562aa80e049026a395 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Thanks all for your help.=A0 I didn't really understand why it was sort= ing on every field, but it now makes sense.=A0 What I ended up doing was re= placing the

SELECT DISTINCT * FROM .... JOIN ... WHERE ... ORDER BY= ... LIMIT ...
with

SELECT * FROM ... WHERE id in (SELECT DISTINCT id FROM .... JOI= N ... WHERE ... ) ORDER BY... LIMIT ...
This reduced the lookup time dow= n to 19 ms, which is much faster than just upping the work_mem, as that sti= ll took 800ms

Thanks all,
Mason

On Fri, Sep 10, = 2010 at 7:03 PM, Stephen Frost <sfrost@snowman.net> wrote:
* Tom Lane (tgl@sss.= pgh.pa.us) wrote:
> The reason it's sorting by all the columns is the DISTINCT

You might also verify that you actually need/*should* have the DISTIN= CT,
if it's included today.. =A0Often developers put that in without
understanding why they're getting dups (which can often be due to
missing pieces from the JOIN clause or misunderstanding of the database
schema...).

=A0 =A0 =A0 =A0Stephen

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.9 (GNU/Linux)

iEYEARECAAYFAkyK43kACgkQrzgMPqB3kihX4ACfVboO4jRzFO3hkckdHfrSeAgF
sysAnjmeoV7BA7uClEY8gXT4nEYhSx0u
=3Dy556
-----END PGP SIGNATURE-----


--001636c5b4562aa80e049026a395--