On Sat, 7 Oct 2006, Sergey E. Koposov wrote:
cas=# explain UPDATE table_list SET description = 'tag{image
SRC="/vizier/new2.gif"}3rd release of DENIS (2005Sep)' WHERE id =
cas_get_table_id ('cas_data_sega','b_denis_denis5' );
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=0.01..17.11 rows=2 width=82)
-> Index Scan using table_user_list_pkey on table_user_list
(cost=0.00..8.02 rows=1 width=10)
Index Cond: (cas_get_table_id('cas_data_sega'::character varying,
'b_denis_denis5'::character varying) = id)
-> Append (cost=0.00..9.07 rows=2 width=76)
-> Index Scan using table_user_list_pkey on table_user_list
(cost=0.00..8.02 rows=1 width=76)
Index Cond: (id = cas_get_table_id('cas_data_sega'::character
varying, 'b_denis_denis5'::character varying))
-> Seq Scan on table_list (cost=0.00..1.04 rows=1 width=51)
Filter: (id = cas_get_table_id('cas_data_sega'::character
varying, 'b_denis_denis5'::character varying))
(8 rows)
As I see from it, it generates two seq. scans for one table (table_user_list)
I meant index scans.
By the way, I sent again the full info about the used tables .
cas=# \d cas_metadata_sega.table_user_list
Table "cas_metadata_sega.table_user_list"
Column | Type | Modifiers
-------------+-------------------+---------------------------------------------------------
id | integer | not null default
nextval('table_list_id_seq'::regclass)
catalog_id | bigint |
name | character varying | not null
info | character varying |
description | character varying |
Indexes:
"table_user_list_pkey" PRIMARY KEY, btree (id)
"table_user_list_catalog_id_key" UNIQUE, btree (catalog_id, name)
Foreign-key constraints:
"table_user_list_catalog_id_fkey" FOREIGN KEY (catalog_id) REFERENCES
catalog_user_list(id) ON UPDATE CASCADE ON DELETE CASCADE
cas=# \d cas_metadata_sega.table_list
View "cas_metadata_sega.table_list"
Column | Type | Modifiers
-------------+-------------------+-----------
id | integer |
catalog_id | bigint |
name | character varying |
info | character varying |
description | character varying |
View definition:
SELECT table_user_list.id, table_user_list.catalog_id, table_user_list.name,
table_user_list.info, table_user_list.description
FROM table_user_list
UNION ALL
SELECT table_list.id, table_list.catalog_id, table_list.name, table_list.info,
table_list.description
FROM cas_metadata.table_list;
Rules:
rule_delete_table AS
ON DELETE TO table_list DO INSTEAD DELETE FROM table_user_list
rule_insert_table AS
ON INSERT TO table_list DO INSTEAD INSERT INTO table_user_list
(catalog_id, name, info, description) SELECT new.catalog_id, new.name,
new.info, new.description
rule_update_table AS
ON UPDATE TO table_list DO INSTEAD UPDATE table_user_list SET catalog_id =
new.catalog_id, name = new.name, info = new.info, description = new.description
WHERE table_user_list.id = new.id
cas=# \d cas_metadata.table_list
Table "cas_metadata.table_list"
Column | Type | Modifiers
-------------+-------------------+---------------------------------------------------------
id | integer | not null default
nextval('table_list_id_seq'::regclass)
catalog_id | bigint |
name | character varying | not null
info | character varying |
description | character varying |
Indexes:
"table_list_pkey" PRIMARY KEY, btree (id)
"table_list_catalog_id_key" UNIQUE, btree (catalog_id, name)
"table_list_catalog_id_idx" btree (catalog_id)
"table_list_name_idx" btree (name)
Foreign-key constraints:
"table_list_catalog_id_fkey" FOREIGN KEY (catalog_id) REFERENCES
cas_metadata.catalog_list(id) ON UPDATE CASCADE ON DELETE CASCADE
Regards,
Sergey
*******************************************************************
Sergey E. Koposov
Max Planck Institute for Astronomy/Sternberg Astronomical Institute
Tel: +49-6221-528-349
Web: http://lnfm1.sai.msu.ru/~math
E-mail: [EMAIL PROTECTED]
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
