Thread: Query taking long time

Hello to everybody and thanks in advance to take a look to this message.
I'm new in this list and with PostgreSQL.
My queries are taking too much time to complete and I don't know what to do right now. I think I'm providing all  the
inforequired for you to help me. If you need extra info please tell me. 

I am using DQL included in the last version of symfony2 (2.4.2). This is the query, formed by DQL, but coppied-pasted
tothe psql client (9.1.11, server 8.3.8) 

explain analyze SELECT e0_.id AS id0, e0_.name AS name1, e0_.qualifier AS qualifier2, e0_."tagMethod" AS tagmethod3,
e0_.curationAS curation4, e0_.created AS created5, e0_.updated AS updated6, d1_.id AS id7, d1_.kind AS kind8, d1_.uid
ASuid9, d1_."sentenceId" AS sentenceid10, d1_.text AS text11, d1_.hepval AS hepval12, d1_.cardval AS cardval13,
d1_.nephvalAS nephval14, d1_.phosval AS phosval15, d1_."patternCount" AS patterncount16, d1_."ruleScore" AS
rulescore17,d1_."hepTermNormScore" AS heptermnormscore18, d1_."hepTermVarScore" AS heptermvarscore19, d1_.created AS
created20,d1_.updated AS updated21, e0_.document_id AS document_id22 FROM Entity2Document e0_ INNER JOIN documentold
d1_ON e0_.document_id = d1_.id WHERE e0_.name ='ranitidine' AND e0_.qualifier = 'CompoundDict' AND d1_.hepval IS NOT
NULLORDER BY d1_.hepval DESC limit 10; 


limtox=> \d+ documentold;
                               Table "public.documentold"
      Column      |              Type              | Modifiers | Storage  | Description
------------------+--------------------------------+-----------+----------+-------------
 id               | integer                        | not null  | plain    |
 kind             | character varying(255)         | not null  | extended |
 uid              | character varying(255)         | not null  | extended |
 sentenceId       | character varying(255)         | not null  | extended |
 text             | text                           | not null  | extended |
 hepval           | double precision               |           | plain    |
 created          | timestamp(0) without time zone | not null  | plain    |
 updated          | timestamp(0) without time zone |           | 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:
    "DocumentOLD_pkey" PRIMARY KEY, btree (id)
    "document_cardval_index" btree (cardval)
    "document_heptermnorm_index" btree ("hepTermNormScore" DESC NULLS LAST)
    "document_heptermvar_index" btree ("hepTermVarScore" DESC NULLS LAST)
    "document_hepval_index" btree (hepval DESC NULLS LAST)
    "document_kind_index" btree (kind)
    "document_nephval_index" btree (nephval DESC NULLS LAST)
    "document_patterncount_index" btree ("patternCount" DESC NULLS LAST)
    "document_phosval_index" btree (phosval DESC NULLS LAST)
    "document_rulescore_index" btree ("ruleScore" DESC NULLS LAST)
    "document_sentenceid_index" btree ("sentenceId")
    "document_uid_index" btree (uid)
Referenced by:
    TABLE "hepkeywordtermnorm2document" CONSTRAINT "fk_1c19bcd0c33f7837" FOREIGN KEY (document_id) REFERENCES
documentold(id)
    TABLE "cytochrome2document" CONSTRAINT "fk_21f7636fc33f7837" FOREIGN KEY (document_id) REFERENCES documentold(id)
    TABLE "hepkeywordtermvariant2document" CONSTRAINT "fk_a316e36bc33f7837" FOREIGN KEY (document_id) REFERENCES
documentold(id)
    TABLE "entity2document" CONSTRAINT "fk_a6020c0dc33f7837" FOREIGN KEY (document_id) REFERENCES documentold(id)
    TABLE "specie2document" CONSTRAINT "fk_b6e551c8c33f7837" FOREIGN KEY (document_id) REFERENCES documentold(id)
Has OIDs: no




limtox=> \d+ entity2document;                                     Table "public.entity2document"   Column    |
   Type              |            Modifiers            | Storage  | Description
-------------+--------------------------------+---------------------------------+----------+-------------id          |
integer                       | not null                        | plain    |  
 document_id | integer                        |                                 | plain    |
 name        | character varying(255)         | not null                        | extended |
 qualifier   | character varying(255)         | not null                        | extended |
 tagMethod   | character varying(255)         | default NULL::character varying | extended |
 created     | timestamp(0) without time zone | not null                        | plain    |
 updated     | timestamp(0) without time zone |                                 | plain    |
 curation    | integer                        |                                 | plain    |
Indexes:
    "Entity2Document_pkey" PRIMARY KEY, btree (id)
    "entity2Document_name_index" btree (name)
    "entity2document_name_qualifier_index" btree (name, qualifier)
    "idx_a6020c0dc33f7837" btree (document_id)
    "qualifier_index" btree (qualifier)
Foreign-key constraints:
    "fk_a6020c0dc33f7837" FOREIGN KEY (document_id) REFERENCES documentold(id)
Has OIDs: no






Table metadata:
    documentold: 124.515.592 of rows. It has several columns with a large proportion of NULLs(updated, patternCount,
ruleScore,hepTermNormScore, hepTermVarScore) 
    entity2document: 93.785.968 of rows. It has two columns with a large proportion of NULLs (updated, curation)

None of the tables receive updates or deletes regularly


                                                                                QUERY PLAN
                                                  

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=387929.02..387929.05 rows=10 width=313) (actual time=55980.472..55980.476 rows=10 loops=1)
   ->  Sort  (cost=387929.02..387966.75 rows=15090 width=313) (actual time=55980.471..55980.473 rows=10 loops=1)
         Sort Key: d1_.hepval
         Sort Method:  top-N heapsort  Memory: 28kB
         ->  Nested Loop  (cost=469.14..387602.93 rows=15090 width=313) (actual time=96.716..55974.004 rows=2774
loops=1)
               ->  Bitmap Heap Scan on entity2document e0_  (cost=469.14..54851.25 rows=15090 width=59) (actual
time=51.299..8452.592rows=2774 loops=1) 
                     Recheck Cond: (((name)::text = 'Cimetidine'::text) AND ((qualifier)::text = 'CompoundDict'::text))
                     ->  Bitmap Index Scan on entity2document_name_qualifier_index  (cost=0.00..465.36 rows=15090
width=0)(actual time=36.467..36.467 rows=2774 loops=1) 
                           Index Cond: (((name)::text = 'Cimetidine'::text) AND ((qualifier)::text =
'CompoundDict'::text))
               ->  Index Scan using "DocumentOLD_pkey" on documentold d1_  (cost=0.00..22.04 rows=1 width=254) (actual
time=17.113..17.129rows=1 loops=2774) 
                     Index Cond: (d1_.id = e0_.document_id)
                     Filter: (d1_.hepval IS NOT NULL)
 Total runtime: 55980.554 ms
(13 rows)

 version
-----------------------------------------------------------------------------------------------------
 PostgreSQL 8.3.8 on x86_64-pc-linux-gnu, compiled by GCC gcc-4.4.real (Ubuntu 10.4.1-3ubuntu3) 10.4.1

 This query has been always slow. It's fast only when it's cached. Vacuum and analyze have been done manually very
recently




 SELECT name, current_setting(name), source
  FROM pg_settings
  WHERE source NOT IN ('default', 'override');

             name            |  current_setting   |        source
----------------------------+--------------------+----------------------
 client_encoding            | UTF8               | client
 DateStyle                  | ISO, DMY           | configuration file
 default_text_search_config | pg_catalog.spanish | configuration file
 effective_cache_size       | 7500MB             | configuration file
 lc_messages                | es_ES.UTF-8        | configuration file
 lc_monetary                | es_ES.UTF-8        | configuration file
 lc_numeric                 | C                  | configuration file
 lc_time                    | es_ES.UTF-8        | configuration file
 listen_addresses           | *                  | configuration file
 log_line_prefix            | %t                 | configuration file
 log_timezone               | localtime          | command line
 maintenance_work_mem       | 2000MB             | configuration file
 max_connections            | 100                | configuration file
 max_fsm_pages              | 63217760           | configuration file
 max_stack_depth            | 2MB                | environment variable
 port                       | 5432               | configuration file
 shared_buffers             | 1500MB             | configuration file
 ssl                        | on                 | configuration file
 tcp_keepalives_count       | 9                  | configuration file
 tcp_keepalives_idle        | 7200               | configuration file
 tcp_keepalives_interval    | 75                 | configuration file
 TimeZone                   | localtime          | command line
 timezone_abbreviations     | Default            | command line
 work_mem                   | 50MB               | configuration file

 Setting the work_mem to 3000MB doesn't change anything...

 Everything seems good to me but the Recheck Cond, because of the large ammount of rows, is slowing the query too much.
Ihave read that is not a good point to try to get rid of recheck cond (maybe even not possible, I don't know, I'm new
toPostgreSQL). I'd like to know what I am doing wrong and how can I solve it... 

 Any help please?

 Thank you very much,

 Andrés
**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. 

Hello,

I have changed the multicolumn index from:
    "entity2document_name_qualifier_index" btree (name, qualifier)
 to:
     "document_qualifier_name_index" btree (qualifier, name)

And now the planner doesn't "Recheck cond:"  (there are only three different qualifiers vs. millions of names...)

But still taking long time


Limit  (cost=384043.64..384043.66 rows=10 width=313) (actual time=80555.930..80555.934 rows=10 loops=1)
   ->  Sort  (cost=384043.64..384081.19 rows=15020 width=313) (actual time=80555.928..80555.931 rows=10 loops=1)
         Sort Key: d1_.hepval
         Sort Method:  top-N heapsort  Memory: 29kB
         ->  Nested Loop  (cost=0.00..383719.06 rows=15020 width=313) (actual time=223.778..80547.196 rows=3170
loops=1)
               ->  Index Scan using document_qualifier_name_index on entity2document e0_  (cost=0.00..52505.40
rows=15020width=59) (actual time=126.880..11549.392 rows=3170 loops=1) 
                     Index Cond: (((qualifier)::text = 'CompoundDict'::text) AND ((name)::text =
'galactosamine'::text))
               ->  Index Scan using "DocumentOLD_pkey" on documentold d1_  (cost=0.00..22.04 rows=1 width=254) (actual
time=21.747..21.764rows=1 loops=3170) 
                     Index Cond: (d1_.id = e0_.document_id)
                     Filter: (d1_.hepval IS NOT NULL)
 Total runtime: 80556.027 ms



Any help/point to any direction, would be very appreciated.
Thank you,
Andrés

El Feb 26, 2014, a las 4:41 PM, acanada escribió:

> Hello to everybody and thanks in advance to take a look to this message.
> I'm new in this list and with PostgreSQL.
> My queries are taking too much time to complete and I don't know what to do right now. I think I'm providing all  the
inforequired for you to help me. If you need extra info please tell me. 
>
> I am using DQL included in the last version of symfony2 (2.4.2). This is the query, formed by DQL, but coppied-pasted
tothe psql client (9.1.11, server 8.3.8) 
>
> explain analyze SELECT e0_.id AS id0, e0_.name AS name1, e0_.qualifier AS qualifier2, e0_."tagMethod" AS tagmethod3,
e0_.curationAS curation4, e0_.created AS created5, e0_.updated AS updated6, d1_.id AS id7, d1_.kind AS kind8, d1_.uid
ASuid9, d1_."sentenceId" AS sentenceid10, d1_.text AS text11, d1_.hepval AS hepval12, d1_.cardval AS cardval13,
d1_.nephvalAS nephval14, d1_.phosval AS phosval15, d1_."patternCount" AS patterncount16, d1_."ruleScore" AS
rulescore17,d1_."hepTermNormScore" AS heptermnormscore18, d1_."hepTermVarScore" AS heptermvarscore19, d1_.created AS
created20,d1_.updated AS updated21, e0_.document_id AS document_id22 FROM Entity2Document e0_ INNER JOIN documentold
d1_ON e0_.document_id = d1_.id WHERE e0_.name ='ranitidine' AND e0_.qualifier = 'CompoundDict' AND d1_.hepval IS NOT
NULLORDER BY d1_.hepval DESC limit 10; 
>
>
> limtox=> \d+ documentold;
>                               Table "public.documentold"
>      Column      |              Type              | Modifiers | Storage  | Description
> ------------------+--------------------------------+-----------+----------+-------------
> id               | integer                        | not null  | plain    |
> kind             | character varying(255)         | not null  | extended |
> uid              | character varying(255)         | not null  | extended |
> sentenceId       | character varying(255)         | not null  | extended |
> text             | text                           | not null  | extended |
> hepval           | double precision               |           | plain    |
> created          | timestamp(0) without time zone | not null  | plain    |
> updated          | timestamp(0) without time zone |           | 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:
>    "DocumentOLD_pkey" PRIMARY KEY, btree (id)
>    "document_cardval_index" btree (cardval)
>    "document_heptermnorm_index" btree ("hepTermNormScore" DESC NULLS LAST)
>    "document_heptermvar_index" btree ("hepTermVarScore" DESC NULLS LAST)
>    "document_hepval_index" btree (hepval DESC NULLS LAST)
>    "document_kind_index" btree (kind)
>    "document_nephval_index" btree (nephval DESC NULLS LAST)
>    "document_patterncount_index" btree ("patternCount" DESC NULLS LAST)
>    "document_phosval_index" btree (phosval DESC NULLS LAST)
>    "document_rulescore_index" btree ("ruleScore" DESC NULLS LAST)
>    "document_sentenceid_index" btree ("sentenceId")
>    "document_uid_index" btree (uid)
> Referenced by:
>    TABLE "hepkeywordtermnorm2document" CONSTRAINT "fk_1c19bcd0c33f7837" FOREIGN KEY (document_id) REFERENCES
documentold(id)
>    TABLE "cytochrome2document" CONSTRAINT "fk_21f7636fc33f7837" FOREIGN KEY (document_id) REFERENCES documentold(id)
>    TABLE "hepkeywordtermvariant2document" CONSTRAINT "fk_a316e36bc33f7837" FOREIGN KEY (document_id) REFERENCES
documentold(id)
>    TABLE "entity2document" CONSTRAINT "fk_a6020c0dc33f7837" FOREIGN KEY (document_id) REFERENCES documentold(id)
>    TABLE "specie2document" CONSTRAINT "fk_b6e551c8c33f7837" FOREIGN KEY (document_id) REFERENCES documentold(id)
> Has OIDs: no
>
>
>
>
> limtox=> \d+ entity2document;                                     Table "public.entity2document"   Column    |
     Type              |            Modifiers            | Storage  | Description
-------------+--------------------------------+---------------------------------+----------+-------------id          |
integer                       | not null                        | plain    |  
> document_id | integer                        |                                 | plain    |
> name        | character varying(255)         | not null                        | extended |
> qualifier   | character varying(255)         | not null                        | extended |
> tagMethod   | character varying(255)         | default NULL::character varying | extended |
> created     | timestamp(0) without time zone | not null                        | plain    |
> updated     | timestamp(0) without time zone |                                 | plain    |
> curation    | integer                        |                                 | plain    |
> Indexes:
>    "Entity2Document_pkey" PRIMARY KEY, btree (id)
>    "entity2Document_name_index" btree (name)
>    "entity2document_name_qualifier_index" btree (name, qualifier)
>    "idx_a6020c0dc33f7837" btree (document_id)
>    "qualifier_index" btree (qualifier)
> Foreign-key constraints:
>    "fk_a6020c0dc33f7837" FOREIGN KEY (document_id) REFERENCES documentold(id)
> Has OIDs: no
>
>
>
>
>
>
> Table metadata:
>    documentold: 124.515.592 of rows. It has several columns with a large proportion of NULLs(updated, patternCount,
ruleScore,hepTermNormScore, hepTermVarScore) 
>    entity2document: 93.785.968 of rows. It has two columns with a large proportion of NULLs (updated, curation)
>
> None of the tables receive updates or deletes regularly
>
>
>                                                                                QUERY PLAN
                                                   
>
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
> Limit  (cost=387929.02..387929.05 rows=10 width=313) (actual time=55980.472..55980.476 rows=10 loops=1)
>   ->  Sort  (cost=387929.02..387966.75 rows=15090 width=313) (actual time=55980.471..55980.473 rows=10 loops=1)
>         Sort Key: d1_.hepval
>         Sort Method:  top-N heapsort  Memory: 28kB
>         ->  Nested Loop  (cost=469.14..387602.93 rows=15090 width=313) (actual time=96.716..55974.004 rows=2774
loops=1)
>               ->  Bitmap Heap Scan on entity2document e0_  (cost=469.14..54851.25 rows=15090 width=59) (actual
time=51.299..8452.592rows=2774 loops=1) 
>                     Recheck Cond: (((name)::text = 'Cimetidine'::text) AND ((qualifier)::text =
'CompoundDict'::text))
>                     ->  Bitmap Index Scan on entity2document_name_qualifier_index  (cost=0.00..465.36 rows=15090
width=0)(actual time=36.467..36.467 rows=2774 loops=1) 
>                           Index Cond: (((name)::text = 'Cimetidine'::text) AND ((qualifier)::text =
'CompoundDict'::text))
>               ->  Index Scan using "DocumentOLD_pkey" on documentold d1_  (cost=0.00..22.04 rows=1 width=254) (actual
time=17.113..17.129rows=1 loops=2774) 
>                     Index Cond: (d1_.id = e0_.document_id)
>                     Filter: (d1_.hepval IS NOT NULL)
> Total runtime: 55980.554 ms
> (13 rows)
>
> version
> -----------------------------------------------------------------------------------------------------
> PostgreSQL 8.3.8 on x86_64-pc-linux-gnu, compiled by GCC gcc-4.4.real (Ubuntu 10.4.1-3ubuntu3) 10.4.1
>
> This query has been always slow. It's fast only when it's cached. Vacuum and analyze have been done manually very
recently
>
>
>
>
> SELECT name, current_setting(name), source
>  FROM pg_settings
>  WHERE source NOT IN ('default', 'override');
>
>             name            |  current_setting   |        source
> ----------------------------+--------------------+----------------------
> client_encoding            | UTF8               | client
> DateStyle                  | ISO, DMY           | configuration file
> default_text_search_config | pg_catalog.spanish | configuration file
> effective_cache_size       | 7500MB             | configuration file
> lc_messages                | es_ES.UTF-8        | configuration file
> lc_monetary                | es_ES.UTF-8        | configuration file
> lc_numeric                 | C                  | configuration file
> lc_time                    | es_ES.UTF-8        | configuration file
> listen_addresses           | *                  | configuration file
> log_line_prefix            | %t                 | configuration file
> log_timezone               | localtime          | command line
> maintenance_work_mem       | 2000MB             | configuration file
> max_connections            | 100                | configuration file
> max_fsm_pages              | 63217760           | configuration file
> max_stack_depth            | 2MB                | environment variable
> port                       | 5432               | configuration file
> shared_buffers             | 1500MB             | configuration file
> ssl                        | on                 | configuration file
> tcp_keepalives_count       | 9                  | configuration file
> tcp_keepalives_idle        | 7200               | configuration file
> tcp_keepalives_interval    | 75                 | configuration file
> TimeZone                   | localtime          | command line
> timezone_abbreviations     | Default            | command line
> work_mem                   | 50MB               | configuration file
>
> Setting the work_mem to 3000MB doesn't change anything...
>
> Everything seems good to me but the Recheck Cond, because of the large ammount of rows, is slowing the query too
much.I have read that is not a good point to try to get rid of recheck cond (maybe even not possible, I don't know, I'm
newto PostgreSQL). I'd like to know what I am doing wrong and how can I solve it... 
>
> Any help please?
>
> Thank you very much,
>
> Andrés
> **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. 
>
>
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance


**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. 

**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.

> 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;
>        
Do you need full result?

If you need just top-n rows, then index on entity_compounddict2document(name, a.hepval) might help.

Regards,
Vladimir Sitnikov

**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.

**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.

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 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 <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 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 <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 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.

> 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.



> 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)
(actualtime=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)
(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ó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. 
>
>

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. 

On 07 Mar 2014, at 12:46, acanada <acanada@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.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
theproblem…  

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" <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
furtherinto 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 <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. 

El Mar 7, 2014, a las 11:03 AM, Evgeniy Shishkin escribió:

>
> On 07 Mar 2014, at 12:46, acanada <acanada@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.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
theproblem…  
>
> And your database size is?
>
> Also do this timings get better in consecutive runs?
>

The table entity2document2 has 30GB. In consecutive runs it gets much better... 30ms aprox.



>
>>
>
>> 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
furtherinto 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 <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
orderby 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.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
orderby 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.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ó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. 

On 07 Mar 2014, at 13:18, acanada <acanada@cnio.es> wrote:

> The table entity2document2 has 30GB. In consecutive runs it gets much better... 30ms apron.

So you just benchmarking your hard drives with random iops.

You need more ram and faster disks.

Hello Evgeniy!

I can move the database to another server...
This is the cat of /proc/cpuinfo. Does it have enough power or should I go for a better one??

(It has 32 processors like this one):

cat /proc/cpuinfo
processor       : 0
vendor_id       : GenuineIntel
cpu family      : 6
model           : 45
model name      : Intel(R) Xeon(R) CPU E5-2670 0 @ 2.60GHz
stepping        : 7
microcode       : 0x70d
cpu MHz         : 1200.000
cache size      : 20480 KB
physical id     : 0
siblings        : 16
core id         : 0
cpu cores       : 8
apicid          : 0
initial apicid  : 0
fpu             : yes
fpu_exception   : yes
cpuid level     : 13
wp              : yes
flags           : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse
sse2ss ht tm pbe syscall nx pdpe1gb rdtscp lm constant_tsc arch_perfmon pebs bts rep_good nopl xtopology nonstop_tsc
aperfmperfpni pclmulqdq dtes64 monitor ds_cpl vmx smx est tm2 ssse3 cx16 xtpr pdcm pcid dca sse4_1 sse4_2 x2apic popcnt
tsc_deadline_timeraes xsave avx lahf_lm ida arat epb xsaveopt pln pts dtherm tpr_shadow vnmi flexpriority ept vpid 
bogomips        : 5187.62
clflush size    : 64
cache_alignment : 64
address sizes   : 46 bits physical, 48 bits virtual
power management:


free
             total       used       free     shared    buffers     cached
Mem:      65901148   32702336   33198812          0     264936   20625024
-/+ buffers/cache:   11812376   54088772
Swap:    134217724     413088  133804636


Thank you for your help,
Andrés


El Mar 10, 2014, a las 3:45 PM, Evgeniy Shishkin escribió:

>
> On 07 Mar 2014, at 13:18, acanada <acanada@cnio.es> wrote:
>
>> The table entity2document2 has 30GB. In consecutive runs it gets much better... 30ms apron.
>
> So you just benchmarking your hard drives with random iops.
>
> You need more ram and faster disks.


**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. 

**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.

Hello,

New server postgres version is 9.3. I'm not sure if I collected latest statistics after migration, if you mean if the
current_settingsor analyze queries that I posted were collected after migration... yes (notice that there are analyze
querybefore migration and after migration, maybe I didn't illustrate right)  
Sorry for that. Reading the statistics collector manual, I see there are plenty of parameters, and I'm not sure which
oneof them are you interested in, or if there's a query to collect them... 


This is the explain for the query after clearing the cache (name of table has changed, not a mistake...)

explain analyze select * from entity2document  where name='Acetaminophen';
                                                                 QUERY PLAN
              


--------------------------------------------------------------------------------------------------------------------------------------
-------
 Bitmap Heap Scan on entity2document  (cost=104.47..17914.96 rows=4632 width=138) (actual time=62.811..12208.446
rows=2845loops=1) 
   Recheck Cond: ((name)::text = 'Acetaminophen'::text)
   ->  Bitmap Index Scan on entity2document_name_index  (cost=0.00..103.31 rows=4632 width=0) (actual
time=34.357..34.357rows=2845 lo 
ops=1)
         Index Cond: ((name)::text = 'Acetaminophen'::text)
 Total runtime: 12216.115 ms
(5 rows)

It's much better now than with old server (39756.507 ms) however still high. I'd like to improve it...
Thank you very much.

Cheers,

Andrés

PS: Also notice that this is a query after denormalizing the database to avoid joins of very big tables. Once the
performanceis good enough I'd like to normalize it again if it's possible... :-) 






El Mar 20, 2014, a las 12:30 AM, Venkata Balaji Nagothi escribió:

> Sorry, i have not been following this since sometime now.
>
> Hardware configuration is better now. You were running on 8.3.x, can you please help us know what version of Postgres
isthis ? 
>
> Did you collect latest statistics and performed VACUUM after migration ?
>
> Can you get us the EXPLAIN plan for "select * from entity2document2  where name='Acetaminophen' ; " ?
>
> Venkata Balaji N
>
> Sr. Database Administrator
> Fujitsu Australia


**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.