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
Date: Thu, 27 Oct 2005 17:52:27 -0500
Message-ID: <20051027225227.GT63747@pervasive.com> (raw)
In-Reply-To: <43614D9F.9010600@pendragon.org>
References: <BAY23-F28F5DD23A4A73C7BFE3CD1CE680@phx.gbl>
<4360F8C1.4040202@archonet.com>
<43614D9F.9010600@pendragon.org>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: jnasby@pervasive.com, mbest@pendragon.org
Subject: Re: how postgresql request the computer resources
In-Reply-To: <20051027225227.GT63747@pervasive.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox