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