Re: Query taking long time

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.362rows=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... 

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" <vbnpgc@gmail.com> ha scritto:
>>>>
>>>> After looking at the distinct values, yes the composite Index on "name" and "hepval" is not recommended. That
wouldworsen - 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
intothis. 
>>>>
>>>> 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 <acanada@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
bya.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.258rows=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.174rows=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 <acanada@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
orderto 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    |
1162MB    | Y      |               0 |           0 |              0 
>>>>>>> entity_compounddict2document   | entity_compound2document_heptermnormscore      | 5.42452e+07 | 6763 MB    |
1162MB    | Y      |               0 |           0 |              0 
>>>>>>> entity_compounddict2document   | entity_compound2document_heptermvarscore       | 5.42452e+07 | 6763 MB    |
1162MB    | Y      |               0 |           0 |              0 
>>>>>>> entity_compounddict2document   | entity_compound2document_hepval                | 5.42452e+07 | 6763 MB    |
1162MB    | Y      |               0 |           0 |              0 
>>>>>>> entity_compounddict2document   | entity_compound2document_name                  | 5.42452e+07 | 6763 MB    |
1505MB    | Y      |              24 |      178680 |              0 
>>>>>>> entity_compounddict2document   | entity_compound2document_nephval               | 5.42452e+07 | 6763 MB    |
1162MB    | Y      |               0 |           0 |              0 
>>>>>>> entity_compounddict2document   | entity_compound2document_patterncount          | 5.42452e+07 | 6763 MB    |
1162MB    | Y      |               0 |           0 |              0 
>>>>>>> entity_compounddict2document   | entity_compound2document_phosval               | 5.42452e+07 | 6763 MB    |
1162MB    | Y      |               0 |           0 |              0 
>>>>>>> entity_compounddict2document   | entity_compound2document_rulescore             | 5.42452e+07 | 6763 MB    |
1162MB    | Y      |               0 |           0 |              0 
>>>>>>> entity_compounddict2document   | entity_compounddict2document_pkey              | 5.42452e+07 | 6763 MB    |
1162MB    | 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
bya.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.483rows=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.221rows=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
orderby 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.485rows=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)
(actualtime=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
cando?? 
>>>>>>>
>>>>>>> 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
frompg_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
notgo 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
serverwhilst 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ónprotegida para el uso exclusivo de su destinatario. Se prohíbe la distribución, reproducción o cualquier
otrotipo de transmisión por parte de otra persona que no sea el destinatario. Si usted recibe por error este correo, se
ruegacomunicarlo al remitente y borrar el mensaje recibido. 
>>>>>
>>>>> **CONFIDENTIALITY NOTICE** This email communication and any attachments may contain confidential and privileged
informationfor the sole use of the designated recipient named above. Distribution, reproduction or any other use of
thistransmission by any party other than the intended recipient is prohibited. If you are not the intended recipient
pleasecontact 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
conversionbecomes 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ónprotegida para el uso exclusivo de su destinatario. Se prohíbe la distribución, reproducción o cualquier
otrotipo de transmisión por parte de otra persona que no sea el destinatario. Si usted recibe por error este correo, se
ruegacomunicarlo al remitente y borrar el mensaje recibido. 
>>
>> **CONFIDENTIALITY NOTICE** This email communication and any attachments may contain confidential and privileged
informationfor the sole use of the designated recipient named above. Distribution, reproduction or any other use of
thistransmission by any party other than the intended recipient is prohibited. If you are not the intended recipient
pleasecontact the sender and delete all copies. 
>>
>>
>


**NOTA DE CONFIDENCIALIDAD** Este correo electrónico, y en su caso los ficheros adjuntos, pueden contener información
protegidapara el uso exclusivo de su destinatario. Se prohíbe la distribución, reproducción o cualquier otro tipo de
transmisiónpor parte de otra persona que no sea el destinatario. Si usted recibe por error este correo, se ruega
comunicarloal remitente y borrar el mensaje recibido. 
**CONFIDENTIALITY NOTICE** This email communication and any attachments may contain confidential and privileged
informationfor the sole use of the designated recipient named above. Distribution, reproduction or any other use of
thistransmission by any party other than the intended recipient is prohibited. If you are not the intended recipient
pleasecontact the sender and delete all copies.