Re: cannot EXPLAIN query...

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) (actual time=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.42 rows=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.02 rows=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=166 width=248) (actual time=2824.86..2837.43 rows=1860 loops=1)
-> Sort (cost=10701.96..10706.10 rows=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.80 rows=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.18 rows=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.74 rows=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_profiles b
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.address2 IS NOT NULL) THEN eyp_listing.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_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 FROM ONLY 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, ((((CASE WHEN (iid_listing.address1 IS NOT NULL) THEN iid_listing.address1 WHEN ('' IS NOT NULL) THEN ''::character varying ELSE NULL::character varying END || ' '::character varying) || CASE WHEN (iid_listing.address2 IS NOT NULL) THEN iid_listing.address2 WHEN ('' IS NOT 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_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 (ONLY public.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 | 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, 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_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 = 0) AND (email IS NOT NULL)),
branding_master_oldcomapany_id btree (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 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_listing_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) 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_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 = '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 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=#

tradein_clients=# \d eyp_listing
Table "public.eyp_listing"
Column | Type | Modifiers
--------------------+--------------------------+-------------------------------------------------------------
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_listing_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) 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_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 = '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 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=#

On Monday 03 February 2003 08:16 pm, Tom Lane wrote:
> "Rajesh Kumar Mallah." <mallah(at)trade-india(dot)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(at)postgresql(dot)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.