Hi Andres, May you missed my first post, and I paste it here again: In our environment sequential scanning (select * from ...) for a table with tens of thousands of record costs 1 - 2 seconds, regardless of using ODBC driver or the "timing" result shown in psql client (which in turn, relies on libpq). However, using EXPLAIN ANALYZE, or checking the statistics in pg_stat_statement view, the query costs only less than 100ms.
Has the pg_stat_statement or EXPLAIN ANALYZE included the cost of copying tuples from shared buffers to result sets? Best regards, Han On Wed, Feb 15, 2012 at 6:55 PM, Andres Freund <and...@anarazel.de> wrote: > Hi, > On Wednesday, February 15, 2012 11:19:00 AM Zhou Han wrote: >> I have tried unix domain socket and the performance is similar with >> TCP socket. It is MIPS architecture so memory copy to/from kernel can >> occupy much time, and apparently using unit domain socket has no >> difference than TCP in terms of memory copy. > >> But it is still unbelievable for the ten-fold gap between the client >> side statistic and the server side statistics. So I want to know what >> exactly the operations are involved in the server side statistics in >> EXPLAIN ANALYZE. May I check the code later on when I get time. > My guess is that the time difference youre seing is actually the planning > time. > The timing shown at the end of EXPLAIN ANALYZE is just the execution, not the > planning time. You can use "\timing on" in psql to let it display timing > information that include planning. > > Whats the query? >> For the query itself, it was just for performance comparison. There >> are other index based queries, which are of course much faster, but >> still result in similar ten-fold of time gap between client side and >> server side statistics. >> >> I am thinking of non-kernel involved client interface, is there such >> an option, or do I have to develop one from scratch? > Its unlikely thats possible in a sensible amount of time. But I don't think > thats your problem anyway. > > Andres -- Best regards, Han -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
