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 [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
