Re: Slow count(*) again...

Joe Conway wrote:
> On 10/09/2010 06:54 PM, Mladen Gogala wrote:
>   
>> In another database, whose name I will not mention, there is a parameter
>> db_file_multiblock_read_count which specifies how many blocks will be
>> read by a single read when doing a full table scan. PostgreSQL is in
>> dire need of something similar and it wouldn't even be that hard to
>> implement.
>>     
>
> You're correct in that it isn't particularly difficult to implement for
> sequential scans. But I have done some testing with aggressive read
> ahead, and although it is clearly a big win with a single client, the
> benefit was less clear as concurrency was increased.
>
> Joe
>
>   
Well, in my opinion that should be left to the DBA, the same as in the 
"other database".  The mythical DBA, the creature that mighty Larry 
Ellison himself is on a crusade against, usually can  figure out the 
right value for the database he or she's is in charge of. I humbly 
confess to being an Oracle DBA for more than 2 decades and now branching 
into Postgres because my employer is less than enthusiastic about 
Oracle, with the special accent on their pricing.

Modern databases, Postgres included, are quite complex and companies 
need DBA personnel to help fine tune the applications. I know that good 
DBA personnel is quite expensive but without a competent DBA who knows 
the database software well enough,  companies can and will suffer from 
blunders with performance, downtime, lost data and alike. In the world 
where almost every application is written for the web, performance, 
uptime and user experience are of the critical importance. The 
architects of Postgres database would be well advised to operate under 
the assumption that every production database has a competent DBA 
keeping an eye on the database.

Every application has its own mix of sequential and index scans, you 
cannot possibly test all possible applications.  Aggressive read-ahead 
or "multi-block reads" can be a performance problem and it will 
complicate the optimizer, because the optimizer now has a new variable  
to account for: the block size, potentially making  seq_page_cost even 
cheaper and random_page_cost even more expensive, depending on the 
blocking. However,  slow sequential scan is, in my humble opinion, the 
single biggest performance problem of the PostgreSQL databases and 
should be improved, the sooner, the better. You should, however, count 
on the DBA personnel to help with the tuning.
We're the Tinkerbells of the database world. I am 6'4", 240 LBS, no wings.


-- 
Mladen Gogala 
Sr. Oracle DBA
1500 Broadway
New York, NY 10036
(212) 329-5251
www.vmsinfo.com