Skip site navigation (1) Skip section navigation (2)

Re: Tuning/performance question.

From: Holger Marzen <holger(at)marzen(dot)de>
To: David Griffiths <dgriffiths(at)boats(dot)com>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: Tuning/performance question.
Date: 2003-09-28 11:13:54
Message-ID: Pine.LNX.4.58.0309281308470.27404@bluebell.marzen.de (view raw or flat)
Thread:
Lists: pgsql-generalpgsql-performance
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.

In response to

Responses

pgsql-performance by date

Next:From: Matt ClarkDate: 2003-09-28 12:07:57
Subject: Re: advice on raid controller
Previous:From: Shridhar DaithankarDate: 2003-09-28 10:09:56
Subject: Re: Tuning/performance question.

pgsql-general by date

Next:From: Bill BellDate: 2003-09-28 13:26:53
Subject: Hesitate to write this: can't get at postgres.org
Previous:From: Shridhar DaithankarDate: 2003-09-28 10:09:56
Subject: Re: Tuning/performance question.

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group