Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1bbiIg-0007gZ-Fn for pgsql-performance@arkaria.postgresql.org; Mon, 22 Aug 2016 06:01:35 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1bbiIf-0005e7-BK for pgsql-performance@arkaria.postgresql.org; Mon, 22 Aug 2016 06:01:33 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1bbiId-0005dH-LX; Mon, 22 Aug 2016 06:01:31 +0000 Received: from md-in-15.webhostbox.net ([103.21.59.21]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1bbiIY-0005bp-JJ; Mon, 22 Aug 2016 06:01:29 +0000 DKIM-Signature: v=1; a=rsa-sha256; q=dns/txt; c=relaxed/relaxed; d=ipathsolutions.co.in; s=default; h=Message-ID:References:In-Reply-To: Subject:Cc:To:From:Date:Content-Transfer-Encoding:Content-Type:MIME-Version; bh=QgT2N73bWHaI333cWatSLGf8PaybvQVoEv/jjOGWyBY=; b=bfFnugB5oAZlPwRp/p9ClEqiH ToH10gQrMPm9Jlzv2ghVqJjT4x0p6ojAV/r4c2pVytDPEEZSee1u0vCBA76nS7nuW388TnX8ywAzk WIppi4K/Aac7TvUTzdFji0tXeSPHLgToN/zie8Pzxjk7QxPFIYkcOPB2m/EpUprKiQ5RpmQjaNJCg yMBkAbTPzBMbe/CjJKI//SZLUsRyV8Nqo/dpd09T09HLnltVTPWqrsIW5OcbrQiJlymh+GOZAHg0j JnxtDru5FJTkUT624v1c6flF1c7EtRgi9ToqnCSCD9PtDNwpnPeNbzV84dbDrMLtO3Kt/q+tRhHRX D6Nyl4eUA==; Received: from [127.0.0.1] (port=56282 helo=ipathsolutions.co.in) by md-in-15.webhostbox.net with esmtpa (Exim 4.86_1) (envelope-from ) id 1bbiIQ-003WuH-5j; Mon, 22 Aug 2016 06:01:19 +0000 MIME-Version: 1.0 Content-Type: text/plain; charset=US-ASCII; format=flowed Content-Transfer-Encoding: 7bit Date: Mon, 22 Aug 2016 06:01:18 +0000 From: debasis.moharana@ipathsolutions.co.in To: Pavel Stehule Cc: pgsql-performance@postgresql.org, pgsql-performance-owner@postgresql.org Subject: Re: pgsql-performance issue In-Reply-To: References: <701d07526dd15e94a38c857b4f459194@ipathsolutions.co.in> <3e9792028c8b4e186bb54343d1e59301@ipathsolutions.co.in> Message-ID: X-Sender: debasis.moharana@ipathsolutions.co.in User-Agent: Roundcube Webmail/1.0.6 X-Authenticated_sender: debasis.moharana@ipathsolutions.co.in X-OutGoing-Spam-Status: No, score=-1.0 X-AntiAbuse: This header was added to track abuse, please include it with any abuse report X-AntiAbuse: Primary Hostname - md-in-15.webhostbox.net X-AntiAbuse: Original Domain - postgresql.org X-AntiAbuse: Originator/Caller UID/GID - [47 12] / [47 12] X-AntiAbuse: Sender Address Domain - ipathsolutions.co.in X-Get-Message-Sender-Via: md-in-15.webhostbox.net: authenticated_id: debasis.moharana@ipathsolutions.co.in X-Authenticated-Sender: md-in-15.webhostbox.net: debasis.moharana@ipathsolutions.co.in X-Source: X-Source-Args: X-Source-Dir: X-Pg-Spam-Score: -0.0 (/) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-performance Precedence: bulk Sender: pgsql-performance-owner@postgresql.org On 2016-08-20 12:19, Pavel Stehule wrote: > 2016-08-20 14:17 GMT+02:00 : > >> On 2016-08-20 12:05, Pavel Stehule wrote: >> >> 2016-08-20 13:59 GMT+02:00 : >> >> On 2016-08-20 11:42, Pavel Stehule wrote: >> >> 2016-08-20 13:31 GMT+02:00 : >> >> On 2016-08-20 08:58, Pavel Stehule wrote: >> 2016-08-20 10:27 GMT+02:00 : >> >> On 2016-08-20 08:21, pgsql-performance-owner@postgresql.org wrote: >> >> Welcome to the pgsql-performance mailing list! >> Your password at PostgreSQL Mailing Lists is >> >> x8DiA6 >> >> To leave this mailing list, send the following command in the >> body >> of a message to majordomo@postgresql.org: >> >> approve x8DiA6 unsubscribe pgsql-performance >> debasis.moharana@ipathsolutions.co.in >> >> This command will work even if your address changes. For that >> reason, >> among others, it is important that you keep a copy of this >> message. >> >> To post a message to the mailing list, send it to >> pgsql-performance@postgresql.org >> >> If you need help or have questions about the mailing list, please >> contact the people who manage the list by sending a message to >> pgsql-performance-owner@postgresql.org >> >> You can manage your subscription by visiting the following WWW >> location: >> >> > > [1] >> [1] >> >> [2] >> >> [1]> >> Dear Sir/Mam, >> >> I have a PostgreSQL 9.5 instance running on Windows 8 machine with >> 4GB of RAM.This server is mainly used for inserting/updating large >> amounts of data via copy/insert/update commands, and seldom for >> running select queries. >> >> Here are the relevant configuration parameters I changed: >> >> max_connections = 100 >> shared_buffers = 512MB >> effective_cache_size = 3GB >> work_mem = 12233kB >> maintenance_work_mem = 256MB >> min_wal_size = 1GB max_wal_size = 2GB >> checkpoint_completion_target = 0.7 >> wal_buffers = 16MB >> default_statistics_target = 100 >> >> After setting in postgresql.conf. I run the select query to fetch >> large amount of record of 29000 in postgresql but it takes 10.3 >> seconds but the same query takes 2 seconds for execution in MSSQL. >> >> So my query is how to improve the perfermance in postgresql. >> >> hi >> >> please, send execution plan of slow query >> >> https://www.postgresql.org/docs/current/static/sql-explain.html >> [2] [3] >> [3] >> [3] >> https://explain.depesz.com/ [3] [4] [4] [4] >> >> p.s. Did you do VACUUM and ANALYZE on database? >> >> Regards >> >> Pavel >> >> Regards, >> Debasis Moharana >> .NET Software Developer >> >> -- >> Sent via pgsql-performance mailing list >> (pgsql-performance@postgresql.org) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-performance [4] [2] [1] >> [2] >> >> Links: >> ------ >> [1] >> >> > https://lists.postgresql.org/mj/mj_wwwusr/domain=postgresql.org/debasis.moharana%40ipathsolutions.co.in >> [1] >> [1] >> [2] >> [2] http://www.postgresql.org/mailpref/pgsql-performance [4] [2] >> [1] >> [3] >> https://www.postgresql.org/docs/current/static/sql-explain.html [2] >> [3] >> [3] >> [4] https://explain.depesz.com/ [3] [4] [4] >> >> Hi, >> >> Please check the execution plan details >> >> Execution Query is = EXPLAIN (ANALYZE, BUFFERS) select * from >> tblPurchaseOrderstock cross join tblPurchaseOrderInfo; >> >> "Nested Loop (cost=0.00..507.51 rows=39593 width=224) (actual >> time=0.032..13.026 rows=39593 loops=1)" >> " Buffers: shared read=8" >> " I/O Timings: read=0.058" >> " -> Seq Scan on tblpurchaseorderstock (cost=0.00..7.89 >> rows=289 >> width=95) (actual time=0.014..0.082 rows=289 loops=1)" >> " Buffers: shared read=5" >> " I/O Timings: read=0.040" >> " -> Materialize (cost=0.00..5.05 rows=137 width=129) (actual >> time=0.000..0.006 rows=137 loops=289)" >> " Buffers: shared read=3" >> " I/O Timings: read=0.019" >> " -> Seq Scan on tblpurchaseorderinfo (cost=0.00..4.37 >> rows=137 width=129) (actual time=0.011..0.035 rows=137 loops=1)" >> " Buffers: shared read=3" >> " I/O Timings: read=0.019" >> "Planning time: 56.052 ms" >> "Execution time: 14.038 ms" >> >> It is same query? It needs only 14ms >> >> Regards >> >> Pavel >> >> Regards, >> Debasis Moharana >> >> Links: >> ------ >> [1] http://www.postgresql.org/mailpref/pgsql-performance [4] [2] >> [2] >> >> > https://lists.postgresql.org/mj/mj_wwwusr/domain=postgresql.org/debasis.moharana%40ipathsolutions.co.in >> [1] >> [1] >> [3] >> https://www.postgresql.org/docs/current/static/sql-explain.html [2] >> [3] >> [4] https://explain.depesz.com/ [3] [4] >> >> Hi, >> >> Yes you right.But it will take more time(10.3 sec.) Plase check >> the >> snap. >> >> The real time you can see in EXPLAIN ANALYZE ... output. The some >> strange time what you can see in PgAdmin can be based on >> >> a) PgAdmin issue - pgAdmin is relativly slow client due slow >> formatting - the time of processing in your application can be >> pretty >> better, try to check another client >> >> b) there can be some network issues - the problem is in passing >> data >> from server to client >> >> but probably variant is @a - pgAdmin is not good for benchmarking - >> use "psql" console instead. >> >> Pavel >> >> Can you please tell me what we need to setup so that it will take >> the actual time. >> >> Regards, >> Debasis Moharana >> >> Links: >> ------ >> [1] >> > https://lists.postgresql.org/mj/mj_wwwusr/domain=postgresql.org/debasis.moharana%40ipathsolutions.co.in >> [1] >> [2] http://www.postgresql.org/mailpref/pgsql-performance [4] >> [3] https://www.postgresql.org/docs/current/static/sql-explain.html >> [2] >> [4] https://explain.depesz.com/ [3] > > Hi, > > Actually i am fresher on this.So want to connect my application with > postgresql instead of MSSQL. > > If we are using psql console for executing the query then it will > faster according to you. > But what is the other option to use instead of pgadmin. > > Can you give me some link for reference. > > > https://wiki.postgresql.org/wiki/Community_Guide_to_PostgreSQL_GUI_Tools > [5] > http://www.sqlmanager.net/en/products/postgresql/manager [6] > > Regards > > Pavel > >> Regards, >> Debasis Moharana > > > > Links: > ------ > [1] > https://lists.postgresql.org/mj/mj_wwwusr/domain=postgresql.org/debasis.moharana%40ipathsolutions.co.in > [2] https://www.postgresql.org/docs/current/static/sql-explain.html > [3] https://explain.depesz.com/ > [4] http://www.postgresql.org/mailpref/pgsql-performance > [5] > https://wiki.postgresql.org/wiki/Community_Guide_to_PostgreSQL_GUI_Tools > [6] http://www.sqlmanager.net/en/products/postgresql/manager Hi, Thanks a lot. its now faster execution of query.Let me check all the things in postgresql.If i have any further query then i will get back to you. Regards, Debasis Moharana -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance