Re: pgsql-performance issue

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