pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedHow to find out programmatically whether a query on a view will use an index?
5+ messages / 3 participants
[nested] [flat]
* How to find out programmatically whether a query on a view will use an index?
@ 2005-01-24 16:34 Martin Schäfer <Martin.Schaefer@cadcorp.com>
2005-01-24 17:12 ` Re: How to find out programmatically whether a query on a view will use an index? Tom Lane <tgl@sss.pgh.pa.us>
2005-01-25 17:40 ` Re: How to find out programmatically whether a query on a view will use an index? Bruno Wolff III <bruno@wolff.to>
0 siblings, 2 replies; 5+ messages in thread
From: Martin Schäfer @ 2005-01-24 16:34 UTC (permalink / raw)
To: 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.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: How to find out programmatically whether a query on a view will use an index?
2005-01-24 16:34 How to find out programmatically whether a query on a view will use an index? Martin Schäfer <Martin.Schaefer@cadcorp.com>
@ 2005-01-24 17:12 ` Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 5+ messages in thread
From: Tom Lane @ 2005-01-24 17:12 UTC (permalink / raw)
To: Martin Schäfer <Martin.Schaefer@cadcorp.com>; +Cc: pgsql-sql
=?iso-8859-1?Q?Martin_Sch=E4fer?= <Martin.Schaefer@cadcorp.com> writes:
> 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,
AFAIK it's not localized, so grepping for "Index" would probably work.
> 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.
regards, tom lane
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: How to find out programmatically whether a query on a view will use an index?
2005-01-24 16:34 How to find out programmatically whether a query on a view will use an index? Martin Schäfer <Martin.Schaefer@cadcorp.com>
@ 2005-01-25 17:40 ` Bruno Wolff III <bruno@wolff.to>
1 sibling, 0 replies; 5+ messages in thread
From: Bruno Wolff III @ 2005-01-25 17:40 UTC (permalink / raw)
To: Martin Schäfer <Martin.Schaefer@cadcorp.com>; +Cc: pgsql-sql
On Mon, Jan 24, 2005 at 16:34:09 -0000,
Martin Schäfer <Martin.Schaefer@cadcorp.com> wrote:
>
> 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?
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: How to find out programmatically whether a query on a view will use an index?
@ 2005-01-24 17:22 Martin Schäfer <Martin.Schaefer@cadcorp.com>
0 siblings, 0 replies; 5+ messages in thread
From: Martin Schäfer @ 2005-01-24 17:22 UTC (permalink / raw)
To: pgsql-sql
> > 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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: How to find out programmatically whether a query on a view will use an index?
@ 2005-01-26 09:10 Martin Schäfer <Martin.Schaefer@cadcorp.com>
0 siblings, 0 replies; 5+ messages in thread
From: Martin Schäfer @ 2005-01-26 09:10 UTC (permalink / raw)
To: pgsql-sql
> > 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
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2005-01-26 09:10 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-01-24 16:34 How to find out programmatically whether a query on a view will use an index? Martin Schäfer <Martin.Schaefer@cadcorp.com>
2005-01-24 17:12 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-01-25 17:40 ` Bruno Wolff III <bruno@wolff.to>
2005-01-24 17:22 Re: How to find out programmatically whether a query on a view will use an index? Martin Schäfer <Martin.Schaefer@cadcorp.com>
2005-01-26 09:10 Re: How to find out programmatically whether a query on a view will use an index? Martin Schäfer <Martin.Schaefer@cadcorp.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox