Robert Fitzpatrick wrote:
> On Tue, 2007-05-22 at 19:03 +0100, Richard Huxton wrote:
>> 4. We're still 5 x slower than MS-SQL (with the count). That might
>> well
>> be down to having to check visibility on each row with our MVCC
>> rather
>> than just going to the index.
>
> Tips? I'd love to know how to see inside MVCC. I really appreciate the
> help!
The main thing is that PostgreSQL's implementation of MVCC means that
1. (In many cases) writers need not block readers.
2. An update is effectively a delete and an insert.
3. VACUUM is needed to mark space from deleted rows for re-use.
4. The indexes don't carry visibility information, which means we need
to go to the actual row on-disk to see if the current transaction can
actually see the row.
This last point is a problem for things like count(*) where we can't
just count entries in the index because we don't know if some of the
rows they point to might be deleted. The reason we don't store
visibility info with the index is that it makes the index larger, so
using up valuable RAM more quickly.
For more info, see "Internals" in the manuals for a start. Then have a
quick look around these for some more bits & pieces. There are some
presentation slides somewhere. Note - I am *not* a developer, just a
long-term user.
http://www.postgresql.org/developer/
http://www.postgresql.org/docs/faqs.FAQ_DEV.html
http://www.postgresql.org/docs/techdocs
>> Hmm... How much of your machine is PG getting to use vs. MS-SQL? What
>> are your shared_buffers, work_mem, effective_cache_size (and how much
>> RAM on this box)?
>
> 3.5GB of RAM on a Xeon 2.8GHz server. I have default shared_buffers
> 32MB, no defaults changed except listen_addresses. How can I check
> work_mem and effective_cache_size?
Ah - I bet MS-SQL is squatting on a gig of RAM or some-such too. Read
through this - it's a little old, but still good advice.
http://www.powerpostgresql.com/PerfList/
You'll probably find increasing work_mem (by a lot) for this one query
will help you out.
SET work_mem = <something large>;
SELECT ....
SET work_mem = <small again>;
--
Richard Huxton
Archonet Ltd