Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1WLrFn-0008Jd-DF for pgsql-performance@arkaria.postgresql.org; Fri, 07 Mar 2014 09:39:44 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1WLrFm-0007xk-TO for pgsql-performance@arkaria.postgresql.org; Fri, 07 Mar 2014 09:39:42 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1WLrFl-0007xe-V6 for pgsql-performance@postgresql.org; Fri, 07 Mar 2014 09:39:42 +0000 Received: from mail-pb0-x22d.google.com ([2607:f8b0:400e:c01::22d]) by magus.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1WLrFi-0006GH-Q6 for pgsql-performance@postgresql.org; Fri, 07 Mar 2014 09:39:41 +0000 Received: by mail-pb0-f45.google.com with SMTP id uo5so3929372pbc.32 for ; Fri, 07 Mar 2014 01:39:37 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113; h=content-type:mime-version:subject:from:in-reply-to:date:cc :content-transfer-encoding:message-id:references:to; bh=6AFPF+24S8PcZo3Nltklvij7Wqpy3x0y4hz/0sJ6m5k=; b=fqlFxfVpQCeheURAP/YmmJs+NadC3VUk5goYk3Hh5PQeRxBk+M6E6RulIaKFLY7bqn e15xhbBOerjG19GWYeSdmyTW4u1o6oEhJXVD7HfGx5uxr6J5Pt0zssNgNnQkTWepx3r1 AAZ6HbiDwnYnOzFvy/7z1DYnIZ/GUuhYVs/1S9Yjajt92r9fatXqmDPOyL872yU258Uv yTF768KH60wjFl52ExoLDKTSRnxsSM2sHxBOuTUyl9K5Q2F45L7wO0h+OfOt+GxjZEDm doJ3JGSBzmmLmHGBUywRcJHRmrF9VC6AQtXblVmxB4Q3uNZBD3Vhk1m6y5YC88bFblKN CFfw== X-Received: by 10.68.131.202 with SMTP id oo10mr20423804pbb.35.1394185177388; Fri, 07 Mar 2014 01:39:37 -0800 (PST) Received: from wgaybook.corp.wargaming.local (by1-office.wargaming.net. [178.172.239.240]) by mx.google.com with ESMTPSA id yk4sm27168592pbc.16.2014.03.07.01.39.34 for (version=TLSv1 cipher=ECDHE-RSA-RC4-SHA bits=128/128); Fri, 07 Mar 2014 01:39:36 -0800 (PST) Content-Type: text/plain; charset=iso-8859-1 Mime-Version: 1.0 (Mac OS X Mail 7.1 \(1827\)) Subject: Re: Query taking long time From: Evgeniy Shishkin In-Reply-To: <99FD8F29-2E89-4B8B-9C9F-1B2A61139064@cnio.es> Date: Fri, 7 Mar 2014 12:39:31 +0300 Cc: desmodemone , Venkata Balaji Nagothi , "pgsql-performance@postgresql.org list" Content-Transfer-Encoding: quoted-printable Message-Id: <7BCDBA11-D581-4B9F-97EC-3F6828F203AA@gmail.com> References: <3AE16087-DE09-4EE0-BDF7-F2DFC68CB6CB@cnio.es><42CEB1D5-629F-490A-A99B-2E15B60CA6D1@cnio.es><964F1009-8714-4B4C-8386-45D3D7A95738@cnio.es><6F33D2DE-AC10-4B2F-9C56-BBBC77D28BE0@cnio.es>

<99FD8F29-2E89-4B8B-9C9F-1B2A61139064@cnio.es> To: acanada X-Mailer: Apple Mail (2.1827) X-Pg-Spam-Score: -2.0 (--) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-performance Precedence: bulk Sender: pgsql-performance-owner@postgresql.org > Hello Mat, >=20 > Setting enable_bitmapscan to off doesn't really helps. It gets worse... >=20 > x=3D> SET enable_bitmapscan=3Doff;=20 > SET > x=3D> explain analyze select * from (select * from entity2document2 wher= e name=3D'ranitidine' ) as a order by a.hepval; > = QUERY PLAN=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20 > -------------------------------------------------------------------------= ---------------------------------------------------------------------------= ------------ > Sort (cost=3D18789.21..18800.70 rows=3D4595 width=3D131) (actual time= =3D79965.282..79966.657 rows=3D13512 loops=3D1) > Sort Key: entity2document2.hepval > Sort Method: quicksort Memory: 2301kB > -> Index Scan using entity2document2_name on entity2document2 (cost= =3D0.00..18509.70 rows=3D4595 width=3D131) (actual time=3D67.507..79945.362= rows=3D13512 loops=3D1) > Index Cond: ((name)::text =3D 'ranitidine'::text) > Total runtime: 79967.705 ms > (6 rows) >=20 > Any other idea?=20 >=20 Please post your hw configuration. I think that your db is on disk and they= are slow. > Thank you very much for your help. Regards, > Andr=E9s >=20 > El Mar 6, 2014, a las 2:11 PM, desmodemone escribi=F3: >=20 >>=20 >> Il 05/mar/2014 00:36 "Venkata Balaji Nagothi" ha scri= tto: >> > >> > After looking at the distinct values, yes the composite Index on "name= " and "hepval" is not recommended. That would worsen - its expected. >> > >> > We need to look for other possible work around. Please drop off the ab= ove Index. Let me see if i can drill further into this. >> > >> > Meanwhile - can you help us know the memory parameters (work_mem, temp= _buffers etc) set ? >> > >> > Do you have any other processes effecting this query's performance ? >> > >> > Any info about your Disk, RAM, CPU would also help. >> > >> > Regards, >> > Venkata Balaji N >> > >> > Fujitsu Australia >> > >> > >> > >> > >> > Venkata Balaji N >> > >> > Sr. Database Administrator >> > Fujitsu Australia >> > >> > >> > On Tue, Mar 4, 2014 at 10:23 PM, acanada wrote: >> >> >> >> Hello, >> >> >> >> I don't know if this helps to figure out what is the problem but afte= r adding the multicolumn index on name and hepval, the performance is even = worse (=BF?). Ten times worse... >> >> >> >> explain analyze select * from (select * from entity_compounddict2docu= ment where name=3D'progesterone') as a order by a.hepval; >> >> = QUERY PLAN=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20 >> >> ---------------------------------------------------------------------= ---------------------------------------------------------------------------= ------------- >> >> Sort (cost=3D422746.18..423143.94 rows=3D159104 width=3D133) (actua= l time=3D95769.674..95797.943 rows=3D138165 loops=3D1) >> >> Sort Key: entity_compounddict2document.hepval >> >> Sort Method: quicksort Memory: 25622kB >> >> -> Bitmap Heap Scan on entity_compounddict2document (cost=3D3501= .01..408999.90 rows=3D159104 width=3D133) (actual time=3D70.789..95519.258 = rows=3D138165 loops=3D1) >> >> Recheck Cond: ((name)::text =3D 'progesterone'::text) >> >> -> Bitmap Index Scan on entity_compound2document_name (cos= t=3D0.00..3461.23 rows=3D159104 width=3D0) (actual time=3D35.174..35.174 ro= ws=3D138165 loops=3D1) >> >> Index Cond: ((name)::text =3D 'progesterone'::text) >> >> Total runtime: 95811.838 ms >> >> (8 rows) >> >> >> >> Any ideas please? >> >> >> >> Thank you=20 >> >> Andr=E9s. >> >> >> >> >> >> >> >> El Mar 4, 2014, a las 12:28 AM, Venkata Balaji Nagothi escribi=F3: >> >> >> >>> On Mon, Mar 3, 2014 at 9:17 PM, acanada wrote: >> >>>> >> >>>> Hello, >> >>>> >> >>>> Thankyou for your answer. >> >>>> I have made more changes than a simple re-indexing recently. I have= moved the sorting field to the table in order to avoid the join clause. No= w the schema is very simple. The query only implies one table: >> >>>> >> >>>> x=3D> \d+ entity_compounddict2document; >> >>>> Table "public.entity_compounddict2document" >> >>>> Column | Type | Modifiers | St= orage | Description=20 >> >>>> ------------------+--------------------------------+-----------+---= -------+------------- >> >>>> id | integer | not null | pl= ain |=20 >> >>>> document_id | integer | | pl= ain |=20 >> >>>> name | character varying(255) | | ex= tended |=20 >> >>>> qualifier | character varying(255) | | ex= tended |=20 >> >>>> tagMethod | character varying(255) | | ex= tended |=20 >> >>>> created | timestamp(0) without time zone | | pl= ain |=20 >> >>>> updated | timestamp(0) without time zone | | pl= ain |=20 >> >>>> curation | integer | | pl= ain |=20 >> >>>> hepval | double precision | | pl= ain |=20 >> >>>> cardval | double precision | | pl= ain |=20 >> >>>> nephval | double precision | | pl= ain |=20 >> >>>> phosval | double precision | | pl= ain |=20 >> >>>> patternCount | double precision | | pl= ain |=20 >> >>>> ruleScore | double precision | | pl= ain |=20 >> >>>> hepTermNormScore | double precision | | pl= ain |=20 >> >>>> hepTermVarScore | double precision | | pl= ain |=20 >> >>>> Indexes: >> >>>> "entity_compounddict2document_pkey" PRIMARY KEY, btree (id) >> >>>> "entity_compound2document_cardval" btree (cardval) >> >>>> "entity_compound2document_heptermnormscore" btree ("hepTermNorm= Score") >> >>>> "entity_compound2document_heptermvarscore" btree ("hepTermVarSc= ore") >> >>>> "entity_compound2document_hepval" btree (hepval) >> >>>> "entity_compound2document_name" btree (name) >> >>>> "entity_compound2document_nephval" btree (nephval) >> >>>> "entity_compound2document_patterncount" btree ("patternCount") >> >>>> "entity_compound2document_phosval" btree (phosval) >> >>>> "entity_compound2document_rulescore" btree ("ruleScore") >> >>>> Has OIDs: no >> >>>> >> >>>> tablename | indexname = | num_rows | table_size | inde= x_size | unique | number_of_scans | tuples_read | tuples_fetched=20 >> >>>> entity_compounddict2document | entity_compound2document_cardval = | 5.42452e+07 | 6763 MB | 1162 MB | Y | = 0 | 0 | 0 >> >>>> entity_compounddict2document | entity_compound2document_heptermn= ormscore | 5.42452e+07 | 6763 MB | 1162 MB | Y | = 0 | 0 | 0 >> >>>> entity_compounddict2document | entity_compound2document_heptermv= arscore | 5.42452e+07 | 6763 MB | 1162 MB | Y | = 0 | 0 | 0 >> >>>> entity_compounddict2document | entity_compound2document_hepval = | 5.42452e+07 | 6763 MB | 1162 MB | Y | = 0 | 0 | 0 >> >>>> entity_compounddict2document | entity_compound2document_name = | 5.42452e+07 | 6763 MB | 1505 MB | Y | = 24 | 178680 | 0 >> >>>> entity_compounddict2document | entity_compound2document_nephval = | 5.42452e+07 | 6763 MB | 1162 MB | Y | = 0 | 0 | 0 >> >>>> entity_compounddict2document | entity_compound2document_patternc= ount | 5.42452e+07 | 6763 MB | 1162 MB | Y | = 0 | 0 | 0 >> >>>> entity_compounddict2document | entity_compound2document_phosval = | 5.42452e+07 | 6763 MB | 1162 MB | Y | = 0 | 0 | 0 >> >>>> entity_compounddict2document | entity_compound2document_rulescor= e | 5.42452e+07 | 6763 MB | 1162 MB | Y | = 0 | 0 | 0 >> >>>> entity_compounddict2document | entity_compounddict2document_pkey= | 5.42452e+07 | 6763 MB | 1162 MB | Y | = 0 | 0 | 0 >> >>>> >> >>>> The table has aprox. 54,000,000 rows >> >>>> There are no NULLs in hepval field and pg_settings haven't changed.= I also have done "analyze" to this table. >> >>>> >> >>>> I have simplified the query and added the last advise that you told= me: >> >>>> >> >>>> Query:=20 >> >>>> >> >>>> explain analyze select * from (select * from entity_compounddict2d= ocument where name=3D'ranitidine') as a order by a.hepval; >> >>>> = QUERY PLAN=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20 >> >>>> -------------------------------------------------------------------= ---------------------------------------------------------------------------= -------- >> >>>> Sort (cost=3D11060.50..11067.55 rows=3D2822 width=3D133) (actual = time=3D32715.097..32716.488 rows=3D13512 loops=3D1) >> >>>> Sort Key: entity_compounddict2document.hepval >> >>>> Sort Method: quicksort Memory: 2301kB >> >>>> -> Bitmap Heap Scan on entity_compounddict2document (cost=3D73= .82..10898.76 rows=3D2822 width=3D133) (actual time=3D6.034..32695.483 rows= =3D13512 loops=3D1) >> >>>> Recheck Cond: ((name)::text =3D 'ranitidine'::text) >> >>>> -> Bitmap Index Scan on entity_compound2document_name (c= ost=3D0.00..73.12 rows=3D2822 width=3D0) (actual time=3D3.221..3.221 rows= =3D13512 loops=3D1) >> >>>> Index Cond: ((name)::text =3D 'ranitidine'::text) >> >>>> Total runtime: 32717.548 ms >> >>>> >> >>>> Another query: >> >>>> explain analyze select * from (select * from entity_compounddict2do= cument where name=3D'progesterone' ) as a order by a.hepval; >> >>>> >> >>>> QUERY PLAN >> >>>> -------------------------------------------------------------------= ---------------------------------------------------------------------------= -------------- >> >>>> Sort (cost=3D367879.25..368209.24 rows=3D131997 width=3D133) (act= ual time=3D9262.887..9287.046 rows=3D138165 loops=3D1) >> >>>> Sort Key: entity_compounddict2document.hepval >> >>>> Sort Method: quicksort Memory: 25622kB >> >>>> -> Bitmap Heap Scan on entity_compounddict2document (cost=3D29= 06.93..356652.81 rows=3D131997 width=3D133) (actual time=3D76.316..9038.485= rows=3D138165 loops=3D1) >> >>>> Recheck Cond: ((name)::text =3D 'progesterone'::text) >> >>>> -> Bitmap Index Scan on entity_compound2document_name (c= ost=3D0.00..2873.93 rows=3D131997 width=3D0) (actual time=3D40.913..40.913 = rows=3D138165 loops=3D1) >> >>>> Index Cond: ((name)::text =3D 'progesterone'::text) >> >>>> Total runtime: 9296.815 ms >> >>>> >> >>>> >> >>>> It has improved (I supose because of the lack of the join table) bu= t still taking a lot of time... Anything I can do?? >> >>>> >> >>>> Any help would be very appreciated. Thank you very much. >> >>> >> >>> >> >>> >> >>> Good to know performance has increased. >> >>> >> >>> "entity_compounddict2document" table goes through high INSERTS ? >> >>> >> >>> Can you help us know if the "helpval" column and "name" column have = high duplicate values ? "n_distinct" value from pg_stats table would have t= hat info.=20 >> >>> >> >>> Below could be a possible workaround - >> >>> >> >>> As mentioned earlier in this email, a composite Index on name and he= pval column might help. If the table does not go through lot of INSERTS, th= en consider performing a CLUSTER on the table using the same INDEX. >> >>> >> >>> Other recommendations - >> >>> >> >>> Please drop off all the Non-primary key Indexes which have 0 scans /= hits. This would harm the DB and the DB server whilst maintenance and DML = operations. >> >>> >> >>> Regards, >> >>> Venkata Balaji N >> >>> >> >>> Fujitsu Australia >> >> >> >> >> >> >> >> **NOTA DE CONFIDENCIALIDAD** Este correo electr=F3nico, y en su caso = los ficheros adjuntos, pueden contener informaci=F3n protegida para el uso = exclusivo de su destinatario. Se proh=EDbe la distribuci=F3n, reproducci=F3= n o cualquier otro tipo de transmisi=F3n por parte de otra persona que no s= ea el destinatario. Si usted recibe por error este correo, se ruega comunic= arlo al remitente y borrar el mensaje recibido. >> >> >> >> **CONFIDENTIALITY NOTICE** This email communication and any attachmen= ts may contain confidential and privileged information for the sole use of = the designated recipient named above. Distribution, reproduction or any oth= er use of this transmission by any party other than the intended recipient = is prohibited. If you are not the intended recipient please contact the sen= der and delete all copies. >> >> >> > >>=20 >>=20 >>=20 >> Hi I think the problem is th heap scan of the table , that the backend h= ave to do because the btree to bitmap conversion becomes lossy. Try to disa= ble the enable_bitmapscan for the current session and rerun the query. >>=20 >> Mat Dba >>=20 >=20 >=20 >=20 > **NOTA DE CONFIDENCIALIDAD** Este correo electr=F3nico, y en su caso los = ficheros adjuntos, pueden contener informaci=F3n protegida para el uso excl= usivo de su destinatario. Se proh=EDbe la distribuci=F3n, reproducci=F3n o = cualquier otro tipo de transmisi=F3n por parte de otra persona que no sea e= l destinatario. Si usted recibe por error este correo, se ruega comunicarlo= al remitente y borrar el mensaje recibido. >=20 > **CONFIDENTIALITY NOTICE** This email communication and any attachments m= ay contain confidential and privileged information for the sole use of the = designated recipient named above. Distribution, reproduction or any other u= se of this transmission by any party other than the intended recipient is p= rohibited. If you are not the intended recipient please contact the sender = and delete all copies. >=20 >=20 --=20 Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance