X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.130]) by svr1.postgresql.org (Postfix) with ESMTP id 4A1FCD1D1D5 for ; Fri, 5 Sep 2003 20:05:22 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (neptune.hub.org [64.117.224.130]) (amavisd-new, port 10024) with ESMTP id 17541-03 for ; Fri, 5 Sep 2003 17:05:10 -0300 (ADT) Received: from news.hub.org (unknown [64.117.224.194]) by svr1.postgresql.org (Postfix) with ESMTP id 20242D1E761 for ; Fri, 5 Sep 2003 15:25:10 -0300 (ADT) Received: from news.hub.org (host-64-117-224-194.altec1.com [64.117.224.194] (may be forged)) by news.hub.org (8.12.9/8.12.9) with ESMTP id h85IP9Df094744 for ; Fri, 5 Sep 2003 18:25:09 GMT (envelope-from news@news.hub.org) Received: (from news@localhost) by news.hub.org (8.12.9/8.12.9/Submit) id h85IInWv093354 for pgsql-performance@postgresql.org; Fri, 5 Sep 2003 18:18:49 GMT From: "Relaxin" X-Newsgroups: comp.databases.postgresql.performance Subject: Re: SELECT's take a long time compared to other DBMS Date: Fri, 5 Sep 2003 11:18:35 -0700 Organization: Hub.Org Networking Services Lines: 129 Message-ID: References: X-Complaints-To: usenet@news.hub.org X-Priority: 3 X-MSMail-Priority: Normal X-Newsreader: Microsoft Outlook Express 6.00.2800.1158 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165 To: pgsql-performance@postgresql.org X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200309/103 X-Sequence-Number: 3469 Expect that the Declare/Fetch only creates a forwardonly cursor, you can go backwards thru the result set. ""Patrick Hatcher"" wrote in message news:OFAD2A2CF4.499F8F67-ON88256D98.00527BCB-88256D98.00538130@fds.com... > > Relaxin, > I can't remember during this thread if you said you were using ODBC or not. > If you are, then your problem is with the ODBC driver. You will need to > check the Declare/Fetch box or you will definitely bring back the entire > recordset. For small a small recordset this is not a problem, but the > larger the recordset the slower the data is return to the client. I played > around with the cache size on the driver and found a value between 100 to > 200 provided good results. > > HTH > Patrick Hatcher > > > > > > "Relaxin" > Sent by: To: pgsql-performance@postgresql.org > pgsql-performance-owner@post cc: > gresql.org Subject: Re: [PERFORM] SELECT's take a long time compared to other DBMS > > > 09/04/2003 07:13 PM > > > > > > Thank you Christopher. > > > Change fsync to true (you want your data to survive, right?) and > > increase shared buffers to something that represents ~10% of your > > system memory, in blocks of 8K. > > I turned it off just in the hope that things would run faster. > > > None of this is likely to substantially change the result of that one > > query, however, and it seems quite likely that it is because > > PostgreSQL is honestly returning the whole result set of ~100K rows at > > once, whereas the other DBMSes are probably using cursors to return > > only the few rows of the result that you actually looked at. > > Finally, someone who will actually assume/admit that it is returning the > entire result set to the client. > Where as other DBMS manage the records at the server. > > I hope PG could fix/enhance this issue. > > There are several issues that's stopping our company from going with PG > (with paid support, if available), but this seems to big the one at the top > of the list. > > The next one is the handling of BLOBS. PG handles them like no other > system > I have ever come across. > > After that is a native Windows port, but we would deal cygwin (for a very > little while) if these other issues were handled. > > Thanks > > > > > > "Christopher Browne" wrote in message > news:m3fzjc58ll.fsf@chvatal.cbbrowne.com... > > A long time ago, in a galaxy far, far away, "Relaxin" > wrote: > > >> Have you changed any of the settings yet in postgresql.conf, > > >> specifically the shared_buffers setting? > > > > > > fsync = false > > > tcpip_socket = true > > > shared_buffers = 128 > > > > Change fsync to true (you want your data to survive, right?) and > > increase shared buffers to something that represents ~10% of your > > system memory, in blocks of 8K. > > > > So, if you have 512MB of RAM, then the total blocks is 65536, and it > > would likely be reasonable to increase shared_buffers to 1/10 of that, > > or about 6500. > > > > What is the value of effective_cache_size? That should probably be > > increased a whole lot, too. If you are mainly just running the > > database on your system, then it would be reasonable to set it to most > > of memory, or > > (* 1/2 (/ (* 512 1024 1024) 8192)) > > 32768. > > > > None of this is likely to substantially change the result of that one > > query, however, and it seems quite likely that it is because > > PostgreSQL is honestly returning the whole result set of ~100K rows at > > once, whereas the other DBMSes are probably using cursors to return > > only the few rows of the result that you actually looked at. > > -- > > "cbbrowne","@","cbbrowne.com" > > http://www3.sympatico.ca/cbbrowne/linuxdistributions.html > > Rules of the Evil Overlord #14. "The hero is not entitled to a last > > kiss, a last cigarette, or any other form of last request." > > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 8: explain analyze is your friend > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly >