Re: getting count for a specific querry - Mailing list pgsql-sql

Please run this disk throughput test on your system :

http://boutiquenumerique.com/pf/multi_io.py
It just spawns N threads which will write a lot of data simultaneously to  
the disk, then measures the total time. Same for read. Modify the  
parameters in the source... it's set to generate 10G of files in the  
current directory and re-read them, all with 8 threads.
How much I/O do you get ?
Also  hdparm -t /dev/hd? would be interesting.

On Fri, 08 Apr 2005 21:51:02 +0200, Joel Fradkin <jfradkin@wazagua.com>  
wrote:

> I will also look at doing it the way you describe, they do have wide
> liberty. Thanks so much for the ideas. Sorry I did not do a perusal of  
> the
> archives first (I normally try that, but think I am brain dead today).
>
> Joel Fradkin
> Wazagua, Inc.
> 2520 Trailmate Dr
> Sarasota, Florida 34243
> Tel.  941-753-7111 ext 305
> jfradkin@wazagua.com
> www.wazagua.com
> Powered by Wazagua
> Providing you with the latest Web-based technology & advanced tools.
> C 2004. WAZAGUA, Inc. All rights reserved. WAZAGUA, Inc
>  This email message is for the use of the intended recipient(s) and may
> contain confidential and privileged information.  Any unauthorized  
> review,
> use, disclosure or distribution is prohibited.  If you are not the  
> intended
> recipient, please contact the sender by reply email and delete and  
> destroy
> all copies of the original message, including attachments.
>
>
> -----Original Message-----
> From: pgsql-sql-owner@postgresql.org  
> [mailto:pgsql-sql-owner@postgresql.org]
> On Behalf Of Mischa Sandberg
> Sent: Friday, April 08, 2005 2:40 PM
> To: Scott Marlowe
> Cc: pgsql-sql@postgresql.org
> Subject: Re: [SQL] getting count for a specific querry
>
> Quoting Scott Marlowe <smarlowe@g2switchworks.com>:
>
>> On Fri, 2005-04-08 at 12:08, Joel Fradkin wrote:
>> > I might have to add a button to do the count on command so they don't
> get
>> > the hit.
>> > I would want it to return the count of the condition, not the  
>> currently
>> > displayed number of rows.
>>
>> Judging postgresql on one single data point (count(*) performance) is
>> quite unfair.  Unless your system only operates on static data and is
>> used to mostly do things like counting, in which case, why are you using
>> a database?
>
> For the general discussion of slowness of count(*),
> and given no entry on the subject in
>    http://www.postgresql.org/docs/faqs.FAQ.html
> ... I guess everyone has to be pointed at:
>  http://archives.postgresql.org/pgsql-hackers/2005-01/msg00247.php
>
> However, the gist of this person's problem is that an adhoc query,
> NOT just a 'select count(*) from table', can take remarkably long.
> Again, the problem is that PG can't just scan an index.
> ----------------------
> One workaround for this is to use EXPLAIN.
> THIS APPLIES TO CASES WHERE THE USER HAS WIDE LIBERTY IN QUERIES.
> It's pointless overhead, otherwise.
>
> default_statistics_target is cranked up to 200 on all such tables,
> and pg_autovacuum is running. (If there were anything to improve,
> it would be refining the thresholds on this).
>
> If the "(cost...rows=nnnn" string returns a number higher than the
> QUERY row limit, the user is derailed ("That's not specific enough to  
> answer
> immediately; do you want an emailed report?").
>
> Otherwise, it runs EXPLAIN ANALYZE, which is still faster than the query
> itself.
> If the "(actual...rows=nnnn...)" is higher than the RESULT row limit  
> (PAGE
> limit).
>
> It then runs the query, with the PAGE rows offset and limit --- and  
> happily,
> practically everything that query needs is now in shared_buffers.
> The count from the EXPLAIN analyze is displayed in the web page.
>