X-Original-To: pgsql-sql@postgresql.org Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 0AD93475AE4 for ; Mon, 3 Feb 2003 10:33:13 -0500 (EST) Received: from rtlocal.trade-india.com (mail-relay.trade-india.com [203.196.129.235]) by postgresql.org (Postfix) with SMTP id 6B03E475ADE for ; Mon, 3 Feb 2003 10:33:06 -0500 (EST) Received: (qmail 8662 invoked from network); 3 Feb 2003 15:29:56 -0000 Received: from unknown (HELO system67.trade-india-local.com) (192.168.0.67) by infocom-236-129-del.trade-india.com with SMTP; 3 Feb 2003 15:29:56 -0000 From: "Rajesh Kumar Mallah." Organization: Infocom Network Limited. To: Tom Lane Subject: Re: cannot EXPLAIN query... Date: Mon, 3 Feb 2003 21:08:23 +0530 User-Agent: KMail/1.5 Cc: pgsql-sql@postgresql.org References: <200302031932.08326.mallah@trade-india.com> <14177.1044283565@sss.pgh.pa.us> In-Reply-To: <14177.1044283565@sss.pgh.pa.us> MIME-Version: 1.0 Content-Disposition: inline Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Message-Id: <200302032108.23554.mallah@trade-india.com> X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200302/56 X-Sequence-Number: 11454 It is PostgreSQL 7.3.0 on Linux. Sorry Postgresql has really made my VIEWS ugly. It wasnt' so when i fed them. I wish pgsql stores the create view defination some day , just like it does for indexes (pg_get_indexdef) Here is the EXPLAIN ANALYZE output of a query that is working on the view. i find that whenever i put any filter expression on the select for any feild it stops working. tradein_clients=3D# explain analyze SELECT * from shippers1 ; = QUERY PLAN ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ------------------------------------------ Hash Join (cost=3D31.67..26780.73 rows=3D2 width=3D339) (actual time=3D45= .18..6072.38 rows=3D732 loops=3D1) Hash Cond: ("outer".id =3D "inner".id) Join Filter: ("inner".source =3D "outer".source) -> Subquery Scan b (cost=3D0.00..26737.99 rows=3D492 width=3D307) (act= ual time=3D0.14..6015.04 rows=3D2293 loops=3D1) -> Append (cost=3D0.00..26737.99 rows=3D492 width=3D307) (actual= time=3D0.13..6001.13 rows=3D2293 loops=3D1) -> Subquery Scan "*SELECT* 1" (cost=3D0.00..6739.42 rows= =3D249 width=3D307) (actual time=3D0.12..2982.18 rows=3D321 loops=3D1) -> Index Scan using eyp_listing_category_id, eyp_list= ing_category_id, eyp_listing_category_id, eyp_listing_category_id, eyp_list= ing_category_id on eyp_listing (cost=3D0.00..6739.42 rows=3D249 width=3D30= 7) (actual time=3D0.11..2979.18 rows=3D321 loops=3D1) Index Cond: ((category_id =3D 1142) OR (category= _id =3D 1143) OR (category_id =3D 1145) OR (category_id =3D 1259) OR (categ= ory_id =3D 1161)) Filter: ((amount > 0) AND (status =3D 'a'::chara= cter varying) AND (email IS NOT NULL)) -> Subquery Scan "*SELECT* 2" (cost=3D0.00..9288.33 rows= =3D77 width=3D286) (actual time=3D0.65..162.03 rows=3D112 loops=3D1) -> Seq Scan on iid_listing (cost=3D0.00..9288.33 row= s=3D77 width=3D286) (actual time=3D0.63..161.02 rows=3D112 loops=3D1) Filter: (((category_id =3D 1142) OR (category_id= =3D 1143) OR (category_id =3D 1145) OR (category_id =3D 1259) OR (category= _id =3D 1161)) AND (amount > 0) AND (status =3D 'a'::character varying) AND= (email IS NOT NULL)) -> Subquery Scan "*SELECT* 3" (cost=3D10701.96..10710.24 r= ows=3D166 width=3D248) (actual time=3D2824.89..2851.45 rows=3D1860 loops=3D= 1) -> Unique (cost=3D10701.96..10710.24 rows=3D166 widt= h=3D248) (actual time=3D2824.86..2837.43 rows=3D1860 loops=3D1) -> Sort (cost=3D10701.96..10706.10 rows=3D1656= width=3D248) (actual time=3D2824.85..2831.21 rows=3D2790 loops=3D1) Sort Key: branding_master.company_id -> Hash Join (cost=3D339.35..10613.44 ro= ws=3D1656 width=3D248) (actual time=3D33.62..2798.98 rows=3D2790 loops=3D1) Hash Cond: ("outer".company_id =3D "= inner".company_id) -> Seq Scan on branding_master (co= st=3D0.00..7171.80 rows=3D36254 width=3D242) (actual time=3D0.07..2620.57 r= ows=3D38766 loops=3D1) Filter: ((old_company_id =3D 0= ) AND (status =3D 'a'::character varying) AND (email IS NOT NULL)) -> Hash (cost=3D331.00..331.00 row= s=3D3339 width=3D6) (actual time=3D32.92..32.92 rows=3D0 loops=3D1) -> Seq Scan on branding_sp_ca= tegory (cost=3D0.00..331.00 rows=3D3339 width=3D6) (actual time=3D0.06..26= .18 rows=3D3892 loops=3D1) Filter: ((category_id = =3D 1142) OR (category_id =3D 1143) OR (category_id =3D 1145) OR (category_= id =3D 1259) OR (category_id =3D 1161)) -> Hash (cost=3D29.74..29.74 rows=3D774 width=3D15) (actual time=3D43.= 78..43.78 rows=3D0 loops=3D1) -> Seq Scan on approved_profiles a (cost=3D0.00..29.74 rows=3D77= 4 width=3D15) (actual time=3D40.64..42.36 rows=3D778 loops=3D1) Total runtime: 6074.86 msec (26 rows) Time: 7080.76 ms tradein_clients=3D# And Following are the VIEWS & Tables: tradein_clients=3D# \d shippers1 View "shipping_corner.shippers1" +----------+-------------------+-----------+ | Column | Type | Modifiers | +----------+-------------------+-----------+ | co_name | character varying | | | address | character varying | | | city | character varying | | | pin_code | character varying | | | phone | character varying | | | fax | character varying | | | contact | character varying | | | email | character varying | | | size | character varying | | | paid | boolean | | +----------+-------------------+-----------+ View definition: SELECT b.co_name, b.address, b.city, b.pin_code, b.phone, = b.fax, b.contact, b.email, b.size, CASE WHEN (b.size =3D 'BRANDING'::charac= ter varying) THEN false ELSE true END AS paid FROM (ONLY approved_profiles = a JOIN ONLY shipping_candidate_profiles b=20 ON (((a.id =3D b.id) AND (a.source =3D b.source)))); tradein_clients=3D# \d shipping_candidate_profiles View "shipping_corner.shipping_candidate_profiles" +-------------+-------------------+-----------+ | Column | Type | Modifiers | +-------------+-------------------+-----------+ | id | integer | | | branch | character varying | | | source | character varying | | | co_name | character varying | | | address | character varying | | | city | character varying | | | pin_code | character varying | | | phone | character varying | | | fax | character varying | | | contact | character varying | | | email | character varying | | | size | character varying | | | status | boolean | | | category_id | integer | | | keywords | text | | | edition | smallint | | | sno | integer | | +-------------+-------------------+-----------+ View definition: ((((SELECT eyp_listing.list_id AS id, eyp_listing.branch, = 'EYP'::character varying AS source, eyp_listing.co_name, ((((CASE WHEN (eyp= _listing.address1 IS NOT NULL) THEN eyp_listing.address1 WHEN ('' IS NOT NU= LL) THEN ''::character varying ELSE NULL::character varying END || ' '::cha= racter varying) || CASE WHEN (eyp_listing.address2 IS NOT NULL) THEN eyp_li= sting.address2 WHEN ('' IS NOT NULL) THEN ''::character varying ELSE NULL::= character varying END) || ' '::character varying) || CASE WHEN (eyp_listing= .address3 IS NOT NULL) THEN eyp_listing.address3 WHEN ('' IS NOT NULL) THEN= ''::character varying ELSE NULL::character varying END) AS address, eyp_li= sting.city, eyp_listing.pin_code, eyp_listing.phone, eyp_listing.fax, eyp_l= isting.contact, eyp_listing.email, eyp_listing.size, true AS status, eyp_li= sting.category_id, eyp_listing.keywords, eyp_listing.edition, eyp_listing.s= no FROM ONLY public.eyp_listing WHERE ((((((((eyp_listing.category_id =3D 1= 142) OR (eyp_listing.category_id =3D 1143)) OR (eyp_listing.category_id =3D= 1145)) OR (eyp_listing.category_id =3D 1259)) OR (eyp_listing.category_id = =3D 1161)) AND (eyp_listing.amount > 0)) AND (eyp_listing.status =3D 'a'::c= haracter varying)) AND (eyp_listing.email IS NOT NULL))) UNION ALL (SELECT = iid_listing.list_id AS id, iid_listing.branch, 'IID'::character varying AS = source, iid_listing.co_name, ((((CASE WHEN (iid_listing.address1 IS NOT NUL= L) THEN iid_listing.address1 WHEN ('' IS NOT NULL) THEN ''::character varyi= ng ELSE NULL::character varying END || ' '::character varying) || CASE WHEN= (iid_listing.address2 IS NOT NULL) THEN iid_listing.address2 WHEN ('' IS N= OT NULL) THEN ''::character varying ELSE NULL::character varying END) || ' = '::character varying) || CASE WHEN (iid_listing.address3 IS NOT NULL) THEN = iid_listing.address3 WHEN ('' IS NOT NULL) THEN ''::character varying ELSE = NULL::character varying END) AS address, iid_listing.city, iid_listing.pin_= code, iid_listing.phone, iid_listing.fax, iid_listing.contact, iid_listing.= email, iid_listing.size, true AS status, iid_listing.category_id, iid_listi= ng.keywords, iid_listing.edition, iid_listing.sno FROM ONLY public.iid_list= ing WHERE ((((((((iid_listing.category_id =3D 1142) OR (iid_listing.categor= y_id =3D 1143)) OR (iid_listing.category_id =3D 1145)) OR (iid_listing.cate= gory_id =3D 1259)) OR (iid_listing.category_id =3D 1161)) AND (iid_listing.= amount > 0)) AND (iid_listing.status =3D 'a'::character varying)) AND (iid_= listing.email IS NOT NULL))))) UNION ALL (SELECT DISTINCT ON (company_id) c= ompany_id AS id, trade_india_branch AS branch, 'BRANDING'::character varyin= g AS source, comp_name AS co_name, office_addr AS address, city, pincode AS= pin_code, phone_no AS phone, fax_no AS fax, key_exec AS contact, email, 'B= RANDING' AS size, false AS status, category_id, serv_prov AS keywords, bran= ding_master.edition, 0 AS sno FROM (ONLY public.branding_master JOIN ONLY p= ublic.branding_sp_category USING (company_id)) WHERE ((((((((category_id = =3D 1142) OR (category_id =3D 1143)) OR (category_id =3D 1145)) OR (categor= y_id =3D 1259)) OR (category_id =3D 1161)) AND (old_company_id =3D 0)) AND = (status =3D 'a'::character varying)) AND (email IS NOT NULL)) ORDER BY comp= any_id)); tradein_clients=3D# \d approved_profiles Table "shipping_corner.approved_profiles" +--------+-------------------+-----------+ | Column | Type | Modifiers | +--------+-------------------+-----------+ | id | integer | not null | | source | character varying | not null | +--------+-------------------+-----------+ Indexes: approved_profiles_id_key unique btree (id, source) tradein_clients=3D# \d branding_master Table "public.branding_master" Column | Type | = Modifiers --------------------+--------------------------+---------------------------= ----------------------------------------- company_id | integer | not null default nextval('= "branding_master_company_id_seq"'::text) comp_name | character varying(100) | office_addr | character varying(255) | phone_no | character varying(100) | fax_no | character varying(100) | email | character varying(100) | website | character varying(100) | key_exec | character varying(255) | bran | character varying(100) | estd | smallint | staff | integer | prod_exp | text | prod_manu | text | prod_imp | text | serv_prov | text | banker_name | character varying(255) | email_valid | integer | currency_ann | character varying(4) | old_company_id | integer | default 0 currency_exp | character varying(4) | old_formno | integer | currency_imp | character varying(4) | imp_exp_code | character varying(100) | memb_affl | character varying(255) | std_cert | character varying(255) | trade_india_branch | character varying(25) | areacode | integer | default 0 datasource | smallint | status | character varying(1) | default 'p' formno | integer | edition | smallint | area | character varying(50) | sno | integer | city | character varying(100) | pincode | character varying(20) | old_edition | bigint | has_new_form | numeric(15,3) | annual_turn | numeric(15,2) | exp_turn | numeric(15,2) | imp_turn | numeric(15,2) | generated | timestamp with time zone | default "timestamp"('now':= :text) operator_code | character varying(25) | fts_index | integer[] | eyp_group_id | integer | default 0 old_branch | character varying(20) | imported | boolean | default false collector | character varying(255) | for_delete1 | boolean | default false for_delete2 | boolean | default false eyp_exported | timestamp with time zone | last_updated | timestamp with time zone | default now() eyp_paid | integer | iid_exported | timestamp with time zone | iid_paid | integer | delete3 | character varying(100) | comp_name_index | txtidx | Indexes: branding_master_pkey primary key btree (company_id), branding_master_formno_ed_branc unique btree (formno, edition, tra= de_india_branch), branding_master_name_city unique btree (comp_name, city) WHERE (ol= d_company_id =3D 0), branding_master_area btree (area), branding_master_areacode btree (areacode), branding_master_branch btree (trade_india_branch), branding_master_comp_name btree (comp_name), branding_master_comp_name_index gist (comp_name_index), branding_master_edition btree (edition), branding_master_email btree (email) WHERE ((old_company_id =3D 0) = AND (email IS NOT NULL)), branding_master_oldcomapany_id btree (old_company_id) WHERE (old_c= ompany_id > 0), branding_master_pincode btree (pincode), branding_master_status btree (status), branding_master_tibranch btree (trade_india_branch), branding_master_website btree (website) Check constraints: "no_whites_paceallowed_in_imp" ((length(btrim(prod_imp))= > 1) OR (prod_imp IS NULL)) "no_whites_paceallowed_in_email" ((length(btrim((email):= :text)) > 1) OR (email IS NULL)) "no_white_space_allowed_in_serv" ((length(btrim(serv_pro= v)) > 1) OR (serv_prov IS NULL)) "no_white_space_allowed_in_manu" ((length(btrim(prod_man= u)) > 1) OR (prod_manu IS NULL)) "no_white_space_allowed_in_exp" ((length(btrim(prod_exp)= ) > 1) OR (prod_exp IS NULL)) tradein_clients=3D# tradein_clients=3D# \d eyp_listing Table "public.eyp_listing" Column | Type | M= odifiers --------------------+--------------------------+---------------------------= ---------------------------------- userid | integer | category | character varying(50) | category_id | integer | not null branch | character varying | sno | integer | default -1 size | character varying(20) | co_name | character varying(100) | not null address1 | character varying(200) | address2 | character varying(200) | default ' ' address3 | character varying(200) | default ' ' city | character varying(100) | pin_code | character varying(30) | phone | character varying(100) | fax | character varying(100) | email | character varying(100) | contact | character varying(100) | website | character varying(100) | keywords | text | show_ad | character varying(25) | default 'f' status | character varying(200) | default 'a' amount | integer | group_id | integer | default nextval('"eyp_list= ing_group_id_seq"'::text) list_id | integer | not null default nextval('= "eyp_listing_list_id_seq"'::text) catalog_id | integer | default 0 generated | date | default date('now'::text) edition | smallint | wrong_last_updated | date | last_updated | timestamp with time zone | default now() user_keywords | text | counter | smallint | default 0 sent_on | timestamp with time zone | default now() max_emails | smallint | emails_sent | smallint | default 0 total_emails | smallint | default 0 notification | boolean | branding_id | integer | keywordidx | txtidx | company_id | integer | website_working | boolean | default 'f' hide_email | boolean | default 'f' co_name_index | txtidx | bankers | character varying(200) | estd | integer | staff | integer | annual_turn_value | numeric | mobile | character varying(50) | reminder_cnt | smallint | default 0 expires_on | date | Indexes: eyp_listing_pkey primary key btree (list_id), eyp_listing_br_cid_cat_id unique btree (company_id, category_id) W= HERE (size =3D 'BRANDING'::character varying), a_gist_key gist (keywordidx), eyp_listing_amt btree (amount), eyp_listing_branch btree (branch) WHERE (amount > 0), eyp_listing_category_id btree (category_id), eyp_listing_co_name btree (co_name), eyp_listing_co_name_index gist (co_name_index), eyp_listing_company_id btree (company_id), eyp_listing_email btree (email), eyp_listing_group_id btree (group_id), eyp_listing_size btree (size), eyp_listing_sno_branch btree (branch, sno), eyp_listing_userid btree (userid) Check constraints: "branding_check" CASE WHEN (size =3D 'BRANDING'::charact= er varying) THEN ((company_id IS NOT NULL) AND (company_id > 0)) ELSE (comp= any_id IS NULL) END Foreign Key constraints: referrer_branches_master FOREIGN KEY (branch) REFE= RENCES tradeindia_branches(branch) ON UPDATE NO ACTION ON DELETE NO ACTION Triggers: RI_ConstraintTrigger_29292778, RI_ConstraintTrigger_29292779, co_name_index_update, last_updated, set_category, set_max_emails tradein_clients=3D# tradein_clients=3D# \d eyp_listing Table "public.eyp_listing" Column | Type | M= odifiers --------------------+--------------------------+---------------------------= ---------------------------------- userid | integer | category | character varying(50) | category_id | integer | not null branch | character varying | sno | integer | default -1 size | character varying(20) | co_name | character varying(100) | not null address1 | character varying(200) | address2 | character varying(200) | default ' ' address3 | character varying(200) | default ' ' city | character varying(100) | pin_code | character varying(30) | phone | character varying(100) | fax | character varying(100) | email | character varying(100) | contact | character varying(100) | website | character varying(100) | keywords | text | show_ad | character varying(25) | default 'f' status | character varying(200) | default 'a' amount | integer | group_id | integer | default nextval('"eyp_list= ing_group_id_seq"'::text) list_id | integer | not null default nextval('= "eyp_listing_list_id_seq"'::text) catalog_id | integer | default 0 generated | date | default date('now'::text) edition | smallint | wrong_last_updated | date | last_updated | timestamp with time zone | default now() user_keywords | text | counter | smallint | default 0 sent_on | timestamp with time zone | default now() max_emails | smallint | emails_sent | smallint | default 0 total_emails | smallint | default 0 notification | boolean | branding_id | integer | keywordidx | txtidx | company_id | integer | website_working | boolean | default 'f' hide_email | boolean | default 'f' co_name_index | txtidx | bankers | character varying(200) | estd | integer | staff | integer | annual_turn_value | numeric | mobile | character varying(50) | reminder_cnt | smallint | default 0 expires_on | date | Indexes: eyp_listing_pkey primary key btree (list_id), eyp_listing_br_cid_cat_id unique btree (company_id, category_id) W= HERE (size =3D 'BRANDING'::character varying), a_gist_key gist (keywordidx), eyp_listing_amt btree (amount), eyp_listing_branch btree (branch) WHERE (amount > 0), eyp_listing_category_id btree (category_id), eyp_listing_co_name btree (co_name), eyp_listing_co_name_index gist (co_name_index), eyp_listing_company_id btree (company_id), eyp_listing_email btree (email), eyp_listing_group_id btree (group_id), eyp_listing_size btree (size), eyp_listing_sno_branch btree (branch, sno), eyp_listing_userid btree (userid) Check constraints: "branding_check" CASE WHEN (size =3D 'BRANDING'::charact= er varying) THEN ((company_id IS NOT NULL) AND (company_id > 0)) ELSE (comp= any_id IS NULL) END Foreign Key constraints: referrer_branches_master FOREIGN KEY (branch) REFE= RENCES tradeindia_branches(branch) ON UPDATE NO ACTION ON DELETE NO ACTION Triggers: RI_ConstraintTrigger_29292778, RI_ConstraintTrigger_29292779, co_name_index_update, last_updated, set_category, set_max_emails tradein_clients=3D# On Monday 03 February 2003 08:16 pm, Tom Lane wrote: > "Rajesh Kumar Mallah." writes: > > tradein_clients=3D# explain SELECT count(*) from shippers1 where > > city=3D'DELHI'; ERROR: get_names_for_var: bogus varno 5 > > What version is this? ISTR having fixed some bugs that might cause that. > > > i can paste the nasty view definations if nothing is obvious till > > now. > > If it's a current release, we need to see *all* the schema definitions > referenced by the query --- views and tables. > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org --=20 -------------------------------------------- Regds Mallah Rajesh Kumar Mallah, Project Manager (Development) Infocom Network Limited, New Delhi phone: +91(11)26152172 (221) (L) 9811255597 (M) Visit http://www.trade-india.com , India's Leading B2B eMarketplace.