Thread: Indexes

Can someone shed some light on as to how the indexes actually work?

I want to index the results of a function, the function does a whole lot of different selects on different tables. Will
thedatabase know when to update the index.
 

If there already is some documentation on this (besides the source...) I would be glad to know.

Aasmund Midttun Godal

aasmund@godal.com - http://www.godal.com/
+47 40 45 20 46

On Fri, 12 Oct 2001, Aasmund Midttun Godal wrote:

> Can someone shed some light on as to how the indexes actually work?
> 
> I want to index the results of a function, the function does a whole
> lot of different selects on different tables. Will the database know
> when to update the index.

AFAIK No.  The functions for functional indexes probably should only
depend upon the arguments and no outside data.  Anything else sounds like
a recipe for broken indexes.

On Sat, 13 Oct 2001 14:17:48 GMT, "Aasmund Midttun Godal" <postgresql@envisity.com> wrote:
> On Fri, 12 Oct 2001 19:33:46 -0700 (PDT), Stephan Szabo <sszabo@megazone23.bigpanda.com> wrote:
> Well, then another question; will a function be only evaluated once inside a query with the specific arguments?
> 
> i.e.will
> 
> my_table
> 
> foo|bar
> 1  |2
> 1  |3
> 2  |1
> 1  |3
> 
> 
> SELECT foo, bar FROM my_table WHERE func(foo, bar);
> 
> will func be evaluated 3 or 4 times ?
> 
I did some research on this issue. and I noticed the following:

the func was executed for each row i.e. 4 times.
However if I set iscachable it was only executed 3 times.
Yet it was executed again next time I called the select (I would have expected it to still be cached) is this
intentional?

Or would it be a good idea to introduce a new option,

iscacheable (permanently cachable)
querycachable (cachable within a given query)

If there is some info on this issue please let me know!
> Aasmund Midttun Godal
> 
> aasmund@godal.com - http://www.godal.com/
> +47 40 45 20 46
Aasmund Midttun Godal

aasmund@godal.com - http://www.godal.com/
+47 40 45 20 46