[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?
=?iso-8859-1?Q?Martin_Sch=E4fer?= <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
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] update from multiple rows
I understand data_sys is the average value for the 3 days, from at the (Bday before to the day after. (BThis should do what you want, in one pass. Check the average function in (Bthe subselect. If what you want is to divide by 3 no matter how many (Brecords where found, enable the commented line. (B (BUPDATE mytable SET data_sys=TMP.average (BFROM ( (B--get the averages by date (BSELECT (BMT1.date AS date, (Bavg(MT2.data_raw) AS average (B--sum(MT2.data_raw)/3 AS average (BFROM (Bmytable MT1 (BINNER JOIN mytable MT2 ON (MT2.date BETWEEN MT1.date-1 AND MT1.date+1) (BGROUP BY (BMT1.date (B) AS TMP (BWHERE (Bmytable.date=TMP.date (B (BHope this is what you where looking for. (B (Badam etienne wrote: (B (B> hi (B> I have some trouble updating a table like this one : (B> date | data_raw | data_sys (B> 12-01 | 5 | 4.5 (B> 13-01 | 6 | 6 (B> 14-01 | 7 | 8 (B> (B> I would like to update the 'data_sys' row by computing values of (B> multiple 'data_raw' values. I mean for example : (B> data_sys(13-01) = (data_raw['12-01'] + data_raw['13-01'] + (B> data_raw['14-01'] )/3; (B> (B> I thought of a function that fetch the 3 data_raw rows for each (B> rows but it was obviously too much slow... (B> (B> Is there a more efficient way to achieve this ? (B> Thanks in advance.. This could help me very much.. (B> (B> Etienne Adam (B> (B> _ (B> $BL5NA%a!<%k$J$i$d$C$Q$j(B $B!V(BMSN Hotmail$B!W(B (B> http://www.hotmail.com/ (B> (B> ---(end of broadcast)--- (B> TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] (B> (B (B (B---(end of broadcast)--- (BTIP 6: Have you searched our list archives? (B (B http://archives.postgresql.org
[SQL] Value specific sequences?
Hello everyone,
I have a table of entities, each entity has a parent_id, I'd like to
have an insert trigger that assigns to that entity a sequential number
which gets incremented per parent_id.
i.e. doing:
INSERT INTO foo(id, parent_id) VALUES('a',1);
INSERT INTO foo(id, parent_id) VALUES('b',1);
INSERT INTO foo(id, parent_id) VALUES('c',2);
Should result in 'foo' containing:
id parent_id parent_index
'a'1 0
'b'1 1
'c'2 0
The two ways that come to mind are quering 'foo' on every insert for the
largest index for that parent_id (which seems slow) and using a separate
table of counters (which seems breaky).
Performance wise: the number of rows in 'foo' is not limited (ie will
grow continuously over the life of the app), but each parent_id will
usually only have about 10 foo's associated with it. To make things
simple, nothing can be deleted from foo, and once inserted the relevant
values cannot change.
Is there an elegant way to do this?
Your help greatly appreciated,
Dmitri
---(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
