Обсуждение: Slow query
Hi,
I have a view which joins multiple tables to give me a result. It takes more than a minute to give me the result on psql prompt when I select all the data from that view.
The single CPU which is used to run this query is utilized 100%.Even if I fire a count(*) it takes 10 Sec. I wanted to know if there is anything we can do to speedup this query below 1 sec.
CREATE OR REPLACE VIEW wh_rbtmapdetails_test AS
SELECT map_tab.binarymapid,
map_tab.whsongid,
map_tab.binaryid,
map_tab.previewbinaryid,
map_tab.created,
map_tab.createdby,
map_tab.lastmodified,
map_tab.lastmodifiedby,
map_tab.stateon,
map_tab.stateby,
map_tab.statename,
map_tab.statereason,
bin_tab.filepath,
bin_tab.filesizeinbytes,
bin_tab.contenttypename,
bin_tab.fileextension,
bin_tab.filepath AS previewfilepath,
bin_tab.contenttypename AS previewcontenttypename,
bin_tab.fileextension AS previewfileextension,
bin_tab.lastmodified AS binlastmodified,
bin_tab.created AS bincreated,
md_tab.whsongname,
md_tab.whsongnamerx,
md_tab.whmoviename,
md_tab.whmovienamerx,
md_tab.languagename,
md_tab.contentproviderid,
md_tab.rightsbodyid,
md_tab.labelid,
md_tab.isrc,
md_tab.keywords,
md_tab.cpcontentid,
md_tab.songreleasedate,
md_tab.moviereleasedate,
md_tab.actor,
md_tab.singer,
md_tab.musicdirector,
md_tab.moviedirector,
md_tab.movieproducer,
md_tab.rightsbodyname,
md_tab.labelname,
md_tab.contentprovidername,
md_tab.categoryid,
md_tab.categoryname,
md_tab.subcategoryname,
md_tab.genrename,
md_tab.promocode,
md_tab.cptransferdate,
md_tab.lastmodified AS mdlastmodified,
md_tab.created AS mdcreated,
map_tab.holdercontentsubtypeid,
NULL::unknown AS holdercontentsubtypename,
md_tab.statename AS metadatastatename,
md_tab.statereason AS metadatastatereason,
bin_tab.statename AS binarystatename,
bin_tab.statereason AS binarystatereason,
md_tab.isbranded,
md_tab.brandname,
md_tab.aliaspromocode,
md_tab.songreleaseyear,
md_tab.moviereleaseyear,
md_tab.songid,
map_tab.holderstartdate,
map_tab.holderenddate,
md_tab.comments,
md_tab.iprs,
md_tab.lyricist,
md_tab.workssociety,
md_tab.publisher,
md_tab.iswc,
songartwork.stateon as artworkstateon,
'' AS airtelvcode,
'' AS airtelccode
FROM songbinarymap map_tab
inner join wh_songmetadatadetails_sukruth md_tab on map_tab.whsongid = md_tab.whsongid
inner join songbinarywarehouse_rbt bin_tab on map_tab.binaryid = bin_tab.binaryid
inner join contentprovider cp_tab on md_tab.contentproviderid = cp_tab.contentproviderid
left join songartwork on songartwork.whsongid = map_tab.whsongid
WHERE cp_tab.hide <> 1;
--##############################################################################################################################
atlantisindia=# Select count(*) from wh_songmetadatadetails_sukruth;
count
---------
2756891
atlantisindia=# Select count(*) from songbinarywarehouse_rbt;
count
---------
3507188
atlantisindia=# Select count(*) from contentprovider;
count
-------
446
atlantisindia=# Select count(*) from songartwork;
count
--------
292457
atlantisindia=# Select count(*) from songbinarymap;
count
---------
3460677
Objects used in the query:
wh_songmetadatadetails_sukruth -- view
songbinarywarehouse_rbt -- Table
songartwork -- Table
songbinarymap -- Table
/*
CREATE OR REPLACE VIEW wh_songmetadatadetails_sukruth AS
SELECT md_tab.whsongid,
md_tab.whsongname,
md_tab.whsongnamerx,
md_tab.whmoviename,
md_tab.whmovienamerx,
md_tab.languagename,
md_tab.contentproviderid,
md_tab.rightsbodyid,
md_tab.labelid,
md_tab.isrc,
md_tab.songreleasedate,
md_tab.moviereleasedate,
md_tab.actor,
md_tab.singer,
md_tab.musicdirector,
md_tab.moviedirector,
md_tab.movieproducer,
md_tab.keywords,
md_tab.cpcontentid,
md_tab.created,
md_tab.createdby,
md_tab.lastmodified,
md_tab.lastmodifiedby,
md_tab.stateon,
md_tab.stateby,
md_tab.statename,
md_tab.statereason,
md_tab.comments,
md_tab.oldcategoryname,
md_tab.oldsubcategoryname,
md_tab.oldgenrename,
rightsbody.rightsbodyname,
label.labelname,
cp_tab.contentprovidername,
cp_tab.alias_contentproviderid,
md_tab.promocode,
md_tab.isbranded,
md_tab.brandname,
'now'::text::timestamp without time zone - md_tab.stateon AS sysdatestateondiff,
md_tab.aliaspromocode,
md_tab.songreleaseyear,
md_tab.moviereleaseyear,
md_tab.costrc,
md_tab.categoryid,
cpcategoryforselect.categoryname,
md_tab.subcategoryname,
md_tab.genrename,
md_tab.metadatacorrection,
md_tab.songid,
songartwork.artworkbinaryid_1,
md_tab.iprs,
md_tab.lyricist,
md_tab.workssociety,
md_tab.publisher,
md_tab.iswc,
md_tab.umdbid,
md_tab.umdbmodified,
md_tab.cptransferdate,
CAST('' as varchar(1)) airtelvcode,
cast('' as varchar(1)) airtelccode
FROM songmetadatawarehouse md_tab
inner join contentprovider cp_tab on md_tab.contentproviderid::text = cp_tab.contentproviderid::text
left join songartwork on songartwork.whsongid::text = md_tab.whsongid::text
left join cpcategoryforselect on cpcategoryforselect.categoryid::text = md_tab.categoryid::text
left join label on label.labelid::text = md_tab.labelid::text
left join rightsbody on rightsbody.rightsbodyid::text = md_tab.rightsbodyid::text
WHERE cp_tab.hide <> 1;
*/
--########################################### Exolain Plan ######################################################
explain analyze select * from wh_rbtmapdetails_test;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------
Hash Left Join (cost=597862.86..1042112.62 rows=3322444 width=1714) (actual time=11015.390..29802.769 rows=2615033 loops=1)
Hash Cond: ((map_tab.whsongid)::text = (songartwork.whsongid)::text)
-> Hash Join (cost=581788.58..976448.68 rows=3322444 width=1706) (actual time=10828.076..25597.518 rows=2615033 loops=1)
Hash Cond: ((md_tab.contentproviderid)::text = (cp_tab.contentproviderid)::text)
-> Hash Left Join (cost=581761.54..930481.44 rows=3390870 width=1734) (actual time=10827.746..24577.167 rows=2615033 loops=1)
Hash Cond: ((md_tab.categoryid)::text = (cpcategoryforselect.categoryid)::text)
-> Hash Join (cost=581759.59..883858.42 rows=3390870 width=1702) (actual time=10827.690..23549.201 rows=2615033 loops=1)
Hash Cond: ((map_tab.whsongid)::text = (md_tab.whsongid)::text)
-> Hash Join (cost=192309.86..425892.93 rows=3460705 width=538) (actual time=2883.529..10130.740 rows=2802042 loops=1)
Hash Cond: ((bin_tab.binaryid)::text = (map_tab.binaryid)::text)
-> Seq Scan on songbinarywarehouse_rbt bin_tab (cost=0.00..159538.54 rows=3505554 width=135) (actual time=0.002..362.037 rows=3507188 loops
=1)
-> Hash (cost=149051.05..149051.05 rows=3460705 width=436) (actual time=2881.792..2881.792 rows=3460677 loops=1)
Buckets: 524288 Batches: 1 Memory Usage: 888911kB
-> Seq Scan on songbinarymap map_tab (cost=0.00..149051.05 rows=3460705 width=436) (actual time=0.004..1129.036 rows=3460677 loops=1)
-> Hash (cost=353389.46..353389.46 rows=2884822 width=1197) (actual time=7941.814..7941.814 rows=2756891 loops=1)
Buckets: 524288 Batches: 1 Memory Usage: 1339511kB
-> Hash Left Join (cost=307.32..353389.46 rows=2884822 width=1197) (actual time=2.585..4859.733 rows=2756891 loops=1)
Hash Cond: ((md_tab.rightsbodyid)::text = (rightsbody.rightsbodyid)::text)
-> Hash Left Join (cost=234.82..313650.65 rows=2884822 width=1183) (actual time=1.978..3756.064 rows=2756891 loops=1)
Hash Cond: ((md_tab.labelid)::text = (label.labelid)::text)
-> Hash Join (cost=27.04..262958.49 rows=2884822 width=1167) (actual time=0.257..2703.354 rows=2756891 loops=1)
Hash Cond: ((md_tab.contentproviderid)::text = (cp_tab_1.contentproviderid)::text)
-> Seq Scan on songmetadatawarehouse md_tab (cost=0.00..223042.35 rows=2944235 width=1125) (actual time=0.002..363.384 ro
ws=2944240 loops=1)
-> Hash (cost=21.57..21.57 rows=437 width=42) (actual time=0.239..0.239 rows=431 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 32kB
-> Seq Scan on contentprovider cp_tab_1 (cost=0.00..21.57 rows=437 width=42) (actual time=0.003..0.141 rows=431 loo
ps=1)
Filter: (hide <> 1)
Rows Removed by Filter: 15
-> Hash (cost=160.68..160.68 rows=3768 width=49) (actual time=1.708..1.708 rows=3768 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 301kB
-> Seq Scan on label (cost=0.00..160.68 rows=3768 width=49) (actual time=0.003..0.797 rows=3768 loops=1)
-> Hash (cost=55.00..55.00 rows=1400 width=47) (actual time=0.596..0.596 rows=1400 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 109kB
-> Seq Scan on rightsbody (cost=0.00..55.00 rows=1400 width=47) (actual time=0.002..0.258 rows=1400 loops=1)
-> Hash (cost=1.70..1.70 rows=20 width=65) (actual time=0.033..0.033 rows=20 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 2kB
-> Subquery Scan on cpcategoryforselect (cost=0.00..1.70 rows=20 width=65) (actual time=0.013..0.025 rows=20 loops=1)
-> Seq Scan on cpcategory (cost=0.00..1.50 rows=20 width=53) (actual time=0.012..0.022 rows=20 loops=1)
-> Hash (cost=21.57..21.57 rows=437 width=28) (actual time=0.306..0.306 rows=431 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 26kB
-> Seq Scan on contentprovider cp_tab (cost=0.00..21.57 rows=437 width=28) (actual time=0.027..0.212 rows=431 loops=1)
Filter: (hide <> 1)
Rows Removed by Filter: 15
-> Hash (cost=12418.57..12418.57 rows=292457 width=41) (actual time=187.181..187.181 rows=292457 loops=1)
Buckets: 32768 Batches: 1 Memory Usage: 20849kB
-> Seq Scan on songartwork (cost=0.00..12418.57 rows=292457 width=41) (actual time=0.024..102.322 rows=292457 loops=1)
Total runtime: 29966.975 ms
(47 rows)
--##############################################################################################################################
System Info:
# lscpu
Architecture: x86_64
CPU op-mode(s): 32-bit, 64-bit
Byte Order: Little Endian
CPU(s): 16
On-line CPU(s) list: 0-15
Thread(s) per core: 2
Core(s) per socket: 4
Socket(s): 2
NUMA node(s): 2
Vendor ID: GenuineIntel
CPU family: 6
Model: 45
Stepping: 7
CPU MHz: 1200.000
BogoMIPS: 6599.09
Virtualization: VT-x
L1d cache: 32K
L1i cache: 32K
L2 cache: 256K
L3 cache: 10240K
NUMA node0 CPU(s): 0-3,8-11
NUMA node1 CPU(s): 4-7,12-15
--##############################################################################################################################
#free
total used free shared buffers cached
Mem: 65932076 34444056 31488020 0 307152 31947732
-/+ buffers/cache: 2189172 63742904
Swap: 33038328 352928 32685400
--##############################################################################################################################
[root@localhost ~]# top
top - 00:42:59 up 113 days, 8:58, 3 users, load average: 0.08, 0.02, 0.01
Tasks: 568 total, 2 running, 565 sleeping, 0 stopped, 1 zombie
Cpu(s): 5.9%us, 0.5%sy, 0.0%ni, 93.6%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st
Mem: 65932076k total, 40638080k used, 25293996k free, 311728k buffers
Swap: 33038328k total, 352844k used, 32685484k free, 35244776k cached
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
36725 postgres 20 0 17.0g 5.9g 3.5g R 99.8 9.4 0:35.91 postmaster
30038 oracle -2 0 19.1g 16m 14m S 1.3 0.0 2:29.59 oracle
41270 root 20 0 15436 1640 956 R 0.7 0.0 0:00.12 top
30141 oracle 20 0 19.1g 16m 14m S 0.3 0.0 0:14.72 oracle
1 root 20 0 19356 796 584 S 0.0 0.0 3:01.96 init
2 root 20 0 0 0 0 S 0.0 0.0 0:04.82 kthreadd
3 root RT 0 0 0 0 S 0.0 0.0 0:15.46 migration/0
4 root 20 0 0 0 0 S 0.0 0.0 0:16.53 ksoftirqd/0
5 root RT 0 0 0 0 S 0.0 0.0 0:00.00 migration/0
6 root RT 0 0 0 0 S 0.0 0.0 0:09.89 watchdog/0
7 root RT 0 0 0 0 S 0.0 0.0 0:19.86 migration/1
8 root RT 0 0 0 0 S 0.0 0.0 0:00.00 migration/1
9 root 20 0 0 0 0 S 0.0 0.0 0:21.03 ksoftirqd/1
10 root RT 0 0 0 0 S 0.0 0.0 0:08.66 watchdog/1
11 root RT 0 0 0 0 S 0.0 0.0 0:05.25 migration/2
12 root RT 0 0 0 0 S 0.0 0.0 0:00.00 migration/2
13 root 20 0 0 0 0 S 0.0 0.0 0:04.83 ksoftirqd/2
14 root RT 0 0 0 0 S 0.0 0.0 0:08.87 watchdog/2
15 root RT 0 0 0 0 S 0.0 0.0 0:04.10 migration/3
16 root RT 0 0 0 0 S 0.0 0.0 0:00.00 migration/3
17 root 20 0 0 0 0 S 0.0 0.0 0:03.56 ksoftirqd/3
18 root RT 0 0 0 0 S 0.0 0.0 0:08.68 watchdog/3
19 root RT 0 0 0 0 S 0.0 0.0 0:17.71 migration/4
20 root RT 0 0 0 0 S 0.0 0.0 0:00.00 migration/4
21 root 20 0 0 0 0 S 0.0 0.0 0:30.47 ksoftirqd/4
22 root RT 0 0 0 0 S 0.0 0.0 0:11.47 watchdog/4
23 root RT 0 0 0 0 S 0.0 0.0 0:25.19 migration/5
24 root RT 0 0 0 0 S 0.0 0.0 0:00.00 migration/5
25 root 20 0 0 0 0 S 0.0 0.0 0:35.14 ksoftirqd/5
26 root RT 0 0 0 0 S 0.0 0.0 0:10.34 watchdog/5
27 root RT 0 0 0 0 S 0.0 0.0 0:06.08 migration/6
28 root RT 0 0 0 0 S 0.0 0.0 0:00.00 migration/6
29 root 20 0 0 0 0 S 0.0 0.0 0:14.71 ksoftirqd/6
30 root RT 0 0 0 0 S 0.0 0.0 0:09.54 watchdog/6
31 root RT 0 0 0 0 S 0.0 0.0 0:02.36 migration/7
32 root RT 0 0 0 0 S 0.0 0.0 0:00.00 migration/7
33 root 20 0 0 0 0 S 0.0 0.0 0:06.70 ksoftirqd/7
--##############################################################################################################################
[root@localhost ~]# vmstat -a 1
procs -----------memory---------- ---swap-- -----io---- --system-- -----cpu-----
r b swpd free inact active si so bi bo in cs us sy id wa st
1 0 352844 19137128 16133388 29410720 0 0 2 8 0 0 0 0 100 0 0
1 0 352844 20462344 16133388 28085988 0 0 0 16 2524 19813 6 1 92 0 0
1 0 352844 20984756 16133412 27564440 0 0 0 32 2276 2711 4 3 94 0 0
1 0 352844 20984524 16133412 27564760 0 0 0 0 2197 2572 6 0 94 0 0
2 0 352844 20984544 16133412 27564760 0 0 0 20 2231 2634 6 0 94 0 0
3 0 352844 20983176 16133416 27564752 0 0 0 216 2342 2690 6 0 93 0 0
2 0 352844 20983308 16133416 27564808 0 0 0 0 2227 2634 6 0 94 0 0
1 0 352844 20983556 16133416 27564808 0 0 0 0 2227 2597 6 0 94 0 0
1 0 352844 20983664 16133416 27564808 0 0 0 32 2263 2643 6 0 93 0 0
1 0 352844 20983400 16133416 27564920 0 0 0 4 2217 2616 6 0 94 0 0
2 0 352844 20987048 16133428 27560248 0 0 0 108 3092 3163 7 1 92 0 0
3 0 352844 20987172 16133416 27560248 0 0 0 32 2316 2716 6 0 94 0 0
2 0 352844 20987188 16133412 27559624 0 0 0 0 2226 2651 6 0 94 0 0
1 0 352844 20987304 16133412 27559624 0 0 0 0 2198 2614 6 0 94 0 0
1 0 352844 20983808 16133416 27563632 0 0 0 32 2422 2839 7 0 93 0 0
1 0 352844 20987420 16133416 27559956 0 0 0 0 2307 2638 6 0 94 0 0
3 0 352844 20987712 16133416 27559700 0 0 0 140 2278 2704 6 0 94 0 0
2 0 352844 20987712 16133412 27559752 0 0 0 88 2306 2706 6 0 94 0 0
2 0 352844 20987844 16133416 27559660 0 0 0 0 2233 2600 6 0 94 0 0
1 0 352844 20988000 16133416 27559660 0 0 0 0 2202 2582 6 0 94 0 0
1 0 352844 20988124 16133416 27559664 0 0 0 44 2278 2654 6 0 94 0 0
1 0 352844 20988240 16133416 27559664 0 0 0 0 2193 2571 6 0 94 0 0
2 0 352844 20988248 16133416 27559664 0 0 0 64 2230 2656 6 0 94 0 0
3 0 352844 20988356 16133416 27559668 0 0 0 32 2275 2637 6 0 94 0 0
2 0 352844 20988668 16133416 27559668 0 0 0 12 2233 2664 6 0 94 0 0
1 0 352844 20988660 16133420 27559672 0 0 0 0 2238 2614 6 0 94 0 0
1 0 352844 20988784 16133420 27559672 0 0 0 44 2243 2658 6 0 94 0 0
2 0 352844 20988040 16133420 27560036 0 0 0 12 2256 2664 6 0 93 0 0
1 0 352844 20987312 16133416 27560752 0 0 0 88 2314 2706 6 0 93 0 0
1 0 352844 20986600 16133468 27561040 0 0 0 64 2343 2688 6 0 94 0 0
2 0 352844 20987212 16133460 27560668 0 0 0 12 2255 2658 6 0 94 0 0
2 0 352844 20987336 16133460 27560724 0 0 0 12 2211 2593 6 0 94 0 0
2 0 352844 20987352 16133464 27560604 0 0 0 32 2295 2732 6 0 94 0 0
1 0 352844 20987148 16133464 27560832 0 0 0 0 3041 3197 7 1 92 0 0
1 0 352844 20986848 16133464 27560728 0 0 0 108 2267 2670 6 0 94 0 0
1 0 352844 20986948 16133468 27560608 0 0 0 32 2261 2652 6 0 94 0 0
2 0 352844 20987064 16133468 27560608 0 0 0 0 2208 2610 6 0 94 0 0
2 0 352844 20987064 16133468 27560608 0 0 0 0 2192 2559 6 0 94 0 0
1 0 352844 20987236 16133468 27560608 0 0 0 32 2256 2630 6 0 94 0 0
1 0 352844 20987368 16133468 27560608 0 0 0 0 2223 2624 6 0 94 0 0
1 0 352844 20980036 16133552 27565904 0 0 0 208 2457 2855 7 0 93 0 0
--##############################################################################################################################
[root@localhost ~]# iostat -xn 1
Linux 2.6.32-358.el6.x86_64 (localhost.localdomain) 03/07/2014 _x86_64_ (16 CPU)
Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz avgqu-sz await svctm %util
sda 0.05 28.93 0.44 4.63 65.60 268.49 65.88 0.04 8.28 0.32 0.16
dm-0 0.00 0.00 0.05 0.09 0.37 0.74 8.00 0.00 6.07 0.16 0.00
dm-1 0.00 0.00 0.08 18.97 4.65 151.78 8.21 0.24 12.71 0.06 0.11
dm-2 0.00 0.00 0.09 5.97 11.55 47.77 9.78 0.43 71.51 0.05 0.03
dm-3 0.00 0.00 0.27 8.52 49.04 68.19 13.33 0.03 3.81 0.05 0.04
Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz avgqu-sz await svctm %util
sda 0.00 12.00 0.00 14.00 0.00 208.00 14.86 0.00 0.00 0.00 0.00
dm-0 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
dm-1 0.00 0.00 0.00 26.00 0.00 208.00 8.00 0.00 0.00 0.00 0.00
dm-2 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
dm-3 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz avgqu-sz await svctm %util
sda 0.00 0.00 0.00 1.00 0.00 8.00 8.00 0.00 3.00 3.00 0.30
dm-0 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
dm-1 0.00 0.00 0.00 1.00 0.00 8.00 8.00 0.00 3.00 3.00 0.30
dm-2 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
dm-3 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz avgqu-sz await svctm %util
sda 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
dm-0 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
dm-1 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
dm-2 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
dm-3 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz avgqu-sz await svctm %util
sda 0.00 24.00 0.00 11.00 0.00 280.00 25.45 0.00 0.00 0.00 0.00
dm-0 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
dm-1 0.00 0.00 0.00 35.00 0.00 280.00 8.00 0.00 0.00 0.00 0.00
dm-2 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
dm-3 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz avgqu-sz await svctm %util
sda 0.00 0.00 0.00 1.00 0.00 8.00 8.00 0.00 3.00 3.00 0.30
dm-0 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
dm-1 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
dm-2 0.00 0.00 0.00 1.00 0.00 8.00 8.00 0.00 3.00 3.00 0.30
dm-3 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz avgqu-sz await svctm %util
sda 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
dm-0 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
dm-1 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
dm-2 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
dm-3 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz avgqu-sz await svctm %util
sda 0.00 6.00 0.00 5.00 0.00 88.00 17.60 0.00 0.00 0.00 0.00
dm-0 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
dm-1 0.00 0.00 0.00 8.00 0.00 64.00 8.00 0.00 0.00 0.00 0.00
dm-2 0.00 0.00 0.00 3.00 0.00 24.00 8.00 0.00 0.00 0.00 0.00
dm-3 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz avgqu-sz await svctm %util
sda 0.00 1.00 0.00 1.00 0.00 16.00 16.00 0.00 0.00 0.00 0.00
dm-0 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
dm-1 0.00 0.00 0.00 2.00 0.00 16.00 8.00 0.00 0.00 0.00 0.00
dm-2 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
dm-3 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz avgqu-sz await svctm %util
sda 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
dm-0 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
dm-1 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
dm-2 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
dm-3 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
Thanks,
Bikram
DISCLAIMER: The information in this message is confidential and may be legally privileged. It is intended solely for the addressee. Access to this message by anyone else is unauthorized. If you are not the intended recipient, any disclosure, copying, or distribution of the message, or any action or omission taken by you in reliance on it, is prohibited and may be unlawful. Please immediately contact the sender if you have received this message in error. Further, this e-mail may contain viruses and all reasonable precaution to minimize the risk arising there from is taken by OnMobile. OnMobile is not liable for any damage sustained by you as a result of any virus in this e-mail. All applicable virus checks should be carried out by you before opening this e-mail or any attachment thereto.
Thank you - OnMobile Global Limited.
Bikram Kesari Naik wrote > Hi, > > I have a view which joins multiple tables to give me a result. It takes > more than a minute to give me the result on psql prompt when I select all > the data from that view. > The single CPU which is used to run this query is utilized 100%.Even if I > fire a count(*) it takes 10 Sec. I wanted to know if there is anything we > can do to speedup this query below 1 sec. In all likelihood you need to index your foreign keys, and possibly other fields, but as you haven't provided table and index definitions it is hard to say for sure. Idepeing on how many rows are hidden I'm not sure an unqualified query on this view can run in 1/60th the time even with indexes present - the sequential scans are efficient if the proportion of the tables being returned is high. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Slow-query-tp5795077p5795079.html Sent from the PostgreSQL - performance mailing list archive at Nabble.com.
Hi David, We have indexes on all the columns which are used in the where clause and these tables are linked by foreign key constraint. Thanks, Bikram -----Original Message----- From: pgsql-performance-owner@postgresql.org [mailto:pgsql-performance-owner@postgresql.org] On Behalf Of David Johnston Sent: Friday, March 07, 2014 11:53 AM To: pgsql-performance@postgresql.org Subject: Re: [PERFORM] Slow query Bikram Kesari Naik wrote > Hi, > > I have a view which joins multiple tables to give me a result. It > takes more than a minute to give me the result on psql prompt when I > select all the data from that view. > The single CPU which is used to run this query is utilized 100%.Even > if I fire a count(*) it takes 10 Sec. I wanted to know if there is > anything we can do to speedup this query below 1 sec. In all likelihood you need to index your foreign keys, and possibly other fields, but as you haven't provided table and indexdefinitions it is hard to say for sure. Idepeing on how many rows are hidden I'm not sure an unqualified query on this view can run in 1/60th the time even withindexes present - the sequential scans are efficient if the proportion of the tables being returned is high. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Slow-query-tp5795077p5795079.html Sent from the PostgreSQL - performance mailing list archive at Nabble.com. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance ________________________________ DISCLAIMER: The information in this message is confidential and may be legally privileged. It is intended solely for theaddressee. Access to this message by anyone else is unauthorized. If you are not the intended recipient, any disclosure,copying, or distribution of the message, or any action or omission taken by you in reliance on it, is prohibitedand may be unlawful. Please immediately contact the sender if you have received this message in error. Further,this e-mail may contain viruses and all reasonable precaution to minimize the risk arising there from is taken byOnMobile. OnMobile is not liable for any damage sustained by you as a result of any virus in this e-mail. All applicablevirus checks should be carried out by you before opening this e-mail or any attachment thereto. Thank you - OnMobile Global Limited.
Bikram Kesari Naik wrote > Hi David, > > We have indexes on all the columns which are used in the where clause and > these tables are linked by foreign key constraint. > > > Thanks, > Bikram > > -----Original Message----- > From: > pgsql-performance-owner@ > [mailto: > pgsql-performance-owner@ > ] On Behalf Of David Johnston > Sent: Friday, March 07, 2014 11:53 AM > To: > pgsql-performance@ > Subject: Re: [PERFORM] Slow query > > Bikram Kesari Naik wrote >> Hi, >> >> I have a view which joins multiple tables to give me a result. It >> takes more than a minute to give me the result on psql prompt when I >> select all the data from that view. >> The single CPU which is used to run this query is utilized 100%.Even >> if I fire a count(*) it takes 10 Sec. I wanted to know if there is >> anything we can do to speedup this query below 1 sec. > > In all likelihood you need to index your foreign keys, and possibly other > fields, but as you haven't provided table and index definitions it is hard > to say for sure. > > Idepeing on how many rows are hidden I'm not sure an unqualified query on > this view can run in 1/60th the time even with indexes present - the > sequential scans are efficient if the proportion of the tables being > returned is high. > > David J. Read these. https://wiki.postgresql.org/wiki/Using_EXPLAIN https://wiki.postgresql.org/wiki/SlowQueryQuestions And note, while the FK thing is likely not relevant in this situation defining a constraint does not cause an index to be created. Depending on your usage patterns defining those indexes can be helpful. One last thought: not only are your row counts high but it seems like your row sizes may also be large due to them containing binary content. You likely need to take a different approach to solving whatever unspecified problem this query is intended to solve if you need sub-second performance. That all said the main area of improvement for this is system memory concerns so, as noted in the links above, play with that and see what happens. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Slow-query-tp5795077p5795086.html Sent from the PostgreSQL - performance mailing list archive at Nabble.com.