X-Original-To: pgsql-general-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.130]) by svr1.postgresql.org (Postfix) with ESMTP id 9FFB9D1B4E1 for ; Sun, 28 Sep 2003 18:21:32 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (neptune.hub.org [64.117.224.130]) (amavisd-new, port 10024) with ESMTP id 92539-03 for ; Sun, 28 Sep 2003 15:21:23 -0300 (ADT) Received: from phaze.fireserve.net (phaze.fireserve.net [207.109.249.2]) by svr1.postgresql.org (Postfix) with ESMTP id 1F924D1B52C for ; Sun, 28 Sep 2003 15:21:21 -0300 (ADT) Received: from fireserve.net (dh0020.moore-s.fireserve.net [207.109.249.149]) by phaze.fireserve.net (8.12.10/linuxconf) with ESMTP id h8SIO25i026744; Sun, 28 Sep 2003 11:24:02 -0700 Message-ID: <3F77268C.7040609@fireserve.net> Date: Sun, 28 Sep 2003 11:21:00 -0700 From: Dennis Gearon User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.4) Gecko/20030624 X-Accept-Language: en-us, en, ru MIME-Version: 1.0 To: holger@marzen.de Cc: David Griffiths , pgsql-general@postgresql.org Subject: Re: Tuning/performance question. References: <01d201c38573$84606930$6501a8c0@griffiths2> In-Reply-To: Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit X-MailScanner: Found to be clean X-MailScanner-SpamCheck: not spam, SpamAssassin (score=-4.782, required 6, AWL, BAYES_00) X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200309/1743 X-Sequence-Number: 49962 Holger Marzen wrote: >On Sat, 27 Sep 2003, David Griffiths wrote: > > > >>We are doing some performance testing among various databases (Oracle, MySQL >>and Postgres). >> >>One of the queries is showing Postgres lagging quite a bit: >> >>SELECT count(*) >>FROM commercial_entity, country, user_account, address_list >>LEFT JOIN state_province ON address_list.state_province_id = >>state_province.state_province_id >>LEFT JOIN contact_info ON address_list.contact_info_id = >>contact_info.contact_info_id >>WHERE address_list.address_type_id = 101 >>AND commercial_entity.commercial_entity_id = >>address_list.commercial_entity_id >>AND address_list.country_id = country.country_id >>AND commercial_entity.user_account_id = user_account.user_account_id >>AND user_account.user_role_id IN (101, 101); >> >> > >I guess that this question has been discussed very often - but I cannot >remember why exactly. Is there a pointer to a technical explanation? Has >it something to do with MVCC? But ist it one of MVCC's benefits that we >can make a consistent online backup without archiving redo locks (Oracle >can't, DB2 can). Is DB2 slower than Oracle in such cases (count(*)) as >well? > >Workaround: >We can sometimes fake a bit to avoid such costly queries and set up a >trigger that calls a function that increases a counter in a separate >counter table. Then we are lightning-fast. > >But many users compain about PostgreSQL's poor count(*) performance, >that's true and can be critical when someone wants to replace another >database product by PostgreSQL. > >---------------------------(end of broadcast)--------------------------- >TIP 6: Have you searched our list archives? > > http://archives.postgresql.org > > > Yup, it'd be nice to have faster count(*) performance.