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=# explain analyze SELECT * from shippers1 ;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------Hash
Join (cost=31.67..26780.73 rows=2 width=339) (actual time=45.18..6072.38 rows=732 loops=1) Hash Cond: ("outer".id =
"inner".id) Join Filter: ("inner".source = "outer".source) -> Subquery Scan b (cost=0.00..26737.99 rows=492
width=307)(actual time=0.14..6015.04 rows=2293 loops=1) -> Append (cost=0.00..26737.99 rows=492 width=307)
(actualtime=0.13..6001.13 rows=2293 loops=1) -> Subquery Scan "*SELECT* 1" (cost=0.00..6739.42 rows=249
width=307)(actual time=0.12..2982.18 rows=321 loops=1) -> Index Scan using eyp_listing_category_id,
eyp_listing_category_id,eyp_listing_category_id, eyp_listing_category_id, eyp_listing_category_id on eyp_listing
(cost=0.00..6739.42rows=249 width=307) (actual time=0.11..2979.18 rows=321 loops=1) Index
Cond:((category_id = 1142) OR (category_id = 1143) OR (category_id = 1145) OR (category_id = 1259) OR (category_id =
1161)) Filter: ((amount > 0) AND (status = 'a'::character varying) AND (email IS NOT NULL))
-> Subquery Scan "*SELECT* 2" (cost=0.00..9288.33 rows=77 width=286) (actual time=0.65..162.03 rows=112
loops=1) -> Seq Scan on iid_listing (cost=0.00..9288.33 rows=77 width=286) (actual
time=0.63..161.02rows=112 loops=1) Filter: (((category_id = 1142) OR (category_id = 1143) OR
(category_id= 1145) OR (category_id = 1259) OR (category_id = 1161)) AND (amount > 0) AND (status = 'a'::character
varying)AND (email IS NOT NULL)) -> Subquery Scan "*SELECT* 3" (cost=10701.96..10710.24 rows=166
width=248)(actual time=2824.89..2851.45 rows=1860 loops=1) -> Unique (cost=10701.96..10710.24
rows=166width=248) (actual time=2824.86..2837.43 rows=1860 loops=1) -> Sort
(cost=10701.96..10706.10rows=1656 width=248) (actual time=2824.85..2831.21 rows=2790 loops=1)
Sort Key: branding_master.company_id -> Hash Join (cost=339.35..10613.44 rows=1656
width=248)(actual time=33.62..2798.98 rows=2790 loops=1) Hash Cond:
("outer".company_id= "inner".company_id) -> Seq Scan on branding_master
(cost=0.00..7171.80rows=36254 width=242) (actual time=0.07..2620.57 rows=38766 loops=1)
Filter: ((old_company_id = 0) AND (status = 'a'::character varying) AND (email IS NOT NULL))
-> Hash (cost=331.00..331.00 rows=3339 width=6) (actual time=32.92..32.92 rows=0 loops=1)
-> Seq Scan on branding_sp_category (cost=0.00..331.00 rows=3339 width=6) (actual
time=0.06..26.18rows=3892 loops=1) Filter: ((category_id = 1142) OR
(category_id= 1143) OR (category_id = 1145) OR (category_id = 1259) OR (category_id = 1161)) -> Hash
(cost=29.74..29.74rows=774 width=15) (actual time=43.78..43.78 rows=0 loops=1) -> Seq Scan on approved_profiles
a (cost=0.00..29.74 rows=774 width=15) (actual time=40.64..42.36 rows=778 loops=1)Total runtime: 6074.86 msec
(26 rows)
Time: 7080.76 ms
tradein_clients=#
And Following are the VIEWS & Tables:
tradein_clients=# \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= 'BRANDING'::character varying) THEN false ELSE true END AS paid FROM (ONLY approved_profiles a JOIN ONLY
shipping_candidate_profilesb
ON (((a.id = b.id) AND (a.source = b.source))));
tradein_clients=# \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 NULL)
THEN''::character varying ELSE NULL::character varying END || ' '::character varying) || CASE WHEN
(eyp_listing.address2IS NOT NULL) THEN eyp_listing.address2 WHEN ('' IS NOT NULL) THEN ''::character varying ELSE
NULL::charactervarying END) || ' '::character varying) || CASE WHEN (eyp_listing.address3 IS NOT NULL) THEN
eyp_listing.address3WHEN ('' IS NOT NULL) THEN ''::character varying ELSE NULL::character varying END) AS address,
eyp_listing.city,eyp_listing.pin_code, eyp_listing.phone, eyp_listing.fax, eyp_listing.contact, eyp_listing.email,
eyp_listing.size,true AS status, eyp_listing.category_id, eyp_listing.keywords, eyp_listing.edition, eyp_listing.sno
FROMONLY public.eyp_listing WHERE ((((((((eyp_listing.category_id = 1142) OR (eyp_listing.category_id = 1143)) OR
(eyp_listing.category_id= 1145)) OR (eyp_listing.category_id = 1259)) OR (eyp_listing.category_id = 1161)) AND
(eyp_listing.amount> 0)) AND (eyp_listing.status = 'a'::character 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,
((((CASEWHEN (iid_listing.address1 IS NOT NULL) THEN iid_listing.address1 WHEN ('' IS NOT NULL) THEN ''::character
varyingELSE NULL::character varying END || ' '::character varying) || CASE WHEN (iid_listing.address2 IS NOT NULL) THEN
iid_listing.address2WHEN ('' IS NOT NULL) THEN ''::character varying ELSE NULL::character varying END) || '
'::charactervarying) || 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_listing.keywords, iid_listing.edition, iid_listing.sno FROM ONLY public.iid_listing WHERE
((((((((iid_listing.category_id= 1142) OR (iid_listing.category_id = 1143)) OR (iid_listing.category_id = 1145)) OR
(iid_listing.category_id= 1259)) OR (iid_listing.category_id = 1161)) AND (iid_listing.amount > 0)) AND
(iid_listing.status= 'a'::character varying)) AND (iid_listing.email IS NOT NULL))))) UNION ALL (SELECT DISTINCT ON
(company_id)company_id AS id, trade_india_branch AS branch, 'BRANDING'::character varying 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,'BRANDING' AS size, false AS status, category_id, serv_prov AS keywords, branding_master.edition, 0 AS sno FROM
(ONLYpublic.branding_master JOIN ONLY public.branding_sp_category USING (company_id)) WHERE ((((((((category_id = 1142)
OR(category_id = 1143)) OR (category_id = 1145)) OR (category_id = 1259)) OR (category_id = 1161)) AND (old_company_id
=0)) AND (status = 'a'::character varying)) AND (email IS NOT NULL)) ORDER BY company_id));
tradein_clients=# \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=# \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 |
charactervarying(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 |
default0currency_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 0datasource | smallint |status | character
varying(1) | default 'p'formno | integer |edition | smallint
|area | character varying(50) |sno | integer |city |
charactervarying(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 0old_branch | character varying(20) |imported
| boolean | default falsecollector | character varying(255) |for_delete1 |
boolean | default falsefor_delete2 | boolean | default falseeyp_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, trade_india_branch), branding_master_name_city unique btree (comp_name, city) WHERE
(old_company_id= 0), branding_master_area btree (area), branding_master_areacode btree (areacode),
branding_master_branchbtree (trade_india_branch), branding_master_comp_name btree (comp_name),
branding_master_comp_name_indexgist (comp_name_index), branding_master_edition btree (edition),
branding_master_emailbtree (email) WHERE ((old_company_id = 0) AND (email IS NOT NULL)),
branding_master_oldcomapany_idbtree (old_company_id) WHERE (old_company_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_prov)) > 1) OR (serv_prov IS NULL))
"no_white_space_allowed_in_manu"((length(btrim(prod_manu)) > 1) OR (prod_manu IS NULL))
"no_white_space_allowed_in_exp"((length(btrim(prod_exp)) > 1) OR (prod_exp IS NULL))
tradein_clients=#
tradein_clients=# \d eyp_listing Table "public.eyp_listing" Column |
Type | Modifiers
--------------------+--------------------------+-------------------------------------------------------------userid
| integer |category | character varying(50) |category_id | integer
| not nullbranch | character varying |sno | integer | default
-1size | character varying(20) |co_name | character varying(100) | not nulladdress1
| 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 |
charactervarying(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_listing_group_id_seq"'::text)list_id | integer |
notnull default nextval('"eyp_listing_list_id_seq"'::text)catalog_id | integer | default
0generated | 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 0sent_on
| timestamp with time zone | default now()max_emails | smallint |emails_sent |
smallint | default 0total_emails | smallint | default 0notification |
boolean |branding_id | integer |keywordidx | txtidx
|company_id | integer |website_working | boolean | default 'f'hide_email
| boolean | default 'f'co_name_index | txtidx |bankers |
charactervarying(200) |estd | integer |staff | integer
|annual_turn_value | numeric |mobile | character varying(50) |reminder_cnt |
smallint | default 0expires_on | date |
Indexes: eyp_listing_pkey primary key btree (list_id), eyp_listing_br_cid_cat_id unique btree (company_id,
category_id)WHERE (size = '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_idbtree (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_useridbtree (userid)
Check constraints: "branding_check" CASE WHEN (size = 'BRANDING'::character varying) THEN ((company_id IS NOT NULL) AND
(company_id> 0)) ELSE (company_id IS NULL) END
Foreign Key constraints: referrer_branches_master FOREIGN KEY (branch) REFERENCES tradeindia_branches(branch) ON UPDATE
NOACTION ON DELETE NO ACTION
Triggers: RI_ConstraintTrigger_29292778, RI_ConstraintTrigger_29292779, co_name_index_update,
last_updated, set_category, set_max_emails
tradein_clients=#
tradein_clients=# \d eyp_listing Table "public.eyp_listing" Column |
Type | Modifiers
--------------------+--------------------------+-------------------------------------------------------------userid
| integer |category | character varying(50) |category_id | integer
| not nullbranch | character varying |sno | integer | default
-1size | character varying(20) |co_name | character varying(100) | not nulladdress1
| 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 |
charactervarying(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_listing_group_id_seq"'::text)list_id | integer |
notnull default nextval('"eyp_listing_list_id_seq"'::text)catalog_id | integer | default
0generated | 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 0sent_on
| timestamp with time zone | default now()max_emails | smallint |emails_sent |
smallint | default 0total_emails | smallint | default 0notification |
boolean |branding_id | integer |keywordidx | txtidx
|company_id | integer |website_working | boolean | default 'f'hide_email
| boolean | default 'f'co_name_index | txtidx |bankers |
charactervarying(200) |estd | integer |staff | integer
|annual_turn_value | numeric |mobile | character varying(50) |reminder_cnt |
smallint | default 0expires_on | date |
Indexes: eyp_listing_pkey primary key btree (list_id), eyp_listing_br_cid_cat_id unique btree (company_id,
category_id)WHERE (size = '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_idbtree (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_useridbtree (userid)
Check constraints: "branding_check" CASE WHEN (size = 'BRANDING'::character varying) THEN ((company_id IS NOT NULL) AND
(company_id> 0)) ELSE (company_id IS NULL) END
Foreign Key constraints: referrer_branches_master FOREIGN KEY (branch) REFERENCES tradeindia_branches(branch) ON UPDATE
NOACTION ON DELETE NO ACTION
Triggers: RI_ConstraintTrigger_29292778, RI_ConstraintTrigger_29292779, co_name_index_update,
last_updated, set_category, set_max_emails
tradein_clients=#
On Monday 03 February 2003 08:16 pm, Tom Lane wrote:
> "Rajesh Kumar Mallah." <mallah@trade-india.com> writes:
> > tradein_clients=# explain SELECT count(*) from shippers1 where
> > city='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
--
-------------------------------------------- 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.
