MIME-Version: 1.0
Content-Type: text/plain; charset=US-ASCII;
 format=flowed
Content-Transfer-Encoding: 7bit
Date: Sat, 20 Aug 2016 11:31:32 +0000
From: debasis.moharana@ipathsolutions.co.in
To: Pavel Stehule <pavel.stehule@gmail.com>
Cc: pgsql-performance@postgresql.org, pgsql-performance-owner@postgresql.org
Subject: Re: pgsql-performance issue
In-Reply-To: 
 <CAFj8pRD+2=hDxdmFU==V=dwfjZ3Qx2Y-EzBm3N7o3CU_Dw=-Rw@mail.gmail.com>
References: <cf406bd128ace3d719173585dc5b6a79@ipathsolutions.co.in>
 <CAFj8pRD+2=hDxdmFU==V=dwfjZ3Qx2Y-EzBm3N7o3CU_Dw=-Rw@mail.gmail.com>
Message-ID: <701d07526dd15e94a38c857b4f459194@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: <http://archives.postgresql.org/pgsql-performance>
List-Help: <mailto:majordomo@postgresql.org?body=help>
List-ID: <pgsql-performance.postgresql.org>
List-Owner: <mailto:pgsql-performance-owner@postgresql.org>
List-Post: <mailto:pgsql-performance@postgresql.org>
List-Subscribe: <mailto:majordomo@postgresql.org?body=sub%20pgsql-performance>
List-Unsubscribe: 
 <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
X-Mailing-List: pgsql-performance
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

On 2016-08-20 08:58, Pavel Stehule wrote:
> 2016-08-20 10:27 GMT+02:00 <debasis.moharana@ipathsolutions.co.in>:
> 
>> 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:
>>> 
>>> 
>> 
> <https://lists.postgresql.org/mj/mj_wwwusr/domain=postgresql.org/debasis.moharana%40ipathsolutions.co.in
>>> [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 [3]
> https://explain.depesz.com/ [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 [2]
> 
> 
> 
> Links:
> ------
> [1]
> https://lists.postgresql.org/mj/mj_wwwusr/domain=postgresql.org/debasis.moharana%40ipathsolutions.co.in
> [2] http://www.postgresql.org/mailpref/pgsql-performance
> [3] https://www.postgresql.org/docs/current/static/sql-explain.html
> [4] https://explain.depesz.com/


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"

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