Skip site navigation (1) Skip section navigation (2)

Re: how postgresql request the computer resources

From: "Jim C(dot) Nasby" <jnasby(at)pervasive(dot)com>
To: Michael Best <mbest(at)pendragon(dot)org>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: how postgresql request the computer resources
Date: 2005-10-27 22:52:27
Message-ID: 20051027225227.GT63747@pervasive.com (view raw or flat)
Thread:
Lists: pgsql-performance
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(at)pervasive(dot)com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461

In response to

pgsql-performance by date

Next:From: Jim C. NasbyDate: 2005-10-27 22:55:17
Subject: Re: How much memory?
Previous:From: Jim C. NasbyDate: 2005-10-27 22:48:56
Subject: Re: What gets cached?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group