Robert Fitzpatrick wrote:
On Tue, 2007-05-22 at 13:10 -0400, Tom Lane wrote:
Robert Fitzpatrick <[EMAIL PROTECTED]> writes:
On Tue, 2007-05-22 at 17:21 +0100, Richard Huxton wrote:
Your query seems to produce 41.8 million rows. Are you sure MS-SQL is
returning that many rows in a few seconds?
I thought the same thing. While I'm not a MS SQL guru, I put 'TOP 100
PERCENT' after SELECT in the query. The Enterprise Manager does not
indicate how many rows come back. I save it as a VIEW in MS SQL and do a
'select count(*)...' and, yes, it comes back 42164877 records.
Just to be sure MS SQL hasn't done something to the structure (I noticed
dbo prefixes, etc.), I pasted back into pgadmin, took off 'top 100
percent'. Then saved as a view and did a count(*) in pgsql, got
41866801.
How much time do the two select count(*) operations take? That would be
a reasonably fair comparison of the query engines, as opposed to
whatever might be happening on the client side (in particular, I wonder
whether the MS client is actually fetching all the rows or just the
first few).
Takes 25K ms in pgsql, don't see a timer in MS Ent Manager, but only 5
seconds clocked. Maybe I should put together a php script to operate on
each to be using the exact same client. I am doing all this all on the
same server with PostgreSQL 8.2 loaded in Windows Server 2003 also with
MS SQL server 2000.
1. Be aware that with the real query, PG is giving you the time to fetch
the *last* row, whereas MS is probably the *first* row. I'm reasonably
sure of this because 4.1 million rows * (say) 256 bytes would be 1GB of
data to return, which in a few seconds seems optimistic.
2. If you don't want all the rows in one go, explicitly declare a cursor
with PG.
3. The EXPLAIN ANALYSE will not have the
format-and-transfer-data-to-client costs, but I think does the rest of
the query.
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.
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)?
--
Richard Huxton
Archonet Ltd
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
