Hello!
The table doesn't go through high inserts so I'm taking into account your
"CLUSTER" advise. Thanks.
I'm afraid that I cannot drop the indexes that don't have scans hits because
they will have scans and hits very soon
Duplicated values for this table are:
tablename | attname | n_distinct
entity_compounddict2document | name | 16635
entity_compounddict2document | hepval | 2.04444e+06
Thank you very much for your help!!
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.
