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
