Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1WIgcA-0003J0-BX for pgsql-performance@arkaria.postgresql.org; Wed, 26 Feb 2014 15:41:42 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1WIgc9-00012a-SD for pgsql-performance@arkaria.postgresql.org; Wed, 26 Feb 2014 15:41:41 +0000 Received: from makus.postgresql.org ([2001:4800:7903:4::125]) by malur.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1WIgc8-00012U-ST for pgsql-performance@postgresql.org; Wed, 26 Feb 2014 15:41:41 +0000 Received: from flash1.cnio.es ([193.147.150.45]) by makus.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1WIgc4-0007Io-Vl for pgsql-performance@postgresql.org; Wed, 26 Feb 2014 15:41:40 +0000 Received: from flash1.cnio.es (localhost [127.0.0.1]) by flash1.cnio.es (ESMTP Server) with ESMTP id 3ED3D19D23 for ; Wed, 26 Feb 2014 16:41:33 +0100 (CET) X-Spam-Checker-Version: SpamAssassin 3.2.5 (2008-06-10) on flash1.cnio.es X-Spam-Level: X-Spam-Status: No, score=-1.1 required=5.0 tests=ALL_TRUSTED,AWL,BAYES_00, FH_DATE_PAST_20XX autolearn=no version=3.2.5 Received: from owa2.cnio.es (owa2.cnio.es [192.168.10.8]) by flash1.cnio.es (ESMTP Server) with ESMTP for ; Wed, 26 Feb 2014 16:41:32 +0100 (CET) thread-index: Ac8zCTlArSPPU8XFRfq3TjkcFOm55w== Received: from [10.222.112.134] ([10.222.112.134]) by owa2.cnio.es over TLS secured channel with Microsoft SMTPSVC(6.0.3790.4675); Wed, 26 Feb 2014 16:41:32 +0100 From: "acanada" Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Subject: Query taking long time Date: Wed, 26 Feb 2014 16:41:31 +0100 Message-ID: <3AE16087-DE09-4EE0-BDF7-F2DFC68CB6CB@cnio.es> Content-Class: urn:content-classes:message Importance: normal X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.4913 To: MIME-Version: 1.0 X-Mailer: Apple Mail (2.1283) X-OriginalArrivalTime: 26 Feb 2014 15:41:32.0479 (UTC) FILETIME=[3938F4F0:01CF3309] X-Pg-Spam-Score: -1.5 (-) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-performance Precedence: bulk Sender: pgsql-performance-owner@postgresql.org Hello to everybody and thanks in advance to take a look to this message. I'm new in this list and with PostgreSQL.=20 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 info required 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 th= e query, formed by DQL, but coppied-pasted to the psql client (9.1.11, serv= er 8.3.8) explain analyze SELECT e0_.id AS id0, e0_.name AS name1, e0_.qualifier AS q= ualifier2, e0_."tagMethod" AS tagmethod3, e0_.curation AS curation4, e0_.cr= eated AS created5, e0_.updated AS updated6, d1_.id AS id7, d1_.kind AS kind= 8, d1_.uid AS uid9, d1_."sentenceId" AS sentenceid10, d1_.text AS text11, d= 1_.hepval AS hepval12, d1_.cardval AS cardval13, d1_.nephval AS nephval14, = d1_.phosval AS phosval15, d1_."patternCount" AS patterncount16, d1_."ruleSc= ore" AS rulescore17, d1_."hepTermNormScore" AS heptermnormscore18, d1_."hep= TermVarScore" AS heptermvarscore19, d1_.created AS created20, d1_.updated A= S updated21, e0_.document_id AS document_id22 FROM Entity2Document e0_ INNE= R JOIN documentold d1_ ON e0_.document_id =3D d1_.id WHERE e0_.name =3D'ran= itidine' AND e0_.qualifier =3D 'CompoundDict' AND d1_.hepval IS NOT NULL OR= DER BY d1_.hepval DESC limit 10; limtox=3D> \d+ documentold; Table "public.documentold" Column | Type | Modifiers | Storage |= Description=20 ------------------+--------------------------------+-----------+----------+= ------------- id | integer | not null | plain |= =20 kind | character varying(255) | not null | extended |= =20 uid | character varying(255) | not null | extended |= =20 sentenceId | character varying(255) | not null | extended |= =20 text | text | not null | extended |= =20 hepval | double precision | | plain |= =20 created | timestamp(0) without time zone | not null | plain |= =20 updated | timestamp(0) without time zone | | plain |= =20 cardval | double precision | | plain |= =20 nephval | double precision | | plain |= =20 phosval | double precision | | plain |= =20 patternCount | double precision | | plain |= =20 ruleScore | double precision | | plain |= =20 hepTermNormScore | double precision | | plain |= =20 hepTermVarScore | double precision | | plain |= =20 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" FO= REIGN KEY (document_id) REFERENCES documentold(id) TABLE "cytochrome2document" CONSTRAINT "fk_21f7636fc33f7837" FOREIGN KE= Y (document_id) REFERENCES documentold(id) TABLE "hepkeywordtermvariant2document" CONSTRAINT "fk_a316e36bc33f7837"= FOREIGN KEY (document_id) REFERENCES documentold(id) TABLE "entity2document" CONSTRAINT "fk_a6020c0dc33f7837" FOREIGN KEY (d= ocument_id) REFERENCES documentold(id) TABLE "specie2document" CONSTRAINT "fk_b6e551c8c33f7837" FOREIGN KEY (d= ocument_id) REFERENCES documentold(id) Has OIDs: no limtox=3D> \d+ entity2document; Table "= public.entity2document" Column | Type | = Modifiers | Storage | Description -------------+--------= ------------------------+---------------------------------+----------+-----= -------- id | integer | not null = | plain |=20 document_id | integer | = | plain |=20 name | character varying(255) | not null = | extended |=20 qualifier | character varying(255) | not null = | extended |=20 tagMethod | character varying(255) | default NULL::character var= ying | extended |=20 created | timestamp(0) without time zone | not null = | plain |=20 updated | timestamp(0) without time zone | = | plain |=20 curation | integer | = | plain |=20 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 p= roportion of NULLs(updated, patternCount, ruleScore, hepTermNormScore, hepT= ermVarScore) entity2document: 93.785.968 of rows. It has two columns with a large pr= oportion of NULLs (updated, curation) =20=20=20=20 None of the tables receive updates or deletes regularly =20=20=20=20 =20=20=20=20=20=20=20=20=20 = QUERY PLAN=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20 ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= -------------------- Limit (cost=3D387929.02..387929.05 rows=3D10 width=3D313) (actual time=3D= 55980.472..55980.476 rows=3D10 loops=3D1) -> Sort (cost=3D387929.02..387966.75 rows=3D15090 width=3D313) (actual= time=3D55980.471..55980.473 rows=3D10 loops=3D1) Sort Key: d1_.hepval Sort Method: top-N heapsort Memory: 28kB -> Nested Loop (cost=3D469.14..387602.93 rows=3D15090 width=3D31= 3) (actual time=3D96.716..55974.004 rows=3D2774 loops=3D1) -> Bitmap Heap Scan on entity2document e0_ (cost=3D469.14.= .54851.25 rows=3D15090 width=3D59) (actual time=3D51.299..8452.592 rows=3D2= 774 loops=3D1) Recheck Cond: (((name)::text =3D 'Cimetidine'::text) A= ND ((qualifier)::text =3D 'CompoundDict'::text)) -> Bitmap Index Scan on entity2document_name_qualifie= r_index (cost=3D0.00..465.36 rows=3D15090 width=3D0) (actual time=3D36.467= ..36.467 rows=3D2774 loops=3D1) Index Cond: (((name)::text =3D 'Cimetidine'::tex= t) AND ((qualifier)::text =3D 'CompoundDict'::text)) -> Index Scan using "DocumentOLD_pkey" on documentold d1_ = (cost=3D0.00..22.04 rows=3D1 width=3D254) (actual time=3D17.113..17.129 row= s=3D1 loops=3D2774) Index Cond: (d1_.id =3D e0_.document_id) Filter: (d1_.hepval IS NOT NULL) Total runtime: 55980.554 ms (13 rows) version=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20 ---------------------------------------------------------------------------= -------------------------- PostgreSQL 8.3.8 on x86_64-pc-linux-gnu, compiled by GCC gcc-4.4.real (Ubu= ntu 10.4.1-3ubuntu3) 10.4.1 =20 This query has been always slow. It's fast only when it's cached. Vacuum a= nd analyze have been done manually very recently =20 =20 =20 =20 SELECT name, current_setting(name), source FROM pg_settings WHERE source NOT IN ('default', 'override'); =20 name | current_setting | source=20=20=20= =20=20=20=20=20 ----------------------------+--------------------+---------------------- 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 =20 Setting the work_mem to 3000MB doesn't change anything... =20 Everything seems good to me but the Recheck Cond, because of the large amm= ount 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 new to PostgreSQL). I'd like to know what I am doing wrong and ho= w can I solve it... =20 Any help please? =20 Thank you very much, =20 Andr=E9s **NOTA DE CONFIDENCIALIDAD** Este correo electr=F3nico, y en su caso los fi= cheros adjuntos, pueden contener informaci=F3n protegida para el uso exclus= ivo de su destinatario. Se proh=EDbe la distribuci=F3n, reproducci=F3n o cu= alquier otro tipo de transmisi=F3n por parte de otra persona que no sea el = destinatario. Si usted recibe por error este correo, se ruega comunicarlo a= l 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 de= signated recipient named above. Distribution, reproduction or any other use= of this transmission by any party other than the intended recipient is pro= hibited. If you are not the intended recipient please contact the sender an= d delete all copies. --=20 Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance