Re: Slow count(*) again...

david@lang.hm wrote:
> On Tue, 12 Oct 2010, Mladen Gogala wrote:
>
>   
>> david@lang.hm wrote:
>>     
>>> from a PR point of view, speeding up the trivil count(*) case could be 
>>> worth it, just to avoid people complaining about it not being fast.
>>>
>>>
>>>       
>> Fixing PR stuff is not the approach that I would take. People are complaining 
>> about select count(*) because they're using it in all the wrong places.
>>     
>
> that may be the case, but if it's possible to make it less painful it will 
> mean more people use postgres, both because it works better for them when 
> they are using the suboptimal programs, but also because when people do 
> their trivial testing of databases to decide which one they will use, they 
> won't rule out postgres because "it's so slow"
>   
>

There is no free lunch. If the count field is maintained somewhere, the 
concurrency will suffer. I find the idea of fixing the "count delusion" 
ridiculous, may Richard Dawkins forgive me for this pun. Saying that 
something is slow without testing and a proper
consideration is ridiculous. As a DBA, I usually get complaints like 
"the database is slow today" 3 times before lunch, every day.  The 
database is never slow, the database is a warehouse where you keep your 
data. What is slow is the access to the data, and that is done by, guess 
what, the application program. Almost always, it's the application 
that's slow, not the database. As for the "select count(*)", idiom, what 
are you trying to do? Where are you using it? If you are using it for 
pagination, consider the possibility of not specifying
the number of pages on the website, just the links "next -->" and "prev 
<--". Alternatively, you can fetch a small amount into the web page and 
direct the users who would like to see the complete information to a 
background reporting too. Mixing batch reports and online reports is a 
very easy thing to do. If you are using it to establish existence, 
you're doing it wrong. I've had a problem like that this morning. A 
developer came to me with the usual phrase that the "database is slow".  
It was a PHP form which should write an output file and let the user 
know where the file is. The function looks like this:

function put_xls($sth) {
    global $FNAME;
    $FNAME=$FNAME.".xls";
    $lineno=0;
    $ncols=$sth->FieldCount();
    for ($i = 0;$i <= $ncols;$i++) {
        $cols[$i] = $sth->FetchField($i);
        $colnames[$i]=$cols[$i]->name;
    }
    $workbook = new Spreadsheet_Excel_Writer("/software$FNAME");
    $format_bold =& $workbook->addFormat();
    $format_bold->setBold();
    $format_bold->setAlign('left');
    $format_left =& $workbook->addFormat();
    $format_left->setAlign('left');
    $worksheet = & $workbook->addWorksheet('Moreover Search');
    $worksheet->writeRow($lineno++,0,$colnames,$format_bold);
    while($row=$sth->FetchRow()) {
        $worksheet->writeRow($lineno++,0,$row,$format_left);
    }
    $workbook->close();
    $cnt=$sth->Recordcount();
    return($cnt);
}

The relevant includes are here:

require ('Date.php');
require ('adodb5/tohtml.inc.php');
require_once ('adodb5/adodb.inc.php');
require_once ('adodb5/adodb-exceptions.inc.php');
require_once 'Spreadsheet/Excel/Writer.php';
$ADODB_FETCH_MODE = ADODB_FETCH_NUM;

So, what is the problem here? Why was the "database slow"? As it turns 
out, the PEAR module for writing Excel spreadsheets, which is the tool 
used here, creates the entire spreadsheet in  memory and writes it out 
on the "close" command. What was spinning was "httpd" process, the 
database was completely and utterly idle, rolling thumbs and awaiting 
orders. Using the "fputcsv" instead, made the function fly. The only 
thing that was lost were the bold column titles. Changing little things 
can result in the big performance gains. Making "select count(*)" 
unnaturally  fast would be tending to  bad programming practices. I am 
not sure that this is a desirable development. You can't expect people 
to adjust the database software to your application. Applications are 
always database specific. Writing  an application that will access a 
PostgreSQL database is not the same as writing an application that will 
access an Oracle database.

-- 
 
Mladen Gogala 
Sr. Oracle DBA
1500 Broadway
New York, NY 10036
(212) 329-5251
http://www.vmsinfo.com 
The Leader in Integrated Media Intelligence Solutions