On Thu, Oct 27, 2005 at 03:58:55PM -0600, Michael Best wrote:
> Richard Huxton wrote:
> >
> >
> >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      [EMAIL PROTECTED]
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Reply via email to