2016-08-20 13:31 GMT+02:00 <debasis.mohar...@ipathsolutions.co.in>: > On 2016-08-20 08:58, Pavel Stehule wrote: > >> 2016-08-20 10:27 GMT+02:00 <debasis.mohar...@ipathsolutions.co.in>: >> >> On 2016-08-20 08:21, pgsql-performance-ow...@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 majord...@postgresql.org: >>>> >>>> approve x8DiA6 unsubscribe pgsql-performance >>>> debasis.mohar...@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-ow...@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" >
It is same query? It needs only 14ms Regards Pavel > > Regards, > Debasis Moharana >
