Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1W7prv-0004O8-2L for pgsql-performance@arkaria.postgresql.org; Mon, 27 Jan 2014 17:21:07 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1W7pru-0003yb-FT for pgsql-performance@arkaria.postgresql.org; Mon, 27 Jan 2014 17:21:06 +0000 Received: from makus.postgresql.org ([2001:4800:7903:4::125]) by malur.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1W7prs-0003yT-U1 for pgsql-performance@postgresql.org; Mon, 27 Jan 2014 17:21:05 +0000 Received: from nm10-vm3.bullet.mail.gq1.yahoo.com ([98.136.218.94]) by makus.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1W7prn-000611-Bi for pgsql-performance@postgresql.org; Mon, 27 Jan 2014 17:21:04 +0000 Received: from [98.137.12.190] by nm10.bullet.mail.gq1.yahoo.com with NNFMP; 27 Jan 2014 17:20:57 -0000 Received: from [98.137.12.208] by tm11.bullet.mail.gq1.yahoo.com with NNFMP; 27 Jan 2014 17:20:57 -0000 Received: from [127.0.0.1] by omp1016.mail.gq1.yahoo.com with NNFMP; 27 Jan 2014 17:20:57 -0000 X-Yahoo-Newman-Property: ymail-3 X-Yahoo-Newman-Id: 720922.15759.bm@omp1016.mail.gq1.yahoo.com Received: (qmail 14917 invoked by uid 60001); 27 Jan 2014 17:20:57 -0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s1024; t=1390843257; bh=MsdSw2D3Fb/C7Cw60bEvE/2LioahdqKpwhoRXeoNFkk=; h=X-YMail-OSG:Received:X-Rocket-MIMEInfo:X-Mailer:References:Message-ID:Date:From:Reply-To:Subject:To:In-Reply-To:MIME-Version:Content-Type; b=gn5Is0YKUC/Xk2uGUwEOoyy0RgikuxpkDy86t++YeVbnRw7MMF07VU4Y8MAQ9Tplp/2zaHCgGnfcmnxmfFeWKQsrB2bwYF4im5R72xK6/a9iX38WOjSWuC0JO7MwtxxwlgQ3IxuM8NLqc5kd6CUm7w13UDYv5gF3TEvZJy2EoYU= DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com; h=X-YMail-OSG:Received:X-Rocket-MIMEInfo:X-Mailer:References:Message-ID:Date:From:Reply-To:Subject:To:In-Reply-To:MIME-Version:Content-Type; b=HE4df1HTQklyYj+wx2niFLPd9s19HhPgAyfXlEhatkfJ4DF6WYSCo6IPVZ/0ViC+Sj3mZQEfbNvJpFaqpT0lFqqnbEFpuzmKf0bf2jIM+2cRcc6Mc0GusYsLgzH+wgbmtzn2dNiQS0BDB12/5ipt1ekLIgkDapCRQFFoFAIDShk=; X-YMail-OSG: eqDvyM4VM1n7Vxo6UtAPPlS1NIcDxm6Lsd_QaBBCBkLDjcZ T7ywncljs8S04m4lHlLVEovUiUoDBvO8llmIxwQYhMhf4ulxdm_xUCcsv7u0 rrh1eMDQ_ZMXd1qqm9u_9gvofrMwf1AposjKTHPhxscu2kwGuDUsIuKlbc.v PI2MbQNYJV_oG2n9A_lvx8UQn42aCAUL2MhWlJz.4f9aOGsswRy3azh__B.X _4CJJqoi2f9gU8Wu6qCSQ20f6w_DOgbw5SjQiqSMayjrg.sKe4E7dnRZE2_f eS0rmdUaMwZ.DAtvSI5ajhUXYTXUmI2Tv0lQieDIn4v7J36qobO6A6h6RFYh aDQv918qu0FMU_XIkK0WlkZ35wPIzoKtPxFbsRkgSHyrXFhYCIZcBBar3ILV fl06FQbgicELwjWIeP_m3JOeM60Sr7xBM15pyMULoHTN8IIGB7EfFf7a5Gwt NlTivmX1m.5pXzS3D_oj.FUegcN0CfjKiGTQwcm3RWJ4O5ViofY1K_pq66_5 7wwiky.mKCHOirzDwCx1wm7OF5v.f3Ah.Ch3re0LtHWiJpyn7PzsabXFOcwM 8YS8wRJvKJjvRo3JikjJceEC2b.v4ftfi44gG_5CqpF.hGVrjlSkI1kxMM_g brE0Fu.oVc5XnOKMt4EEUV7plkPzE.T.JtDWZpR0X.FRQ3ZqfiScNR5zs7CK ofHZaaz72Xch3RAxM5ow3TAcjGRcscGUujg9ldbMCki74vi5KlTK8xxwrWKp R_wJ6Go4KZ6TwoKEQDqwn6T2fjNZsvJgDdWJhja7g_Xe3cXM3ePtSn2T3Pdz O8uDSl_yx8jFwZNnDLnZcQR68XknkMOj8ymQpuDyvpTYfYLWT_5Pa0LaH7Wp s9VghqFpbZBCiCrRnX6HbxRSGw2BcOWJpiXn6P9VHwVtlEOtce0nm7hEfla0 rHIlpuHuLHwSFNmksV.XvtaC1rWbLsJVfFBDs.muOibB0efENioPyUUDhblm evx91adfIqUX.dOJs2WrJY_4Gnupb8kx5e6ZzcfrG3.88l0TOBRQhrKa9US7 _csRnFx7IWeD47P4ShVaZyc_GwWDcyk.d8SJ_qVC3.3kRoqUbo5O4jpKvW4c eggaf67pvx8wzRcaMW54nI7QclEI8esEJzpghA12h_8dQ Received: from [80.152.211.89] by web164806.mail.gq1.yahoo.com via HTTP; Mon, 27 Jan 2014 09:20:57 PST X-Rocket-MIMEInfo: 002.001, SGVsbG8gU3RlbGlhbizCoAoKSGF2ZSB5b3UgdHJpZWQgdG8gdXNlIGZ1bmNfdGFibGUgbW9kdWxlPywgSSB0aGluayBpdCB3aWxsIGhlbHAgeW91IHRvIGVsaW1pbmF0ZSBhbGwgdGhlIGpvaW5zLgoKUmVnYXJkcwoKCgpPbiBNb25kYXksIEphbnVhcnkgMjcsIDIwMTQgNTo1NCBQTSwgU3RlbGlhbiBJYW5jdSA8c3RlbGlhbkBpYW5jdS5jaD4gd3JvdGU6CiAKSGVsbG8sCgpJIGhhdmUgUG9zdHJncmVzIDkuMyBydW5uaW5nIG9uIGEgTGludXggbWFjaGluZSB3aXRoIDMyR0IgUkFNLiBJIGhhdmUgYQpmYWlybHkBMAEBAQE- X-Mailer: YahooMailWebService/0.8.174.629 References: <1390833860.7369.75825537.0CAF7BA8@webmail.messagingengine.com> Message-ID: <1390843257.23746.YahooMailNeo@web164806.mail.gq1.yahoo.com> Date: Mon, 27 Jan 2014 09:20:57 -0800 (PST) From: salah jubeh Reply-To: salah jubeh Subject: Re: Slow query (wrong index used maybe) To: Stelian Iancu , "pgsql-performance@postgresql.org" In-Reply-To: <1390833860.7369.75825537.0CAF7BA8@webmail.messagingengine.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="1272841393-1593615698-1390843257=:23746" X-Pg-Spam-Score: -2.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 --1272841393-1593615698-1390843257=:23746 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: quoted-printable Hello Stelian,=A0=0A=0AHave you tried to use func_table module?, I think it= will help you to eliminate all the joins.=0A=0ARegards=0A=0A=0A=0AOn Monda= y, January 27, 2014 5:54 PM, Stelian Iancu wrote:=0A =0A= Hello,=0A=0AI have Postrgres 9.3 running on a Linux machine with 32GB RAM. = I have a=0Afairly large database (some tables with approx. 1 mil. records) = and I=0Ahave the following query:=0A=0A=A0 =A0 SELECT * FROM (=0A=A0 =A0 SE= LECT DISTINCT c.ext_content_id AS type_1_id,=0A=A0 =A0 "substring"(c.ext_co= ntent_id::text, 1, 13) AS type_1_album_id,=0A=A0 =A0 cm1.value AS type_1_ar= tist,=0A=A0 =A0 cm2.value AS type_1_title,=0A=A0 =A0 cm4.value AS type_1_du= ration,=0A=A0 =A0 pm1.value AS type_1_icpn,=0A=A0 =A0 cm3.value AS type_1_i= src,=0A=A0 =A0 c.provider AS type_1_provider,=0A=A0 =A0 to_number(cm5.value= ::text, '999999'::text) AS type_2_set_number,=0A=A0 =A0 to_number(cm6.value= ::text, '999999'::text) AS type_2_track_number,=0A=A0 =A0 cm7.value AS type= _6_availability_ppd,=0A=A0 =A0 cm12.value AS type_6_availability_sub,=0A=A0= =A0 cm9.value AS type_1_language,=0A=A0 =A0 cm11.value AS type_1_label_rep= orting_id,=0A=A0 =A0 cm13.value AS type_1_parent_isrc=0A=A0 =A0 FROM conten= t c=0A=A0 =A0 LEFT JOIN content_metadata cm1 ON c.content_id =3D cm1.conten= t_id AND=0A=A0 =A0 cm1.name::text =3D 'track_artist'::text=0A=A0 =A0 LEFT J= OIN content_metadata cm2 ON c.content_id =3D cm2.content_id AND=0A=A0 =A0 c= m2.name::text =3D 'track_title'::text=0A=A0 =A0 LEFT JOIN content_metadata = cm3 ON c.content_id =3D cm3.content_id AND=0A=A0 =A0 cm3.name::text =3D 'tr= ack_isrc'::text=0A=A0 =A0 LEFT JOIN content_metadata cm4 ON c.content_id = =3D cm4.content_id AND=0A=A0 =A0 cm4.name::text =3D 'track_duration'::text= =0A=A0 =A0 LEFT JOIN content_metadata cm5 ON c.content_id =3D cm5.content_i= d AND=0A=A0 =A0 cm5.name::text =3D 'set_number'::text=0A=A0 =A0 LEFT JOIN c= ontent_metadata cm6 ON c.content_id =3D cm6.content_id AND=0A=A0 =A0 cm6.na= me::text =3D 'track_number'::text=0A=A0 =A0 LEFT JOIN content_metadata cm7 = ON c.content_id =3D cm7.content_id AND=0A=A0 =A0 cm7.name::text =3D 'unlimi= ted'::text=0A=A0 =A0 LEFT JOIN content_metadata cm9 ON c.content_id =3D cm9= .content_id AND=0A=A0 =A0 cm9.name::text =3D 'language'::text=0A=A0 =A0 LEF= T JOIN content_metadata cm10 ON c.content_id =3D cm10.content_id=0A=A0 =A0 = AND cm10.name::text =3D 'import_date'::text=0A=A0 =A0 LEFT JOIN content_met= adata cm11 ON c.content_id =3D cm11.content_id=0A=A0 =A0 AND cm11.name::tex= t =3D 'label_reporting_id'::text=0A=A0 =A0 LEFT JOIN content_metadata cm12 = ON c.content_id =3D cm12.content_id=0A=A0 =A0 AND cm12.name::text =3D 'subs= cription'::text=0A=A0 =A0 LEFT JOIN content_metadata cm13 ON c.content_id = =3D cm13.content_id=0A=A0 =A0 AND cm13.name::text =3D 'parent_isrc'::text,= =0A=A0 =A0 product p=0A=A0 =A0 LEFT JOIN product_metadata pm4 ON p.product_= id =3D pm4.product_id AND=0A=A0 =A0 pm4.name::text =3D 'product_title'::tex= t=0A=A0 =A0 LEFT JOIN product_metadata pm1 ON p.product_id =3D pm1.product_= id AND=0A=A0 =A0 pm1.name::text =3D 'upc'::text=0A=A0 =A0 WHERE p.ext_produ= ct_id::text =3D substr(c.ext_content_id::text, 1, 13)=0A=A0 =A0 ) view=0A= =A0 =A0 WHERE type_1_id=3D'1-111-1027897-01-001';=0A=0ABelow are the defini= tions of the tables involved.=0A=0AContent:=0A=0A=A0 =A0 =A0 =A0 =A0 =A0 = =A0 =A0 =A0 =A0 =A0 Table "public.content"=0A=A0 =A0 =A0 =A0 Column=A0 =A0= =A0 |=A0 =A0 =A0 =A0 =A0 =A0 Type=A0 =A0 =A0 =A0 =A0 =A0 | Modifiers=0A= =A0 =A0 -----------------+-----------------------------+-----------=0A=A0 = =A0 content_id=A0 =A0 =A0 | bigint=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 = =A0 | not null=0A=A0 =A0 status=A0 =A0 =A0 =A0 =A0 | character varying(3)= =A0 =A0 =A0 =A0 | not null=0A=A0 =A0 display_name=A0 =A0 | character varyi= ng(1024)=A0 =A0 | not null=0A=A0 =A0 ext_content_id=A0 | character varyin= g(64)=A0 =A0 =A0 | not null=0A=A0 =A0 provider=A0 =A0 =A0 =A0 | character= varying(128)=A0 =A0 =A0 | not null=0A=A0 =A0 last_updated_by | character = varying(30)=A0 =A0 =A0 | not null=0A=A0 =A0 last_updated_on | timestamp w= ithout time zone | not null=0A=A0 =A0 created_by=A0 =A0 =A0 | character va= rying(30)=A0 =A0 =A0 | not null=0A=A0 =A0 created_on=A0 =A0 =A0 | timesta= mp without time zone | not null=0A=A0 =A0 Indexes:=0A=A0 =A0 =A0 =A0 "conte= nt_pkey" PRIMARY KEY, btree (content_id)=0A=A0 =A0 =A0 =A0 "ak_key_2_conten= t" UNIQUE, btree (ext_content_id, provider)=0A=A0 =A0 =A0 =A0 "index_conten= t_01" UNIQUE, btree (ext_content_id)=0A=A0 =A0 Foreign-key constraints:=0A= =A0 =A0 =A0 =A0 "fk_content_01" FOREIGN KEY (provider) REFERENCES=0A=A0 =A0= =A0 =A0 provider(ext_provider_id)=0A=A0 =A0 Referenced by:=0A=A0 =A0 =A0 = =A0 TABLE "content_metadata" CONSTRAINT "fk_content_metadata_01"=0A=A0 =A0 = =A0 =A0 FOREIGN KEY (content_id) REFERENCES content(content_id)=0A=A0 =A0 = =A0 =A0 TABLE "packaged" CONSTRAINT "fk_packaged_reference_content"=0A=A0 = =A0 =A0 =A0 FOREIGN KEY (content_id) REFERENCES content(content_id)=0A=A0 = =A0 =A0 =A0 TABLE "product_content" CONSTRAINT "fk_product_content_01"=0A= =A0 =A0 =A0 =A0 FOREIGN KEY (content_id) REFERENCES content(content_id)=0A= =A0 =A0 Triggers:=0A=A0 =A0 =A0 =A0 td_content BEFORE DELETE ON content FOR= EACH ROW EXECUTE=0A=A0 =A0 =A0 =A0 PROCEDURE trigger_fct_td_content()=0A= =A0 =A0 =A0 =A0 ti_content BEFORE INSERT ON content FOR EACH ROW EXECUTE=0A= =A0 =A0 =A0 =A0 PROCEDURE trigger_fct_ti_content()=0A=A0 =A0 =A0 =A0 tu_con= tent BEFORE UPDATE ON content FOR EACH ROW EXECUTE=0A=A0 =A0 =A0 =A0 PROCED= URE trigger_fct_tu_content()=0A=A0 =A0 =A0 =A0 tu_content_tree BEFORE UPDAT= E ON content FOR EACH ROW EXECUTE=0A=A0 =A0 =A0 =A0 PROCEDURE trigger_fct_t= u_content_tree()=0A=0AProduct:=0A=0A=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 Tab= le "public.product"=0A=A0 =A0 =A0 =A0 Column=A0 =A0 =A0 |=A0 =A0 =A0 =A0 = =A0 =A0 Type=A0 =A0 =A0 =A0 =A0 =A0 | Modifiers=0A=A0 =A0 ----------------= -+-----------------------------+-----------=0A=A0 =A0 product_id=A0 =A0 = =A0 | bigint=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 | not null=0A=A0 = =A0 status=A0 =A0 =A0 =A0 =A0 | character varying(3)=A0 =A0 =A0 =A0 | not = null=0A=A0 =A0 display_name=A0 =A0 | character varying(1024)=A0 =A0 | not= null=0A=A0 =A0 ext_product_id=A0 | character varying(64)=A0 =A0 =A0 | no= t null=0A=A0 =A0 last_updated_by | character varying(30)=A0 =A0 =A0 | not= null=0A=A0 =A0 last_updated_on | timestamp without time zone | not null= =0A=A0 =A0 created_by=A0 =A0 =A0 | character varying(30)=A0 =A0 =A0 | not= null=0A=A0 =A0 created_on=A0 =A0 =A0 | timestamp without time zone | not = null=0A=A0 =A0 Indexes:=0A=A0 =A0 =A0 =A0 "product_pkey" PRIMARY KEY, btree= (product_id)=0A=A0 =A0 =A0 =A0 "ak_key_2_product" UNIQUE, btree (ext_produ= ct_id)=0A=A0 =A0 Referenced by:=0A=A0 =A0 =A0 =A0 TABLE "contract_product" = CONSTRAINT "fk_contract_product_02"=0A=A0 =A0 =A0 =A0 FOREIGN KEY (product_= id) REFERENCES product(product_id)=0A=A0 =A0 =A0 =A0 TABLE "offer_product" = CONSTRAINT "fk_offer_product_01" FOREIGN=0A=A0 =A0 =A0 =A0 KEY (product_id)= REFERENCES product(product_id)=0A=A0 =A0 =A0 =A0 TABLE "product_metadata" = CONSTRAINT=0A=A0 =A0 =A0 =A0 "fk_product__reference_product" FOREIGN KEY (p= roduct_id)=0A=A0 =A0 =A0 =A0 REFERENCES product(product_id)=0A=A0 =A0 =A0 = =A0 TABLE "product_content" CONSTRAINT "fk_product_content_02"=0A=A0 =A0 = =A0 =A0 FOREIGN KEY (product_id) REFERENCES product(product_id)=0A=A0 =A0 T= riggers:=0A=A0 =A0 =A0 =A0 td_product BEFORE DELETE ON product FOR EACH ROW= EXECUTE=0A=A0 =A0 =A0 =A0 PROCEDURE trigger_fct_td_product()=0A=A0 =A0 =A0= =A0 ti_product BEFORE INSERT ON product FOR EACH ROW EXECUTE=0A=A0 =A0 =A0= =A0 PROCEDURE trigger_fct_ti_product()=0A=A0 =A0 =A0 =A0 tu_product BEFORE= UPDATE ON product FOR EACH ROW EXECUTE=0A=A0 =A0 =A0 =A0 PROCEDURE trigger= _fct_tu_product()=0A=A0 =A0 =A0 =A0 tu_product_tree BEFORE UPDATE ON produc= t FOR EACH ROW EXECUTE=0A=A0 =A0 =A0 =A0 PROCEDURE trigger_fct_tu_product_t= ree()=0A=0AProduct_metadata:=0A=0A=A0 =A0 =A0 =A0 =A0 =A0 =A0 Table "public= .product_metadata"=0A=A0 =A0 =A0 =A0 Column=A0 =A0 =A0 |=A0 =A0 =A0 =A0 = =A0 =A0 Type=A0 =A0 =A0 =A0 =A0 =A0 | Modifiers=0A=A0 =A0 ----------------= -+-----------------------------+-----------=0A=A0 =A0 product_id=A0 =A0 = =A0 | bigint=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 | not null=0A=A0 = =A0 name=A0 =A0 =A0 =A0 =A0 =A0 | character varying(64)=A0 =A0 =A0 | not = null=0A=A0 =A0 distributor_id=A0 | bigint=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 = =A0 =A0 =A0 |=0A=A0 =A0 value=A0 =A0 =A0 =A0 =A0 | character varying(4000= )=A0 =A0 |=0A=A0 =A0 created_on=A0 =A0 =A0 | timestamp without time zone = | not null=0A=A0 =A0 created_by=A0 =A0 =A0 | character varying(30)=A0 =A0 = =A0 | not null=0A=A0 =A0 last_updated_on | timestamp without time zone | = not null=0A=A0 =A0 last_updated_by | character varying(30)=A0 =A0 =A0 | n= ot null=0A=A0 =A0 Indexes:=0A=A0 =A0 =A0 =A0 "idx_product_metadata_03" btre= e (name, value)=0A=A0 =A0 =A0 =A0 "index_product_metadata_02" btree (produc= t_id, name)=0A=A0 =A0 =A0 =A0 "index_product_metadata_cid" btree (product_i= d)=0A=A0 =A0 Foreign-key constraints:=0A=A0 =A0 =A0 =A0 "fk_product__refere= nce_product" FOREIGN KEY (product_id)=0A=A0 =A0 =A0 =A0 REFERENCES product(= product_id)=0A=A0 =A0 =A0 =A0 "fk_product_metadata_02" FOREIGN KEY (distrib= utor_id) REFERENCES=0A=A0 =A0 =A0 =A0 operator(operator_id)=0A=A0 =A0 Trigg= ers:=0A=A0 =A0 =A0 =A0 td_product_metadata BEFORE DELETE ON product_metadat= a FOR EACH=0A=A0 =A0 =A0 =A0 ROW EXECUTE PROCEDURE trigger_fct_td_product_m= etadata()=0A=A0 =A0 =A0 =A0 ti_product_metadata BEFORE INSERT ON product_me= tadata FOR EACH=0A=A0 =A0 =A0 =A0 ROW EXECUTE PROCEDURE trigger_fct_ti_prod= uct_metadata()=0A=A0 =A0 =A0 =A0 tu_product_metadata BEFORE UPDATE ON produ= ct_metadata FOR EACH=0A=A0 =A0 =A0 =A0 ROW EXECUTE PROCEDURE trigger_fct_tu= _product_metadata()=0A=0AContent_metadata:=0A=0A=A0 =A0 =A0 =A0 =A0 =A0 =A0= Table "public.content_metadata"=0A=A0 =A0 =A0 =A0 Column=A0 =A0 =A0 |=A0 = =A0 =A0 =A0 =A0 =A0 Type=A0 =A0 =A0 =A0 =A0 =A0 | Modifiers=0A=A0 =A0 ----= -------------+-----------------------------+-----------=0A=A0 =A0 content_= id=A0 =A0 =A0 | bigint=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 | not nul= l=0A=A0 =A0 name=A0 =A0 =A0 =A0 =A0 =A0 | character varying(64)=A0 =A0 =A0= | not null=0A=A0 =A0 distributor_id=A0 | bigint=A0 =A0 =A0 =A0 =A0 =A0 = =A0 =A0 =A0 =A0 =A0 |=0A=A0 =A0 value=A0 =A0 =A0 =A0 =A0 | character vary= ing(4000)=A0 =A0 |=0A=A0 =A0 last_updated_by | character varying(30)=A0 = =A0 =A0 | not null=0A=A0 =A0 last_updated_on | timestamp without time zon= e | not null=0A=A0 =A0 created_by=A0 =A0 =A0 | character varying(30)=A0 = =A0 =A0 | not null=0A=A0 =A0 created_on=A0 =A0 =A0 | timestamp without ti= me zone | not null=0A=A0 =A0 Indexes:=0A=A0 =A0 =A0 =A0 "idx_content_metada= ta_03" btree (name, value)=0A=A0 =A0 =A0 =A0 "idx_content_metadata_04" btre= e (content_id, name, value)=0A=A0 =A0 =A0 =A0 "index_content_metadata_02" b= tree (content_id, name)=0A=A0 =A0 =A0 =A0 "index_content_metadata_cid" btre= e (content_id)=0A=A0 =A0 Foreign-key constraints:=0A=A0 =A0 =A0 =A0 "fk_con= tent_metadata_01" FOREIGN KEY (content_id) REFERENCES=0A=A0 =A0 =A0 =A0 con= tent(content_id)=0A=A0 =A0 =A0 =A0 "fk_content_metadata_02" FOREIGN KEY (di= stributor_id) REFERENCES=0A=A0 =A0 =A0 =A0 operator(operator_id)=0A=A0 =A0 = Triggers:=0A=A0 =A0 =A0 =A0 td_content_metadata BEFORE DELETE ON content_me= tadata FOR EACH=0A=A0 =A0 =A0 =A0 ROW EXECUTE PROCEDURE trigger_fct_td_cont= ent_metadata()=0A=A0 =A0 =A0 =A0 ti_content_metadata BEFORE INSERT ON conte= nt_metadata FOR EACH=0A=A0 =A0 =A0 =A0 ROW EXECUTE PROCEDURE trigger_fct_ti= _content_metadata()=0A=A0 =A0 =A0 =A0 tu_content_metadata BEFORE UPDATE ON = content_metadata FOR EACH=0A=A0 =A0 =A0 =A0 ROW EXECUTE PROCEDURE trigger_f= ct_tu_content_metadata()=0A=0AThe query as it is takes approx. 35 seconds, = which is very bad. If I=0Atake out the line:=0A=0A=A0 =A0 LEFT JOIN product= _metadata pm4 ON p.product_id =3D pm4.product_id AND=0A=A0 =A0 pm4.name::te= xt =3D 'product_title'::text=0A=0Athen the time is under 1 second. =0A=0AHe= re you can see the plan for the query (as it is here, i.e. when it=0Atakes = a lot of time): http://explain.depesz.com/s/K9s=0A=0AAs far as I can see, t= he wrong index is used. In the lines=0A=0A=A0 =A0 "->=A0 Bitmap Heap Scan o= n product_metadata pm4 =0A=A0 =A0 (cost=3D6014.11..257694.54 rows=3D579474 = width=3D8) (actual=0A=A0 =A0 time=3D282.364..13005.344 rows=3D557834 loops= =3D1)"=0A=A0 =A0 =A0 =A0 "Recheck Cond: ((name)::text =3D 'product_title'::= text)"=0A=A0 =A0 =A0 =A0 "Buffers: shared read=3D175851"=0A=A0 =A0 =A0 =A0 = "->=A0 Bitmap Index Scan on idx_product_metadata_03 =0A=A0 =A0 =A0 =A0 (cos= t=3D0.00..5869.24 rows=3D579474 width=3D0) (actual=0A=A0 =A0 =A0 =A0 time= =3D222.724..222.724 rows=3D557834 loops=3D1)"=0A=A0 =A0 =A0 =A0 =A0 =A0 "In= dex Cond: ((name)::text =3D 'product_title'::text)"=0A=A0 =A0 =A0 =A0 =A0 = =A0 "Buffers: shared read=3D3953"=0A=0Ait can be seen that it uses idx_prod= uct_metadata_03 which is on (name,=0Avalue). Shouldn't it use index_product= _metadata_02 which is on=0A(product_id, name)? =0A=0AOr is there another re= ason why this query is so slow? =0A=0AI have changed some of the default se= ttings of Postgres as follows:=0A=0Arandom_page_cost =3D 1.4=0Acpu_index_tu= ple_cost =3D 0.00001=0Aeffective_cache_size =3D 256MB=0Awork_mem =3D 300MB= =0A=0AIf you need any other information, let me know.=0A=0AThanks in advanc= e!=0A=0AWith regards,=0AStelian Iancu=0A=0A=0A-- =0ASent via pgsql-performa= nce mailing list (pgsql-performance@postgresql.org)=0ATo make changes to yo= ur subscription:=0Ahttp://www.postgresql.org/mailpref/pgsql-performance --1272841393-1593615698-1390843257=:23746 Content-Type: text/html; charset=iso-8859-1 Content-Transfer-Encoding: quoted-printable
Hello Stelian, 

Have you tried to use func_table module?, I= think it will help you to eliminate all the joins.

Regards


On Monday, January 27, 2014 5:54 PM, Stelian Iancu <stelia= n@iancu.ch> wrote:
He= llo,

I have Postrgres 9.3 running on a Linux machine with 32GB RAM. = I have a
fairly large database (some tables with approx. 1 mil. records)= and I
have the following query:

    SELECT * FROM (    SELECT DISTINCT c.ext_content_id AS type_1_id,
  &nb= sp; "substring"(c.ext_content_id::text, 1, 13) AS type_1_album_id,
 = ;   cm1.value AS type_1_artist,
    cm2.value AS type_1_t= itle,
    cm4.value AS type_1_duration,
    pm1.v= alue AS type_1_icpn,
    cm3.value AS type_1_isrc,
   = ; c.provider AS type_1_provider,
    to_number(cm5.value::text= , '999999'::text) AS type_2_set_number,
    to_number(cm6.valu= e::text, '999999'::text) AS type_2_track_number,
    cm7.value= AS type_6_availability_ppd,
    cm12.value AS type_6_availabi= lity_sub,
    cm9.value AS type_1_language,
    c= m11.value AS type_1_label_reporting_id,
    cm13.value AS type= _1_parent_isrc
    FROM content c
    LEFT JOIN c= ontent_metadata cm1 ON c.content_id =3D cm1.content_id AND
   = cm1.name::text =3D 'track_artist'::text
    LEFT JOIN content= _metadata cm2 ON c.content_id =3D cm2.content_id AND
    cm2.n= ame::text =3D 'track_title'::text
    LEFT JOIN content_metada= ta cm3 ON c.content_id =3D cm3.content_id AND
    cm3.name::te= xt =3D 'track_isrc'::text
    LEFT JOIN content_metadata cm4 ON = c.content_id =3D cm4.content_id AND
    cm4.name::text =3D 'tr= ack_duration'::text
    LEFT JOIN content_metadata cm5 ON c.co= ntent_id =3D cm5.content_id AND
    cm5.name::text =3D 'set_nu= mber'::text
    LEFT JOIN content_metadata cm6 ON c.content_id= =3D cm6.content_id AND
    cm6.name::text =3D 'track_number':= :text
    LEFT JOIN content_metadata cm7 ON c.content_id =3D c= m7.content_id AND
    cm7.name::text =3D 'unlimited'::text
=     LEFT JOIN content_metadata cm9 ON c.content_id =3D cm9.conten= t_id AND
    cm9.name::text =3D 'language'::text
  &nb= sp; LEFT JOIN content_metadata cm10 ON c.content_id =3D cm10.content_id
=     AND cm10.name::text =3D 'import_date'::text
    = LEFT JOIN content_metadata cm11 ON c.content_id =3D cm11.content_id
&nbs= p;   AND cm11.name::text =3D 'label_reporting_id'::text
    LEFT JOIN = content_metadata cm12 ON c.content_id =3D cm12.content_id
    = AND cm12.name::text =3D 'subscription'::text
    LEFT JOIN con= tent_metadata cm13 ON c.content_id =3D cm13.content_id
    AND= cm13.name::text =3D 'parent_isrc'::text,
    product p
&nb= sp;   LEFT JOIN product_metadata pm4 ON p.product_id =3D pm4.product_i= d AND
    pm4.name::text =3D 'product_title'::text
  &= nbsp; LEFT JOIN product_metadata pm1 ON p.product_id =3D pm1.product_id AND=
    pm1.name::text =3D 'upc'::text
    WHERE p.e= xt_product_id::text =3D substr(c.ext_content_id::text, 1, 13)
  &nb= sp; ) view
    WHERE type_1_id=3D'1-111-1027897-01-001';
Below are the definitions of the tables involved.

Content:

=                      = ; Table "public.content"
        Column     = ; |            Type       = ;     | Modifiers
    -----------------+-----------= ------------------+-----------
    content_id    &n= bsp; | bigint                 =     | not null
    status      &nbs= p;   | character varying(3)        | not null
&= nbsp;   display_name    | character varying(1024)  &nb= sp; | not null
    ext_content_id  | character varying(= 64)      | not null
    provider   =     | character varying(128)      | not null
&= nbsp;   last_updated_by | character varying(30)      = | not null
    last_updated_on | timestamp without time zone | not null
    created_by      | ch= aracter varying(30)      | not null
    creat= ed_on      | timestamp without time zone | not null
 = ;   Indexes:
        "content_pkey" PRIMARY KEY= , btree (content_id)
        "ak_key_2_content" UNIQ= UE, btree (ext_content_id, provider)
        "index_= content_01" UNIQUE, btree (ext_content_id)
    Foreign-key con= straints:
        "fk_content_01" FOREIGN KEY (provi= der) REFERENCES
        provider(ext_provider_id)    Referenced by:
        TABLE "content= _metadata" CONSTRAINT "fk_content_metadata_01"
      &nbs= p; FOREIGN KEY (content_id) REFERENCES content(content_id)
   =     TABLE "packaged" CONSTRAINT "fk_packaged_reference_content"
        FOREIGN KEY= (content_id) REFERENCES content(content_id)
       = TABLE "product_content" CONSTRAINT "fk_product_content_01"
   = ;     FOREIGN KEY (content_id) REFERENCES content(content_id)
=     Triggers:
        td_content BEFORE DE= LETE ON content FOR EACH ROW EXECUTE
        PROCEDU= RE trigger_fct_td_content()
        ti_content BEFOR= E INSERT ON content FOR EACH ROW EXECUTE
        PRO= CEDURE trigger_fct_ti_content()
        tu_content B= EFORE UPDATE ON content FOR EACH ROW EXECUTE
       = PROCEDURE trigger_fct_tu_content()
        tu_conte= nt_tree BEFORE UPDATE ON content FOR EACH ROW EXECUTE
    &nbs= p;   PROCEDURE trigger_fct_tu_content_tree()

Product:

      =             Table "public.product"
  =       Column      |      &nbs= p;     Type            | Modifiers=
    -----------------+-----------------------------+---------= --
    product_id      | bigint    &= nbsp;                 | not null    status          | character varyin= g(3)        | not null
    display_name&n= bsp;   | character varying(1024)    | not null
  &n= bsp; ext_product_id  | character varying(64)      | n= ot null
    last_updated_by | character varying(30)  &nb= sp;   | not null
    last_updated_on | timestamp without time zone | not null
    created_by    &nb= sp; | character varying(30)      | not null
   = ; created_on      | timestamp without time zone | not null<= br>    Indexes:
        "product_pkey" PRI= MARY KEY, btree (product_id)
        "ak_key_2_produ= ct" UNIQUE, btree (ext_product_id)
    Referenced by:
 = ;       TABLE "contract_product" CONSTRAINT "fk_contract_pro= duct_02"
        FOREIGN KEY (product_id) REFERENCES= product(product_id)
        TABLE "offer_product" C= ONSTRAINT "fk_offer_product_01" FOREIGN
        KEY = (product_id) REFERENCES product(product_id)
        = TABLE "product_metadata" CONSTRAINT
        "fk_prod= uct__reference_product" FOREIGN KEY (product_id)
        REFERENCES product(product_id)
       = ; TABLE "product_content" CONSTRAINT "fk_product_content_02"
  &nbs= p;     FOREIGN KEY (product_id) REFERENCES product(product_id)    Triggers:
        td_product BEFORE D= ELETE ON product FOR EACH ROW EXECUTE
        PROCED= URE trigger_fct_td_product()
        ti_product BEFO= RE INSERT ON product FOR EACH ROW EXECUTE
        PR= OCEDURE trigger_fct_ti_product()
        tu_product = BEFORE UPDATE ON product FOR EACH ROW EXECUTE
       = ; PROCEDURE trigger_fct_tu_product()
        tu_prod= uct_tree BEFORE UPDATE ON product FOR EACH ROW EXECUTE
    &nb= sp;   PROCEDURE trigger_fct_tu_product_tree()

Product_metadata:=

              Table "public.product_metadata"
        Column  &nb= sp;   |            Type    &nb= sp;       | Modifiers
    -----------------+--= ---------------------------+-----------
    product_id  =     | bigint              &nbs= p;       | not null
    name    &nbs= p;       | character varying(64)      | not = null
    distributor_id  | bigint      &n= bsp;               |
    v= alue          | character varying(4000)  &nb= sp; |
    created_on      | timestamp without= time zone | not null
    created_by      | ch= aracter varying(30)      | not null
    last_updated_on | timestamp without time zone | not null
   = last_updated_by | character varying(30)      | not null    Indexes:
        "idx_product_metada= ta_03" btree (name, value)
        "index_product_me= tadata_02" btree (product_id, name)
        "index_p= roduct_metadata_cid" btree (product_id)
    Foreign-key constr= aints:
        "fk_product__reference_product" FOREI= GN KEY (product_id)
        REFERENCES product(produ= ct_id)
        "fk_product_metadata_02" FOREIGN KEY = (distributor_id) REFERENCES
        operator(operato= r_id)
    Triggers:
        td_product_= metadata BEFORE DELETE ON product_metadata FOR EACH
     =   ROW EXECUTE PROCEDURE trigger_fct_td_product_metadata()
        ti_produc= t_metadata BEFORE INSERT ON product_metadata FOR EACH
    &nbs= p;   ROW EXECUTE PROCEDURE trigger_fct_ti_product_metadata()
 =       tu_product_metadata BEFORE UPDATE ON product_metadata= FOR EACH
        ROW EXECUTE PROCEDURE trigger_fct_= tu_product_metadata()

Content_metadata:

      =         Table "public.content_metadata"
   = ;     Column      |        &n= bsp;   Type            | Modifiers
&= nbsp;   -----------------+-----------------------------+-----------    content_id      | bigint     =                 | not null
&nbs= p;   name            | character varying(64)      | not null
    distributor_= id  | bigint                &n= bsp;     |
    value        &nb= sp; | character varying(4000)    |
    last_updat= ed_by | character varying(30)      | not null
  &nb= sp; last_updated_on | timestamp without time zone | not null
  &nb= sp; created_by      | character varying(30)    &n= bsp; | not null
    created_on      | timesta= mp without time zone | not null
    Indexes:
    =     "idx_content_metadata_03" btree (name, value)
   = ;     "idx_content_metadata_04" btree (content_id, name, value)        "index_content_metadata_02" btree (content_id= , name)
        "index_content_metadata_cid" btree (content_id)
    Foreign-key constraints:
  &nb= sp;     "fk_content_metadata_01" FOREIGN KEY (content_id) REFEREN= CES
        content(content_id)
    &nb= sp;   "fk_content_metadata_02" FOREIGN KEY (distributor_id) REFERENCES=
        operator(operator_id)
    Trig= gers:
        td_content_metadata BEFORE DELETE ON c= ontent_metadata FOR EACH
        ROW EXECUTE PROCEDU= RE trigger_fct_td_content_metadata()
        ti_cont= ent_metadata BEFORE INSERT ON content_metadata FOR EACH
    &n= bsp;   ROW EXECUTE PROCEDURE trigger_fct_ti_content_metadata()
&nbs= p;       tu_content_metadata BEFORE UPDATE ON content_metada= ta FOR EACH
        ROW EXECUTE PROCEDURE trigger_fc= t_tu_content_metadata()

The query as it is takes approx. 35 seconds, which is very bad. If I
take out the line:

   = ; LEFT JOIN product_metadata pm4 ON p.product_id =3D pm4.product_id AND
=     pm4.name::text =3D 'product_title'::text

then the time= is under 1 second.

Here you can see the plan for the query (as it = is here, i.e. when it
takes a lot of time): http://explain.depesz.com/s/K9s
As far as I can see, the wrong index is used. In the lines

  =   "->  Bitmap Heap Scan on product_metadata pm4
  &nb= sp; (cost=3D6014.11..257694.54 rows=3D579474 width=3D8) (actual
  &= nbsp; time=3D282.364..13005.344 rows=3D557834 loops=3D1)"
    =     "Recheck Cond: ((name)::text =3D 'product_title'::text)"
&= nbsp;       "Buffers: shared read=3D175851"
   =     "->  Bitmap Index Scan on idx_product_metadata_03         (cost=3D0.00..5869.24 rows=3D579474 width=3D0) (actual
&n= bsp;       time=3D222.724..222.724 rows=3D557834 loops=3D1)"=
            "Index Cond: ((name)::text = =3D 'product_title'::text)"
            "B= uffers: shared read=3D3953"

it can be seen that it uses idx_product_= metadata_03 which is on (name,
value). Shouldn't it use index_product_me= tadata_02 which is on
(product_id, name)?

Or is there another re= ason why this query is so slow?

I have changed some of the default = settings of Postgres as follows:

random_page_cost =3D 1.4
cpu_ind= ex_tuple_cost =3D 0.00001
effective_cache_size =3D 256MB
work_mem =3D= 300MB

If you need any other information, let me know.

Thanks= in advance!

With regards,
Stelian Iancu


--
Sent v= ia pgsql-performance mailing list (pgsql-performance@postgre= sql.org)
To make changes to your subscription:
http://www= .postgresql.org/mailpref/pgsql-performance


--1272841393-1593615698-1390843257=:23746--