From: Dennis Gearon <gearond@fireserve.net>
To: holger@marzen.de
Cc: David Griffiths <dgriffiths@boats.com>
Cc: pgsql-general@postgresql.org
Subject: Re: Tuning/performance question.
Date: Sun, 28 Sep 2003 11:21:00 -0700
Message-ID: <3F77268C.7040609@fireserve.net> (raw)
In-Reply-To: <Pine.LNX.4.58.0309281308470.27404@bluebell.marzen.de>
References: <DBEIKNMKGOBGNDHAAKGNIENDFBAC.dave@hawk-systems.com>
<01d201c38573$84606930$6501a8c0@griffiths2>
<Pine.LNX.4.58.0309281308470.27404@bluebell.marzen.de>
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-performance@postgresql.org
Cc: gearond@fireserve.net, holger@marzen.de, dgriffiths@boats.com, pgsql-general@postgresql.org
Subject: Re: Tuning/performance question.
In-Reply-To: <3F77268C.7040609@fireserve.net>
References: <DBEIKNMKGOBGNDHAAKGNIENDFBAC.dave@hawk-systems.com> <01d201c38573$84606930$6501a8c0@griffiths2> <Pine.LNX.4.58.0309281308470.27404@bluebell.marzen.de> <3F77268C.7040609@fireserve.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox