Hello Dinesh, You can try the EXPLAIN tool
psql=> EXPLAIN ANALYZE SELECT DISTINCT feature_id FROM evidence.point p INNER JOIN evidence.observation_evidence oe ON p.feature_id = oe.evd_feature_id WHERE p.domain_class_id IN (11) AND (p.modification_time > '2015-05-10 00:06:56.056 IST' OR oe.modification_time > '2015-05-10 00:06:56.056 IST') ORDER BY feature_id Then paste here the result. Thanks On Fri, Mar 3, 2017 at 5:29 PM, Dinesh Chandra 12108 < dinesh.chan...@cyient.com> wrote: > Dear Experts, > > > > I need your suggestions to resolve the performance issue reported on our > *PostgreSQL9.1* production database having 1.5 TB *Size*. I have observed > that, some select queries with order by clause are taking lot of time in > execution and forcing applications to give slow response. > > > > The configuration of database server is : > > > > Architecture: x86_64 > > CPU op-mode(s): 32-bit, 64-bit > > CPU’s : 8 > > Core(s) per socket: 4 > > Socket(s): 2 > > Model name: Intel(R) Xeon(R) CPU E5-2609 0 @ 2.40GHz > > > > *RAM* : 32 GB > > *SWAP* :8 Gb > > > > *Kernel parameter*: > > > > kernel.shmmax = 32212254720 > > kernel.shmall = 1073741824 > > > > > > Values of PostgreSQL.conf parameters are : > > > > shared_buffers = 10GB > > temp_buffers = 32MB > > work_mem = 512MB > > maintenance_work_mem = 2048MB > > max_files_per_process = 2000 > > checkpoint_segments = 200 > > max_wal_senders = 5 > > wal_buffers = -1 # min 32kB, -1 sets based on > shared_buffers > > > > > > *Queries taking lot of time are:* > > ================================== > > > > > > 2017-03-02 00:46:50 IST LOG: duration: 2492951.927 ms execute <unnamed>: > SELECT DISTINCT feature_id FROM evidence.point p INNER JOIN > evidence.observation_evidence oe ON p.feature_id = oe.evd_feature_id WHERE > p.domain_class_id IN (11) AND (p.modification_time > '2015-05-10 > 00:06:56.056 IST' OR oe.modification_time > '2015-05-10 00:06:56.056 IST') > ORDER BY feature_id > > > > > > 2017-03-02 01:05:16 IST LOG: duration: 516250.512 ms execute <unnamed>: > SELECT DISTINCT feature_id FROM evidence.point p INNER JOIN > evidence.observation_evidence oe ON p.feature_id = oe.evd_feature_id WHERE > p.domain_class_id IN (3) AND (p.modification_time > '2015-05-10 > 01:22:59.059 IST' OR oe.modification_time > '2015-05-10 01:22:59.059 IST') > ORDER BY feature_id > > > > > > *Top command output*: > > > > top - 15:13:15 up 66 days, 3:45, 8 users, load average: 1.84, 1.59, 1.57 > > Tasks: 830 total, 1 running, 828 sleeping, 0 stopped, 1 zombie > > Cpu(s): 3.4%us, 0.7%sy, 0.0%ni, 81.7%id, 14.2%wa, 0.0%hi, 0.0%si, > 0.0%st > > *Mem:* 32830016k total, *32142596k* used, *687420k* free, 77460k > buffers > > Swap: 8190972k total, 204196k used, 7986776k free, 27981268k cached > > > > PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND > > 30639 postgres 20 0 10.5g 4.7g 4.7g S 13.5 14.9 10:20.95 postgres > > 18185 postgres 20 0 10.5g 603m 596m S 4.9 1.9 2:51.16 postgres > > 16543 postgres 20 0 10.5g 2.8g 2.8g S 4.3 8.8 1:34.04 postgres > > 14710 postgres 20 0 10.5g 2.9g 2.9g S 3.9 9.2 1:20.84 postgres > > 1214 root 20 0 15668 1848 896 S 1.0 0.0 130:46.43 top > > 13462 postgres 20 0 10.5g 1.4g 1.3g S 1.0 4.3 0:25.56 postgres > > 20081 root 20 0 15668 1880 936 R 1.0 0.0 0:00.12 top > > 13478 postgres 20 0 10.5g 2.1g 2.1g S 0.7 6.9 0:56.43 postgres > > 41107 root 20 0 416m 10m 4892 S 0.7 0.0 305:25.71 pgadmin3 > > 2680 root 20 0 0 0 0 S 0.3 0.0 103:38.54 nfsiod > > 3558 root 20 0 13688 1100 992 S 0.3 0.0 45:00.36 gam_server > > 15576 root 20 0 0 0 0 S 0.3 0.0 0:01.16 flush-253:1 > > 18430 postgres 20 0 10.5g 18m 13m S 0.3 0.1 0:00.64 postgres > > 20083 postgres 20 0 105m 1852 1416 S 0.3 0.0 0:00.01 bash > > 24188 postgres 20 0 102m 1856 832 S 0.3 0.0 0:23.39 sshd > > 28250 postgres 20 0 156m 1292 528 S 0.3 0.0 0:46.86 postgres > > 1 root 20 0 19356 1188 996 S 0.0 0.0 0:05.00 init > > > > *Regards,* > > *Dinesh Chandra* > > *|Database administrator (Oracle/PostgreSQL)| * > > > > ------------------------------ > > DISCLAIMER: > > This email message is for the sole use of the intended recipient(s) and > may contain confidential and privileged information. Any unauthorized > review, use, disclosure or distribution is prohibited. If you are not the > intended recipient, please contact the sender by reply email and destroy > all copies of the original message. Check all attachments for viruses > before opening them. All views or opinions presented in this e-mail are > those of the author and may not reflect the opinion of Cyient or those of > our affiliates. >
