On Mon, Apr 30, 2012 at 1:32 PM, Ronald Hahn, DOCFOCUS INC.
<rhahn@docfocus.ca> wrote:
> Hi,
> We have recently switch our product from MS SQL 2000 to Postgresql
> 9.0.7. We have tuned the searches and indexes so that they are very close
> (often better) to what sql2k was giving us. We are noticing some
> differences now in the time it takes for the result set to make it back to
> the client and would like some help finding out why.
>
> What we see on the PG side is that if we run:
> Select SomeInt32 from someTable where something Limit 1
> It consistently returns the results "instantaneously" after the fetch
> time. If we run the same select but ask for more data the fetch time stays
> the same but the row takes longer to come back. Bringing back 400 bytes
> takes 1-2 s but bringing back 866 bytes takes 9 - 11 s.
>
> We went to the SQL2k server (On the same hardware) and ran the selects
> again. When bringing back on an int32 PG was faster with the fetch and the
> row coming back in 1-5 ms and SQL2k coming back in 500-700 ms. This tells me
> that the problem is not related to PG index or Disk. When bringing back 400
> bytes PG fetch time would be 1-2 ms but the results would take 2-3 s but
> SQL2k would it bring back in 700-900 ms. Working with 866 bytes, PG fetch
> time is 1-3 ms with the results coming back in 9 - 11 s and SQL2k bringing
> the results back in 2-3 s.
>
> The head to head test was run in Aqua Data Studio 10.0.8 and ODBC driver
> 9.0.3.10. The same slow down happens when use PGadminIII. The differnces in
> time to not occure when running on the pg/sql server computer so I think
> there is a network component to this.
to rule out network just do:
create temp table scratch as select <your query>...
if it's a lot faster, then you have a probable network issue.
merlin