Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1WLrdI-0000oS-JN for pgsql-performance@arkaria.postgresql.org; Fri, 07 Mar 2014 10:04:00 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1WLrdI-0004c3-3S for pgsql-performance@arkaria.postgresql.org; Fri, 07 Mar 2014 10:04:00 +0000 Received: from makus.postgresql.org ([2001:4800:7903:4::125]) by malur.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1WLrdG-0004bw-9g for pgsql-performance@postgresql.org; Fri, 07 Mar 2014 10:03:58 +0000 Received: from mail-pa0-x229.google.com ([2607:f8b0:400e:c03::229]) by makus.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1WLrd7-0006C3-53 for pgsql-performance@postgresql.org; Fri, 07 Mar 2014 10:03:57 +0000 Received: by mail-pa0-f41.google.com with SMTP id fa1so3986335pad.28 for ; Fri, 07 Mar 2014 02:03:47 -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=m2f5LKwXHDMDvCRCX96Epyn1Bzt+a+RovNwa7ZK+Ufc=; b=luq7x3EW3gZlVOhDXEj+vAAf4vRED2KdCdrvDGVh3XxZbG/HVSNrIvq3zemKfSuDlH i/fEzdltTejSzLSw6i7OUdMefUyN0F5Zfm0N930DqTJVS55aeVGfa2QlBuQFYli28uXK T+PgWyTJxI2G7OD1fCZkrVq/anNrFe7qqMdh+0uvCNQZ9JyBK55Sdr7IXjnSShFtXc8k MsEGYov+7MLhsdTxZLkRXUoW5Gp7TqCoygtqiLkA1SV5m3vbDCsKaWDBe431Tx/jFtm3 QhQoZkzvG79kV9ze0spKmubi02S0CKXdHyKzbNvtp3Fn/tafJl+dVG1nJE4E5Z4XKd74 XRTw== X-Received: by 10.66.146.199 with SMTP id te7mr20945980pab.106.1394186624727; Fri, 07 Mar 2014 02:03:44 -0800 (PST) Received: from wgaybook.corp.wargaming.local (by1-office.wargaming.net. [178.172.239.240]) by mx.google.com with ESMTPSA id ja8sm31722554pbd.3.2014.03.07.02.03.42 for (version=TLSv1 cipher=ECDHE-RSA-RC4-SHA bits=128/128); Fri, 07 Mar 2014 02:03:44 -0800 (PST) Content-Type: text/plain; charset=windows-1252 Mime-Version: 1.0 (Mac OS X Mail 7.1 \(1827\)) Subject: Re: Query taking long time From: Evgeniy Shishkin In-Reply-To: <6A1EC90E-4289-46A3-924A-799C0E158ACC@cnio.es> Date: Fri, 7 Mar 2014 13:03:39 +0300 Cc: "pgsql-performance@postgresql.org list" Content-Transfer-Encoding: quoted-printable Message-Id: 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> <7BCDBA11-D581-4B9F-97EC-3F6828F203AA@gmail.com> <6A1EC90E-4289-46A3-924A-799C0E158ACC@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 On 07 Mar 2014, at 12:46, acanada wrote: >=20 > El Mar 7, 2014, a las 10:39 AM, Evgeniy Shishkin escribi=F3: >=20 >>=20 >>> 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 wh= ere 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 >>=20 >> Please post your hw configuration. I think that your db is on disk and t= hey are slow. >=20 > The server has 2 processors quadcore, 10GB of RAM and data is located in = a fiber disk of 2TB. It doesn't seem to be the problem=85=20 And your database size is? Also do this timings get better in consecutive runs?=20 >=20 > Thank you >=20 > Andr=E9s >=20 >>=20 >>=20 >>=20 >>> 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 sc= ritto: >>>>>=20 >>>>> After looking at the distinct values, yes the composite Index on "nam= e" and "hepval" is not recommended. That would worsen - its expected. >>>>>=20 >>>>> We need to look for other possible work around. Please drop off the a= bove Index. Let me see if i can drill further into this. >>>>>=20 >>>>> Meanwhile - can you help us know the memory parameters (work_mem, tem= p_buffers etc) set ? >>>>>=20 >>>>> Do you have any other processes effecting this query's performance ? >>>>>=20 >>>>> Any info about your Disk, RAM, CPU would also help. >>>>>=20 >>>>> Regards, >>>>> Venkata Balaji N >>>>>=20 >>>>> Fujitsu Australia >>>>>=20 >>>>>=20 >>>>>=20 >>>>>=20 >>>>> Venkata Balaji N >>>>>=20 >>>>> Sr. Database Administrator >>>>> Fujitsu Australia >>>>>=20 >>>>>=20 >>>>> On Tue, Mar 4, 2014 at 10:23 PM, acanada wrote: >>>>>>=20 >>>>>> Hello, >>>>>>=20 >>>>>> I don't know if this helps to figure out what is the problem but aft= er adding the multicolumn index on name and hepval, the performance is even= worse (=BF?). Ten times worse... >>>>>>=20 >>>>>> explain analyze select * from (select * from entity_compounddict2doc= ument 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 r= ows=3D138165 loops=3D1) >>>>>> Recheck Cond: ((name)::text =3D 'progesterone'::text) >>>>>> -> Bitmap Index Scan on entity_compound2document_name (cost= =3D0.00..3461.23 rows=3D159104 width=3D0) (actual time=3D35.174..35.174 row= s=3D138165 loops=3D1) >>>>>> Index Cond: ((name)::text =3D 'progesterone'::text) >>>>>> Total runtime: 95811.838 ms >>>>>> (8 rows) >>>>>>=20 >>>>>> Any ideas please? >>>>>>=20 >>>>>> Thank you=20 >>>>>> Andr=E9s. >>>>>>=20 >>>>>>=20 >>>>>>=20 >>>>>> El Mar 4, 2014, a las 12:28 AM, Venkata Balaji Nagothi escribi=F3: >>>>>>=20 >>>>>>> On Mon, Mar 3, 2014 at 9:17 PM, acanada wrote: >>>>>>>>=20 >>>>>>>> Hello, >>>>>>>>=20 >>>>>>>> Thankyou for your answer. >>>>>>>> I have made more changes than a simple re-indexing recently. I hav= e moved the sorting field to the table in order to avoid the join clause. N= ow the schema is very simple. The query only implies one table: >>>>>>>>=20 >>>>>>>> x=3D> \d+ entity_compounddict2document; >>>>>>>> Table "public.entity_compounddict2document" >>>>>>>> Column | Type | Modifiers | Sto= rage | 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 ("hepTermNormS= core") >>>>>>>> "entity_compound2document_heptermvarscore" btree ("hepTermVarSco= re") >>>>>>>> "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 >>>>>>>>=20 >>>>>>>> tablename | indexname = | num_rows | table_size | index= _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 >>>>>>>>=20 >>>>>>>> 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. >>>>>>>>=20 >>>>>>>> I have simplified the query and added the last advise that you tol= d me: >>>>>>>>=20 >>>>>>>> Query:=20 >>>>>>>>=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 (co= st=3D0.00..73.12 rows=3D2822 width=3D0) (actual time=3D3.221..3.221 rows=3D= 13512 loops=3D1) >>>>>>>> Index Cond: ((name)::text =3D 'ranitidine'::text) >>>>>>>> Total runtime: 32717.548 ms >>>>>>>>=20 >>>>>>>> Another query: >>>>>>>> explain analyze select * from (select * from entity_compounddict2d= ocument where name=3D'progesterone' ) as a order by a.hepval; >>>>>>>>=20 >>>>>>>> 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=3D290= 6.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 (co= st=3D0.00..2873.93 rows=3D131997 width=3D0) (actual time=3D40.913..40.913 r= ows=3D138165 loops=3D1) >>>>>>>> Index Cond: ((name)::text =3D 'progesterone'::text) >>>>>>>> Total runtime: 9296.815 ms >>>>>>>>=20 >>>>>>>>=20 >>>>>>>> It has improved (I supose because of the lack of the join table) b= ut still taking a lot of time... Anything I can do?? >>>>>>>>=20 >>>>>>>> Any help would be very appreciated. Thank you very much. >>>>>>>=20 >>>>>>>=20 >>>>>>>=20 >>>>>>> Good to know performance has increased. >>>>>>>=20 >>>>>>> "entity_compounddict2document" table goes through high INSERTS ? >>>>>>>=20 >>>>>>> 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 = that info.=20 >>>>>>>=20 >>>>>>> Below could be a possible workaround - >>>>>>>=20 >>>>>>> As mentioned earlier in this email, a composite Index on name and h= epval column might help. If the table does not go through lot of INSERTS, t= hen consider performing a CLUSTER on the table using the same INDEX. >>>>>>>=20 >>>>>>> Other recommendations - >>>>>>>=20 >>>>>>> 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. >>>>>>>=20 >>>>>>> Regards, >>>>>>> Venkata Balaji N >>>>>>>=20 >>>>>>> Fujitsu Australia >>>>>>=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= exclusivo de su destinatario. Se proh=EDbe la distribuci=F3n, reproducci= =F3n o cualquier otro tipo de transmisi=F3n por parte de otra persona que n= o sea el destinatario. Si usted recibe por error este correo, se ruega comu= nicarlo al remitente y borrar el mensaje recibido. >>>>>>=20 >>>>>> **CONFIDENTIALITY NOTICE** This email communication and any attachme= nts may contain confidential and privileged information for the sole use of= the designated recipient named above. Distribution, reproduction or any ot= her use of this transmission by any party other than the intended recipient= is prohibited. If you are not the intended recipient please contact the se= nder and delete all copies. >>>>>>=20 >>>>>=20 >>>>=20 >>>>=20 >>>>=20 >>>> Hi I think the problem is th heap scan of the table , that the backend= have to do because the btree to bitmap conversion becomes lossy. Try to di= sable 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 lo= s ficheros adjuntos, pueden contener informaci=F3n protegida para el uso ex= clusivo 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= el destinatario. Si usted recibe por error este correo, se ruega comunicar= lo al remitente y borrar el mensaje recibido. >>>=20 >>> **CONFIDENTIALITY NOTICE** This email communication and any attachments= may contain confidential and privileged information for the sole use of th= e designated recipient named above. Distribution, reproduction or any other= use of this transmission by any party other than the intended recipient is= prohibited. If you are not the intended recipient please contact the sende= r and delete all copies. >>>=20 >>>=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. > **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 Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance