Re: pgsql-performance issue
От | debasis.moharana@ipathsolutions.co.in |
---|---|
Тема | Re: pgsql-performance issue |
Дата | |
Msg-id | 701d07526dd15e94a38c857b4f459194@ipathsolutions.co.in обсуждение исходный текст |
Ответ на | Re: pgsql-performance issue (Pavel Stehule <pavel.stehule@gmail.com>) |
Ответы |
Re: pgsql-performance issue
(Pavel Stehule <pavel.stehule@gmail.com>)
|
Список | pgsql-performance |
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
В списке pgsql-performance по дате отправления: