Received: from magus.postgresql.org (magus.postgresql.org [87.238.57.229]) by mail.postgresql.org (Postfix) with ESMTP id 54D5710A8FE for ; Wed, 15 Feb 2012 01:23:33 -0400 (AST) Received: from szxga03-in.huawei.com ([119.145.14.66]) by magus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1RxXKy-0005OW-5T for pgsql-hackers@postgresql.org; Wed, 15 Feb 2012 05:23:33 +0000 Received: from huawei.com (szxga03-in [172.24.2.9]) by szxga03-in.huawei.com (iPlanet Messaging Server 5.2 HotFix 2.14 (built Aug 8 2006)) with ESMTP id <0LZF00F5M5MMQ5@szxga03-in.huawei.com> for pgsql-hackers@postgresql.org; Wed, 15 Feb 2012 13:23:10 +0800 (CST) Received: from szxrg02-dlp.huawei.com ([172.24.2.119]) by szxga03-in.huawei.com (iPlanet Messaging Server 5.2 HotFix 2.14 (built Aug 8 2006)) with ESMTP id <0LZF004BZ5MMPC@szxga03-in.huawei.com> for pgsql-hackers@postgresql.org; Wed, 15 Feb 2012 13:23:10 +0800 (CST) Received: from szxeml212-edg.china.huawei.com ([172.24.2.119]) by szxrg02-dlp.huawei.com (MOS 4.1.9-GA) with ESMTP id AHC91613; Wed, 15 Feb 2012 13:23:09 +0800 Received: from SZXEML410-HUB.china.huawei.com (10.82.67.137) by szxeml212-edg.china.huawei.com (172.24.2.181) with Microsoft SMTP Server (TLS) id 14.1.323.3; Wed, 15 Feb 2012 13:23:04 +0800 Received: from blrprnc09ns (10.18.96.98) by szxeml410-hub.china.huawei.com (10.82.67.137) with Microsoft SMTP Server id 14.1.323.3; Wed, 15 Feb 2012 13:23:06 +0800 Date: Wed, 15 Feb 2012 10:53:04 +0530 From: Amit Kapila Subject: Re: client performance v.s. server statistics In-reply-to: X-Originating-IP: [10.18.96.98] To: 'Zhou Han' , pgsql-hackers@postgresql.org Message-id: <002301cceba1$e5577d80$b0067880$%kapila@huawei.com> MIME-version: 1.0 X-Mailer: Microsoft Office Outlook 12.0 Content-type: multipart/alternative; boundary="Boundary_(ID_Ar0TM+tpLreW4CsFjlEFTQ)" Content-language: en-us Thread-index: AczrlneJ64LB7pwNTomy9jL4elP6LQABIjBw X-CFilter-Loop: Reflected References: X-Pg-Spam-Score: -4.2 (----) X-Archive-Number: 201202/611 X-Sequence-Number: 202975 --Boundary_(ID_Ar0TM+tpLreW4CsFjlEFTQ) Content-type: text/plain; charset=us-ascii Content-transfer-encoding: 7BIT >>So, is it client interface (ODBC, libpq) 's cost mainly due to TCP? The difference as compare to your embedded DB you are seeing is mainly seems to be due to TCP. One optimization you can use is to use Unix-domain socket mode of PostgreSQL. You can refer unix_socket_directory parameter in postgresql.conf and other related parameters. I am suggesting you this as earlier you were using embedded DB, so your client/server should be on same machine. If now this is not the case then it will not work. Can you please clarify some more things like 1. After doing sequence scan, do you need all the records in client for which seq. scan is happening. If less records then why you have not created index. 2. What is exact scenario for fetching records From: pgsql-hackers-owner@postgresql.org [mailto:pgsql-hackers-owner@postgresql.org] On Behalf Of Zhou Han Sent: Wednesday, February 15, 2012 9:30 AM To: pgsql-hackers@postgresql.org Subject: [HACKERS] client performance v.s. server statistics Hi, I am checking a performance problem encountered after porting old embeded DB to postgreSQL. While the system is real-time sensitive, we are concerning for per-query cost. 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. So, is it client interface (ODBC, libpq) 's cost mainly due to TCP? Has the pg_stat_statement or EXPLAIN ANALYZE included the cost of copying tuples from shared buffers to result sets? Could you experts share your views on this big gap? And any suggestions to optimise? P.S. In our original embeded DB a "fastpath" interface is provided to read directly from shared memory for the records, thus provides extremely realtime access (of course sacrifice some other features such as consistency). Best regards, Han --Boundary_(ID_Ar0TM+tpLreW4CsFjlEFTQ) Content-type: text/html; charset=us-ascii Content-transfer-encoding: 7BIT

>>So, is it client interface (ODBC, libpq) 's cost mainly due to TCP?

 

The difference as compare to your embedded DB you are seeing is mainly seems to be due to TCP.

One optimization you can use is to use Unix-domain socket mode of PostgreSQL. You can refer unix_socket_directory parameter in postgresql.conf and other related parameters.

I am suggesting you this as earlier you were using embedded DB, so your client/server should be on same machine. If now this is not the case then it will not work.

 

Can you please clarify some more things like

1.      After doing sequence scan, do you need all the records in client for which seq. scan is happening. If less records then why you have not created index.

2.      What is exact scenario for fetching records

 

 

 

pgsql-hackers-owner@postgresql.org [mailto:pgsql-hackers-owner@postgresql.org] On Behalf Of Zhou Han
Sent: Wednesday, February 15, 2012 9:30 AM
To: pgsql-hackers@postgresql.org
Subject: [HACKERS] client performance v.s. server statistics

 

Hi,

I am checking a performance problem encountered after porting old embeded DB to postgreSQL. While the system is real-time sensitive, we are concerning for per-query cost. 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.
rface (ODBC, libpq) 's cost mainly due to TCP? Has the pg_stat_statement or EXPLAIN ANALYZE included the cost of copying tuples from shared buffers to result sets?

Could you experts share your views on this big gap? And any suggestions to optimise?

P.S. In our original embeded DB a "fastpath" interface is provided to read directly from shared memory for the records, thus provides extremely realtime access (of course sacrifice some other features such as consistency).

Best regards,
Han

--Boundary_(ID_Ar0TM+tpLreW4CsFjlEFTQ)--