Re: Permance issues with migrated db

Robert Fitzpatrick wrote:
> On Tue, 2007-05-22 at 13:10 -0400, Tom Lane wrote:
>> Robert Fitzpatrick <lists(at)webtent(dot)net> 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