[SQL] Find out whether a view's column is indexed?
Is there any way to find out whether a column that's used in a view is indexed? The following query: SELECT ic.relname AS index_name FROM pg_class bc, pg_class ic, pg_index i, pg_attribute a, pg_opclass oc, pg_namespace n WHERE i.indrelid = bc.oid AND i.indexrelid = ic.oid AND i.indkey[0] = a.attnum AND i.indclass[0] = oc.oid AND a.attrelid = bc.oid AND oc.opcname = 'gist_geometry_ops' AND n.oid = bc.relnamespace AND bc.relkind ~ '[rv]' AND ic.relkind = 'i' AND n.nspname = 'foo' AND bc.relname = 'bar' AND a.attname = 'foobar'; lets me find out whether a table column is indexed, but it doesn't work for views. Is there anything that can be done for views? At least for simple views of the kind 'CREATE VIEW v AS SELECT a,b,c FROM t'? Can anybody help? Martin PS: as you can see from the query I'm using the PostGIS extension, and I'm only interested in spatial indices on geometry columns. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Find out whether a view's column is indexed?
Martin Schäfer wrote: Is there any way to find out whether a column that's used in a view is indexed? The following query: SELECT ic.relname AS index_name [snip] lets me find out whether a table column is indexed, but it doesn't work for views. Is there anything that can be done for views? At least for simple views of the kind 'CREATE VIEW v AS SELECT a,b,c FROM t'? If you're running 7.4 you can look in the information schema, in view_column_usage - that will tell you which table-columns a view uses. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] sub-select parameter problem
Hello, Imagine the following query: --- SELECT tableA.field1, tableA.field2, tableB.field1, tableB.field2, ( SELECT tableC.field2 FROM tableC WHERE tableC.field1 = tableB.field1 - 1; ) AS p FROM tableA INNER JOIN tableB ON tableA.pk = tableB.FK; --- It works fine. Now, I need to do something else: the parameter of my sub-select is also a member of the table I'm selecting. --- SELECT tableA.field1, tableA.field2, tableB.field1, tableB.field2, ( SELECT tableB.field2 FROM tableB WHERE tableB.field1 = tableB.field1 (--> from-main-select?) - 1; ) AS p FROM tableA INNER JOIN tableB ON tableA.pk = tableB.FK; --- How can I refer to the tableB.field1 parameter from the main query? I've tried to do something like this, but without success: --- SELECT tableA.field1, tableA.field2, tableB.field1 AS param, tableB.field2, ( SELECT tableB.field2 FROM tableB WHERE tableB.field1 = param - 1;(--> does not work...) ) AS p FROM tableA INNER JOIN tableB ON tableA.pk = tableB.FK; --- The only workaround I found is to use CASE... WHEN, but this is not really robust, nor elegant. --- SELECT tableA.field1, tableA.field2, tableB.field1, tableB.field2, CASE WHEN tableB.field1 = 1 THEN ( SELECT tableB.field2 FROM tableB WHERE tableB.field1 = 0; ) WHEN tableB.field1 = 2 THEN ( SELECT tableB.field2 FROM tableB WHERE tableB.field1 = 1; ) WHEN tableB.field1 = 3 THEN ( SELECT tableB.field2 FROM tableB WHERE tableB.field1 = 2; ) ... etc... ELSE 0 END AS p, FROM tableA INNER JOIN tableB ON tableA.pk = tableB.FK; --- In my particular application, this is almost acceptable, but I'm sure there is a better way to do that... Thanks for your help! (And for reading, by the way!) --- Philippe Lang Attik System ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Schema + User-Defined Data Type Indexing problems...
--- Tom Lane <[EMAIL PROTECTED]> wrote: > Chris Gamache <[EMAIL PROTECTED]> writes: > > I'm having a heck of a time, and it seems like in my thrashing about > > to find a solution to this problem I have ruined the uniqueidentifier > > datatype in the schema... > > > CREATE INDEX mt_uuid_idx > > ON my_schema.my_table USING btree (my_uuid); > > > ERROR: data type my_schema.uniqueidentifier has no default operator class > for > > access method "btree" > > HINT: You must specify an operator class for the index or define a default > > operator class for the data type. > > > I can look at the operator classes and see that there is an operator class > for > > btree for my_schema.uniqueidentifier. > > IIRC, the opclass has to be in a schema that is in your schema search > path to be found by CREATE INDEX by default. If it isn't, you could > specify it explicitly: > > CREATE INDEX mt_uuid_idx > ON my_schema.my_table USING btree (my_uuid USING my_schema.uuidopclass); > > It's possible that we could think of a more convenient behavior for > default opclasses, but I don't want to do something that would foreclose > having similarly-named datatypes in different schemas. You have any > suggestions? That /is/ important to be able to have similarly named datatypes in different schemas. I'll give the explicit opclass a go. Indeed, if I place the schema in my search path the index creation and index scans seem to work perfectly. I had wanted to have to specify the schema whenever I referenced objects in it instead of putting it in my search path. I had no concept of exactly how truly separated schemas are. The only idea that I can think of (and, again, I may be underestimating the level of separation that needs to exist between schema) is that object creation could implicitly looks to the current schema for a usable index/opclass/whatever first before checking the search path. A SELECT could look first to the schema of the table before checking the search path for a usable index. Is it even possible to create an index that lives in a different schema from the table it is indexing? CG __ Do you Yahoo!? Friends. Fun. Try the all-new Yahoo! Messenger. http://messenger.yahoo.com/ ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] Converting integer to binary
I have searched around but I cannot see any standard way in PostgreSQL to convert from an integer into a binary representation. i.e. I want to do: 16 ==> 1 32 ==> 10 64 ==> 100 96 ==> 110 etc.. Now I have an algorithm to do it so I could write an SQL function, I guess. If there's a standard way to do it though that would be quite nice. Thanks in advance, Stephen Quinney ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Find out whether a view's column is indexed?
I think the information_schema.view_column_usage doesn't tell me which view column is based on which table column, it only says generally which set of table/view columns are used for the view as a whole. I need a bit more detailed information. If I have two views defined as this: CREATE VIEW v1 AS SELECT a,b,c FROM t; CREATE VIEW v2 AS SELECT b AS a,a AS b,c FROM t; then their entries in view_column_usage is identical, but it is entirely possible that e.g. v1.a is indexed, but v2.a is not indexed. I can do EXPLAIN SELECT * FROM v2 WHERE a = 'foo'; and I can see whether a sequential scan or an index scan is performed, but parsing the output of EXPLAIN programmatically is nearly impossible. Anyway the words 'Index Scan' and 'Seq Scan' can change without notice, maybe even from one locale to another. Martin > -Original Message- > From: Richard Huxton [mailto:[EMAIL PROTECTED] > > Martin Schäfer wrote: > > Is there any way to find out whether a column that's used > in a view is indexed? > > > > The following query: > > > > SELECT ic.relname AS index_name > [snip] > > lets me find out whether a table column is indexed, but it > doesn't work for views. Is there anything that can be done > for views? At least for simple views of the kind 'CREATE VIEW > v AS SELECT a,b,c FROM t'? > > If you're running 7.4 you can look in the information schema, in > view_column_usage - that will tell you which table-columns a > view uses. > > > -- >Richard Huxton >Archonet Ltd > ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] Schema + User-Defined Data Type Indexing problems...
Chris Gamache <[EMAIL PROTECTED]> writes: > Is it even possible to create an index that lives in a different > schema from the table it is indexing? It is not --- the index always lives in the same schema as its table. However, I think that the real issue here is "where is the datatype?". I'm assuming that you created both the datatype uuid and the opclass for it in my_schema. So, when working in another schema (with my_schema not in the search path at all) you'd have had to say create table foo (my_uuid my_schema.uuid); and if you then try to make an index you'll have to say create index fooi on foo (my_uuid my_schema.uuid_ops); because no default opclass for uuid will be found in the search path. In practice I'm not sure that this is really a situation that we need to fret about, because using a datatype that isn't in your search path has got notational problems that are orders of magnitude worse than this one. The functions and operators that do something useful with the datatype would also have to be schema-qualified every time you use them. This is perhaps tolerable for functions but it's quite unpleasant for operators :-( You can't write select * from foo where my_uuid = 'xxx'; instead select * from foo where my_uuid operator(my_schema.=) 'xxx'; Yech. I think you'll end up putting uuid's schema in your search path before long anyway. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] Find out whether a view's column is indexed?
> and I can see whether a sequential scan or an index scan is > performed, but parsing the output of EXPLAIN programmatically > is nearly impossible. Anyway the words 'Index Scan' and 'Seq > Scan' can change without notice, maybe even from one locale to > another. I think you are operating under the faulty assumption that 'Index Scan' in EXPLAIN output signifies that a column is *indexed*. What it really tells you is whether an index is actually *used* when getting data from a column. That of course requires an index to be there. However, an index being there doesn't guarantee it being used. Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Schema + User-Defined Data Type Indexing problems...
--- Tom Lane <[EMAIL PROTECTED]> wrote: > In practice I'm not sure that this is really a situation that we need to > fret about, because using a datatype that isn't in your search path has > got notational problems that are orders of magnitude worse than this > one. The functions and operators that do something useful with the > datatype would also have to be schema-qualified every time you use them. > This is perhaps tolerable for functions but it's quite unpleasant for > operators :-( You can't write > select * from foo where my_uuid = 'xxx'; > instead > select * from foo where my_uuid operator(my_schema.=) 'xxx'; > Yech. I think you'll end up putting uuid's schema in your search path > before long anyway. Right you are. I guess the moral of the story is that when using custom datatypes, search_path is a required setting. I guess that is why the "public" schema should be just that, completely accessable by any user with rights to the DB. So, is the best-practice for the my_schema tables to reference the user-defined datatype in the "public" schema? CREATE TABLE my_schema.foo (uuid public.uniqueidentifier); __ Do you Yahoo!? Friends. Fun. Try the all-new Yahoo! Messenger. http://messenger.yahoo.com/ ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[SQL] Function returns error
Hi All,
I am a relatively new user to postgres. I have created a function that compiles but
generates an error when executed. I know I am overlooking something simple. The
function and error are below.
CREATE OR REPLACE FUNCTION building_insert(varchar, int4, varchar)
RETURNS int4 AS
'
/* Return code dictionary:
0 - Success
1 - Valid User, Insert Failed
2 - Invalid User, Abort
*/
DECLARE
p_user ALIAS FOR $1;
p_parcel_id ALIAS FOR $2;
p_name ALIAS FOR $3;
BEGIN
IF p_user == \'mlong\' THEN
RETURN 2;
END IF;
INSERT INTO
building(
parcel_id,
name,
createdate
)
VALUES(
p_parcel_id,
p_name,
now()
);
RETURN 0;
END;
' LANGUAGE 'plpgsql' VOLATILE;
/* Query that generates error */
select building_insert('mlong', 20,'building 1');
ERROR: operator does not exist: character varying == "unknown"
HINT: No operator matches the given name and argument type(s). You may need to add
explicit type casts.
CONTEXT: PL/pgSQL function "building_insert" line 14 at if
Thanks,
Mike
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Schema + User-Defined Data Type Indexing problems...
Chris Gamache <[EMAIL PROTECTED]> writes: > So, is the best-practice for the my_schema tables to reference the > user-defined datatype in the "public" schema? Not necessarily, but if you put it somewhere else you'll want to add the somewhere else to your default search path (probably via ALTER DATABASE). regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Function returns error
Michael Long wrote: ERROR: operator does not exist: character varying == "unknown" HINT: No operator matches the given name and argument type(s). You may need to add explicit type casts. CONTEXT: PL/pgSQL function "building_insert" line 14 at if Common mistake, still make it myself on occasion. The '==' operator doesn't exist in plpgsql, you should use '=' when comparing and ':=' for assignment. HTH -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] query optimization
I see that attribute project is defined as integer in library, and as varchar(8) in clone. I suspect that's what causing the problem and forcing a seq scan on library.
On Thu, 2004-03-04 at 14:56, Charles Hauser wrote:
All,
I have the following query which is running quite slow on our server and
was hoping someone would have suggestions how I might improve it.
est3=>EXPLAIN SELECT clone.uniquename,clone.name,library.type,clone.clone_id
est3-> FROM library,clone_aceg
est3-> JOIN clone USING (clone_id)
est3-> WHERE clone_aceg.aceg_id = 8 AND
est3-> clone.project=library.project;
QUERY PLAN
-
Nested Loop (cost=0.00..27.92 rows=1 width=57)
Join Filter: (("outer".project)::text = ("inner".project)::text)
-> Nested Loop (cost=0.00..18.55 rows=4 width=43)
-> Index Scan using aceg_id_clone_aceg_key on clone_aceg (cost=0.00..3.05 rows=4 width=4)
Index Cond: (aceg_id = 8)
-> Index Scan using clone_pkey on clone (cost=0.00..3.91 rows=1 width=39)
Index Cond: ("outer".clone_id = clone.clone_id)
-> Seq Scan on library (cost=0.00..2.15 rows=15 width=14)
(8 rows)
relevant tables below.
regards,
Charles
Tables:
Table "public.clone"
Column | Type | Modifiers
+---+
clone_id | integer | not null default nextval('"clone_clone_id_seq"'::text)
name | character varying(10) | not null
uniquename | text | not null
project| character varying(8) |
p_end | character varying(2) |
lib_id | integer |
accn | character varying(10) |
seq| text | not null
seqlen | integer |
hq_start | integer |
hq_end | integer |
scaffold | character varying(50) |
Indexes: clone_pkey primary key btree (clone_id),
clone_uniquename_idx unique btree (uniquename),
clone_accn_idx btree (accn),
clone_name_idx btree (name),
clone_project_idx btree (project),
clone_scaf_idx btree (scaffold)
Table "public.library"
Column| Type | Modifiers
-+-+
lib_id | integer | not null default nextval('"library_lib_id_seq"'::text)
source | text|
type| text|
project | integer |
name| text|
organism| text|
strain | text|
vector | text|
rs1 | text|
rs2 | text|
preparation | text|
Indexes: library_pkey primary key btree (lib_id),
library_project_idx btree (project),
library_type_idx btree ("type")
Table "public.clone_aceg"
Column | Type | Modifiers
--+-+---
clone_id | integer |
aceg_id | integer |
Indexes: clone_aceg_clone_id_key unique btree (clone_id, aceg_id),
aceg_id_clone_aceg_key btree (aceg_id),
clone_id_clone_aceg_key btree (clone_id)
Foreign Key constraints: cloneid FOREIGN KEY (clone_id) REFERENCES clone(clone_id) ON UPDATE NO ACTION ON DELETE CASCADE,
acegid FOREIGN KEY (aceg_id) REFERENCES aceg(aceg_id) ON UPDATE NO ACTION ON DELETE CASCADE
List of relations
Schema |Name| Type | Owner |Table
++---+-+--
public | aceg_aceg_idx | index | chauser | aceg
public | aceg_assembly_key | index | chauser | aceg
public | aceg_blast_aceg_id_key | index | chauser | aceg_blast
public | aceg_contig_idx| index | chauser | aceg
public | aceg_g_scaffold_idx| index | chauser | aceg
public | aceg_has_blast_idx | index | chauser | aceg
public | aceg_id_aceg_blast_key | index | chauser | aceg_blast
public | aceg_id_clone_aceg_key | index | chauser | clone_aceg
public | aceg_pkey | index | chauser | aceg
public | aceg_uniquename_idx| index | chauser | aceg
public | blast_id_aceg_blast_key| index | chauser | aceg_blast
public | blast_id_contig_blast_key | index | chauser | contig_blast
public | blast_ortho_idx| index | chauser | blast
public | blast_pkey | index | chauser | blast
public | clone_accn_idx | index | chauser | clone
public | clone_aceg_clone_id_key| index | chauser | clone_aceg
public | clone_contig_clone_id_key | index | chauser | clone_contig
public | clone_id_clone_aceg_key| index | chauser | clone_aceg
public | clone_id_clone_contig_key | index | chauser |
Re: [SQL] Converting integer to binary
On Thu, Jun 10, 2004 at 14:52:41 +0100, Stephen Quinney <[EMAIL PROTECTED]> wrote: > > I have searched around but I cannot see any standard way in PostgreSQL > to convert from an integer into a binary representation. > > Now I have an algorithm to do it so I could write an SQL function, I > guess. If there's a standard way to do it though that would be quite nice. There doesn't seem to currently be a function that does this. to_char would be the logical place since that is what is used to convert various numeric types to strings with a decimal representation. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Converting integer to binary
Once upon a time in PostgreSQL there was a function : bitfromint4 ... Any idea where it has disappeared to? You can do # select B'10101101'::int4; int4 -- 173 (1 row) but you want to go # select 173::varbit; which is what bitfromint4 used to do. CG --- Bruno Wolff III <[EMAIL PROTECTED]> wrote: > On Thu, Jun 10, 2004 at 14:52:41 +0100, > Stephen Quinney <[EMAIL PROTECTED]> wrote: > > > > I have searched around but I cannot see any standard way in PostgreSQL > > to convert from an integer into a binary representation. > > > > Now I have an algorithm to do it so I could write an SQL function, I > > guess. If there's a standard way to do it though that would be quite nice. > > There doesn't seem to currently be a function that does this. to_char > would be the logical place since that is what is used to convert various > numeric types to strings with a decimal representation. > > ---(end of broadcast)--- > TIP 4: Don't 'kill -9' the postmaster __ Do you Yahoo!? Friends. Fun. Try the all-new Yahoo! Messenger. http://messenger.yahoo.com/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] Converting integer to binary
On Thu, Jun 10, 2004 at 13:24:15 -0700, Chris Gamache <[EMAIL PROTECTED]> wrote: Following up on the cast to bit idea, he could do something like casting to bit(32). I don't think there is an easy way to get this cast to string, so it may not completely solve his problem, depending on what he was going to do with the binary representation. On teh other hand, for some things a bit field might be more useful than a string. area=> select 6::bit(32); bit -- 0110 (1 row) ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[SQL] (No Subject)
is it possible to dump within procedural language/SQL syntax? Using pg_dump from console is very confusing for some end user who don't have Linux skills. so I decide to create a function to do that, and they may call it from my application. Thanks William Need a new email address that people can remember Check out the new EudoraMail at http://www.eudoramail.com ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Query becoming slower on adding a primary key [ SOLVED
HI, The problem was solved by reducing the effective_cache_size from 102400 to 10240 my total RAM is 4GB. Regds mallah. Tom Lane wrote: [EMAIL PROTECTED] writes: tradein_clients=# explain analyze select email_id ,email ,contact from t_a a join email_source f using(email_id) join email_subscriptions h using(email_id) where 1=1 and f.source_id =1 and h.sub_id = 3 ; Runs for Ever. So what does plain explain say about it? regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
