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

Reply via email to