[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?
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
[SQL] How to find out programmatically whether a query on a view will use an index?
Hi, I'm using the PostGIS spatial extension. Some of my spatial queries (like live zooming and panning) should only be performed when the column containing the spatial data is spatially indexed, otherwise the first query takes almost forever and users will just kill the application out of frustration. I can easily find out whether a spatial column in a table is spatially indexed, using pg_class, pg_index, and pg_opclass. But this doesn't work for views. I can't find out whether a column in a view, is just an 'exact copy' of a column in a table (indicating that there's a chance that an index on the table column could be used for a query on the view column), or whether the column in the view defines some kind of operation, rendering any possible index on the table column useless. >From information_schema.view_column_usage I can see which columns of which tables are referenced by the view, but it doesn't tell me whether an index on a column in the table would be used for a query on the view. Using 'EXPLAIN SELECT ...' does tell me whether the spatial index is used, but the output is not machine readable. I guess the output is localized to the current locale, so trying to parse the output doesn't seem to be a good idea. Is there no way to get a machine readable query plan? Any help appreciated, Martin ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] How to find out programmatically whether a query on a view will use an index?
> > Is there no way to get a machine readable query plan? > > No, and no such API is likely to be defined in the future either, > because we reserve the right to change plan structures at any time. How about a list of the indices used in a query? That alone would already be very useful. Martin ---(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] How to find out programmatically whether a query on a view will use an index?
> > I'm using the PostGIS spatial extension. Some of my spatial > queries (like > > live zooming and panning) should only be performed when the column > > containing the spatial data is spatially indexed, otherwise > the first query > > takes almost forever and users will just kill the > application out of > > frustration. > > If the real problem is long running queries, maybe using a > statement timeout > will solve your problem? Using a timeout to test for the presence of an index is not exact enough: I can't guard myself against false positives or false negatives. If the server is very busy at the moment all views might seem to be 'unindexed', i.e. unusable for live zooming and panning. The next day it might look different. I need to know in advance whether the queries would use a spatial index on the views. If no spatial index would be used, I have to make a local copy of (a subset of) the view (or table), create a local index and display the local copy instead. This is better than waiting for the timeout to expire and display nothing. With Oracle I can fire a spatial query on a view or table, and if the spatial column is not indexed, the entire query will fail. Unfortunately, with PostgreSQL, the spatial queries always succeed. Martin ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] Are SQL queries locale dependent?
Hi,
I recently found out that my app doesn't work in Spain because it creates
localized queries like this:
create table t (c1 float8, c2 float8);
insert into t (c1, c2) values (3,14159, 1,4142);
I understand that PostgreSQL obviously can't parse this query. But I'm not sure
how to fix this query so that it works under all circumstances.
If the database uses the "C" (or e.g. English or US) locale, then I know that I
always have to use the decimal dot ('.') instead of a decimal comma (','),
regardless of the locale of the client machine.
But what if the database itself uses a locale which mandates a decimal comma,
like Spanish, German, etc.? How do I have to construct the SQL query then?
Martin
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
