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
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
