Re: Problems with base and postgresql

[Frank Knobbe <frank () knobbe us>]

On Tue, 2005-04-05 at 15:54 -0400, Michael Stone wrote:
> For example, there are a lot of instances of count(*) in the code. mysql
> shortcuts that operation by keeping a counter, but in postgres that
> triggers a sequential scan of the table--which can take a while for a
> large table. 

This can be solved by counting an element that is indexed instead of *,
for example, if a single index for ip_src exist, count(ip_src) is faster
than count(*) as it will to a scan on the index.

Also, don't forget about tricks like "limit 1" etc. Queries can, and
should be tuned, to each individual database in order to achieve maximum
throughput.

Perhaps one size does not fit all, and someone might want to create a
BASE-pg and BASE-mysql version or something. But since it's open source,
everyone is welcome to tweak their own stuff.

Postgres performance tuning is borderline to a black-art. But once tuned
(server and application)... man... it just rocks.

Regards,
Frank

Attachment: signature.asc
Description: This is a digitally signed message part