> 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.
> 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.
>
>
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
