Date: Thu, 27 Oct 2005 17:52:27 -0500
From: "Jim C. Nasby" <jnasby@pervasive.com>
To: Michael Best <mbest@pendragon.org>
Cc: pgsql-performance@postgresql.org
Subject: Re: how postgresql request the computer resources
Message-ID: <20051027225227.GT63747@pervasive.com>
References: <BAY23-F28F5DD23A4A73C7BFE3CD1CE680@phx.gbl>
	<4360F8C1.4040202@archonet.com> <43614D9F.9010600@pendragon.org>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <43614D9F.9010600@pendragon.org>
User-Agent: Mutt/1.5.9i

On Thu, Oct 27, 2005 at 03:58:55PM -0600, Michael Best wrote:
> Richard Huxton wrote:
> >>WITH ANY OF THIS QUERIES MSSQL TAKES NOT MUCH OF 7 SECONDS....
> >
> >
> >In which case they make a bad choice for showing PostgreSQL is faster 
> >than MSSQL. Is this the only query you have, or are others giving you 
> >problems too?
> >
> >I think count(*) is about the weakest point in PG, but I don't think 
> >there'll be a general solution available soon. As I'm sure someone has 
> >mentioned, whatever else, PG needs to check the row for its visibility 
> >information.
> >
> > From the start of your email, you seem to suspect your configuration 
> >needs some work. Once you are happy that your settings in general are 
> >good, you can override some by issuing set statements before your query. 
> >For example:
> >    SET work_mem = 10000;
> >might well improve example #2 where you had a hash.
> >
> >-- 
> >  Richard Huxton
> >  Archonet Ltd
> 
> Someone had suggested keeping a vector table with +1 and -1 for row 
> insertion and deletion and then running a cron to sum the vectors and 
> update a table so that you could select from that table to get the row 
> count.  Perhaps some sort of SUM() on a column function.
> 
> Since this seems like a reasonable approach (or perhaps there may be yet 
> another better mechanism), cannot someone add this sort of functionality 
> to Postgresql to do behind the scenes?

There's all kinds of things that could be added; the issue is
ascertaining what the performance trade-offs are (there's no such thing
as a free lunch) and if the additional code complexity is worth it.

Note that your suggestion probably wouldn't work in this case because
the user isn't doing a simple SELECT count(*) FROM table;. I'd bet that
MSSQL is using index covering to answer his queries so quickly,
something that currently just isn't possible with PostgreSQL. But if you
search the -hackers archives, you'll find a discussion on adding limited
heap tuple visibility information to indexes. That would allow for
partial index covering in many cases, which would probably be a huge win
for the queries the user was asking about.
-- 
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461