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
