Re: cannot EXPLAIN query... - Mailing list pgsql-sql

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.