On 07 Mar 2014, at 12:46, acanada <acan...@cnio.es> wrote: > > El Mar 7, 2014, a las 10:39 AM, Evgeniy Shishkin escribió: > >> >>> Hello Mat, >>> >>> Setting enable_bitmapscan to off doesn't really helps. It gets worse... >>> >>> x=> SET enable_bitmapscan=off; >>> SET >>> x=> explain analyze select * from (select * from entity2document2 where >>> name='ranitidine' ) as a order by a.hepval; >>> >>> QUERY PLAN >>> >>> ---------------------------------------------------------------------------------------------------------------------------------------------------------------- >>> Sort (cost=18789.21..18800.70 rows=4595 width=131) (actual >>> time=79965.282..79966.657 rows=13512 loops=1) >>> Sort Key: entity2document2.hepval >>> Sort Method: quicksort Memory: 2301kB >>> -> Index Scan using entity2document2_name on entity2document2 >>> (cost=0.00..18509.70 rows=4595 width=131) (actual time=67.507..79945.362 >>> rows=13512 loops=1) >>> Index Cond: ((name)::text = 'ranitidine'::text) >>> Total runtime: 79967.705 ms >>> (6 rows) >>> >>> Any other idea? >>> >> >> Please post your hw configuration. I think that your db is on disk and they >> are slow. > > 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…
And your database size is? Also do this timings get better in consecutive runs? > > Thank you > > Andrés > >> >> >> >>> Thank you very much for your help. Regards, >>> Andrés >>> >>> El Mar 6, 2014, a las 2:11 PM, desmodemone escribió: >>> >>>> >>>> Il 05/mar/2014 00:36 "Venkata Balaji Nagothi" <vbn...@gmail.com> ha >>>> scritto: >>>>> >>>>> 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 above >>>>> 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 <acan...@cnio.es> wrote: >>>>>> >>>>>> Hello, >>>>>> >>>>>> I don't know if this helps to figure out what is the problem but after >>>>>> adding the multicolumn index on name and hepval, the performance is even >>>>>> worse (¿?). Ten times worse... >>>>>> >>>>>> explain analyze select * from (select * from >>>>>> entity_compounddict2document where name='progesterone') as a order by >>>>>> a.hepval; >>>>>> >>>>>> QUERY PLAN >>>>>> >>>>>> ------------------------------------------------------------------------------------------------------------------------------------------------------------- >>>>>> Sort (cost=422746.18..423143.94 rows=159104 width=133) (actual >>>>>> time=95769.674..95797.943 rows=138165 loops=1) >>>>>> Sort Key: entity_compounddict2document.hepval >>>>>> Sort Method: quicksort Memory: 25622kB >>>>>> -> Bitmap Heap Scan on entity_compounddict2document >>>>>> (cost=3501.01..408999.90 rows=159104 width=133) (actual >>>>>> time=70.789..95519.258 rows=138165 loops=1) >>>>>> Recheck Cond: ((name)::text = 'progesterone'::text) >>>>>> -> Bitmap Index Scan on entity_compound2document_name >>>>>> (cost=0.00..3461.23 rows=159104 width=0) (actual time=35.174..35.174 >>>>>> rows=138165 loops=1) >>>>>> Index Cond: ((name)::text = 'progesterone'::text) >>>>>> Total runtime: 95811.838 ms >>>>>> (8 rows) >>>>>> >>>>>> Any ideas please? >>>>>> >>>>>> Thank you >>>>>> Andrés. >>>>>> >>>>>> >>>>>> >>>>>> El Mar 4, 2014, a las 12:28 AM, Venkata Balaji Nagothi escribió: >>>>>> >>>>>>> On Mon, Mar 3, 2014 at 9:17 PM, acanada <acan...@cnio.es> 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. Now the schema is very simple. The query only implies one >>>>>>>> table: >>>>>>>> >>>>>>>> x=> \d+ entity_compounddict2document; >>>>>>>> Table "public.entity_compounddict2document" >>>>>>>> Column | Type | Modifiers | Storage >>>>>>>> | Description >>>>>>>> ------------------+--------------------------------+-----------+----------+------------- >>>>>>>> id | integer | not null | plain >>>>>>>> | >>>>>>>> document_id | integer | | plain >>>>>>>> | >>>>>>>> name | character varying(255) | | >>>>>>>> extended | >>>>>>>> qualifier | character varying(255) | | >>>>>>>> extended | >>>>>>>> tagMethod | character varying(255) | | >>>>>>>> extended | >>>>>>>> created | timestamp(0) without time zone | | plain >>>>>>>> | >>>>>>>> updated | timestamp(0) without time zone | | plain >>>>>>>> | >>>>>>>> curation | integer | | plain >>>>>>>> | >>>>>>>> hepval | double precision | | plain >>>>>>>> | >>>>>>>> cardval | double precision | | plain >>>>>>>> | >>>>>>>> nephval | double precision | | plain >>>>>>>> | >>>>>>>> phosval | double precision | | plain >>>>>>>> | >>>>>>>> patternCount | double precision | | plain >>>>>>>> | >>>>>>>> ruleScore | double precision | | plain >>>>>>>> | >>>>>>>> hepTermNormScore | double precision | | plain >>>>>>>> | >>>>>>>> hepTermVarScore | double precision | | plain >>>>>>>> | >>>>>>>> Indexes: >>>>>>>> "entity_compounddict2document_pkey" PRIMARY KEY, btree (id) >>>>>>>> "entity_compound2document_cardval" btree (cardval) >>>>>>>> "entity_compound2document_heptermnormscore" btree >>>>>>>> ("hepTermNormScore") >>>>>>>> "entity_compound2document_heptermvarscore" btree ("hepTermVarScore") >>>>>>>> "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 | >>>>>>>> index_size | unique | number_of_scans | tuples_read | tuples_fetched >>>>>>>> entity_compounddict2document | entity_compound2document_cardval >>>>>>>> | 5.42452e+07 | 6763 MB | 1162 MB | Y | >>>>>>>> 0 | 0 | 0 >>>>>>>> entity_compounddict2document | >>>>>>>> entity_compound2document_heptermnormscore | 5.42452e+07 | 6763 MB >>>>>>>> | 1162 MB | Y | 0 | 0 | >>>>>>>> 0 >>>>>>>> entity_compounddict2document | >>>>>>>> entity_compound2document_heptermvarscore | 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_patterncount >>>>>>>> | 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_rulescore >>>>>>>> | 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: >>>>>>>> >>>>>>>> explain analyze select * from (select * from >>>>>>>> entity_compounddict2document where name='ranitidine') as a order by >>>>>>>> a.hepval; >>>>>>>> >>>>>>>> QUERY PLAN >>>>>>>> >>>>>>>> ------------------------------------------------------------------------------------------------------------------------------------------------------ >>>>>>>> Sort (cost=11060.50..11067.55 rows=2822 width=133) (actual >>>>>>>> time=32715.097..32716.488 rows=13512 loops=1) >>>>>>>> Sort Key: entity_compounddict2document.hepval >>>>>>>> Sort Method: quicksort Memory: 2301kB >>>>>>>> -> Bitmap Heap Scan on entity_compounddict2document >>>>>>>> (cost=73.82..10898.76 rows=2822 width=133) (actual >>>>>>>> time=6.034..32695.483 rows=13512 loops=1) >>>>>>>> Recheck Cond: ((name)::text = 'ranitidine'::text) >>>>>>>> -> Bitmap Index Scan on entity_compound2document_name >>>>>>>> (cost=0.00..73.12 rows=2822 width=0) (actual time=3.221..3.221 >>>>>>>> rows=13512 loops=1) >>>>>>>> Index Cond: ((name)::text = 'ranitidine'::text) >>>>>>>> Total runtime: 32717.548 ms >>>>>>>> >>>>>>>> Another query: >>>>>>>> explain analyze select * from (select * from >>>>>>>> entity_compounddict2document where name='progesterone' ) as a order >>>>>>>> by a.hepval; >>>>>>>> >>>>>>>> QUERY PLAN >>>>>>>> ------------------------------------------------------------------------------------------------------------------------------------------------------------ >>>>>>>> Sort (cost=367879.25..368209.24 rows=131997 width=133) (actual >>>>>>>> time=9262.887..9287.046 rows=138165 loops=1) >>>>>>>> Sort Key: entity_compounddict2document.hepval >>>>>>>> Sort Method: quicksort Memory: 25622kB >>>>>>>> -> Bitmap Heap Scan on entity_compounddict2document >>>>>>>> (cost=2906.93..356652.81 rows=131997 width=133) (actual >>>>>>>> time=76.316..9038.485 rows=138165 loops=1) >>>>>>>> Recheck Cond: ((name)::text = 'progesterone'::text) >>>>>>>> -> Bitmap Index Scan on entity_compound2document_name >>>>>>>> (cost=0.00..2873.93 rows=131997 width=0) (actual time=40.913..40.913 >>>>>>>> rows=138165 loops=1) >>>>>>>> Index Cond: ((name)::text = 'progesterone'::text) >>>>>>>> Total runtime: 9296.815 ms >>>>>>>> >>>>>>>> >>>>>>>> It has improved (I supose because of the lack of the join table) but >>>>>>>> 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 that info. >>>>>>> >>>>>>> Below could be a possible workaround - >>>>>>> >>>>>>> As mentioned earlier in this email, a composite Index on name and >>>>>>> hepval column might help. If the table does not go through lot of >>>>>>> INSERTS, then 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ónico, y en su caso los >>>>>> ficheros adjuntos, pueden contener información protegida para el uso >>>>>> exclusivo de su destinatario. Se prohíbe la distribución, reproducción o >>>>>> cualquier otro tipo de transmisión por parte de otra persona que no sea >>>>>> el 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 >>>>>> may contain confidential and privileged information for the sole use of >>>>>> the 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 sender and delete all copies. >>>>>> >>>>> >>>> >>>> >>>> >>>> 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 >>>> disable the enable_bitmapscan for the current session and rerun the query. >>>> >>>> Mat Dba >>>> >>> >>> >>> >>> **NOTA DE CONFIDENCIALIDAD** Este correo electrónico, y en su caso los >>> ficheros adjuntos, pueden contener información protegida para el uso >>> exclusivo de su destinatario. Se prohíbe la distribución, reproducción o >>> cualquier otro tipo de transmisión por parte de otra persona que no sea el >>> 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 may >>> contain confidential and privileged information for the sole use of the >>> 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 sender >>> and delete all copies. >>> >>> >> > > > **NOTA DE CONFIDENCIALIDAD** Este correo electrónico, y en su caso los > ficheros adjuntos, pueden contener información protegida para el uso > exclusivo de su destinatario. Se prohíbe la distribución, reproducción o > cualquier otro tipo de transmisión por parte de otra persona que no sea el > 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 may > contain confidential and privileged information for the sole use of the > 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 sender > and delete all copies. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance