How to find out programmatically whether a query on a view will use an index?
От | Martin Schäfer |
---|---|
Тема | How to find out programmatically whether a query on a view will use an index? |
Дата | |
Msg-id | 37936EEC582B394A9E1AA951991A551609D274@dev001_pdc.Dev.cadcorp.net обсуждение исходный текст |
Ответы |
Re: How to find out programmatically whether a query on a view will use an index?
Re: How to find out programmatically whether a query on a view will use an index? |
Список | pgsql-sql |
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
В списке pgsql-sql по дате отправления: