From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Dean Rasheed <dean(dot)a(dot)rasheed(at)googlemail(dot)com> |
Cc: | Philippe Lang <philippe(dot)lang(at)attiksystem(dot)ch>, Tore Halvorsen <tore(dot)halvorsen(at)gmail(dot)com>, pgsql-general(at)postgresql(dot)org |
Subject: | Re: Index on immutable function call |
Date: | 2010-01-19 14:47:35 |
Message-ID: | 7983.1263912455@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Dean Rasheed <dean(dot)a(dot)rasheed(at)googlemail(dot)com> writes:
> 2010/1/19 Philippe Lang <philippe(dot)lang(at)attiksystem(dot)ch>:
>> That works just fine, but is there maybe a way of creating a slighly
>> more "generic" index? If I change the ">" with a "<" in the query, index
>> cannot of course be used. According to documentation, answer seems to be
>> "no"...
> You could create an index on the difference:
> create index long_transformation_index on indexed_table
> ((data1-this_is_a_long_transformation(data2)));
> then rewrite your queries accordingly:
> ... WHERE data1-this_is_a_long_transformation(data2) > some const (or
> < some const)
Yeah. There's been a lot of noise in this thread about statistics,
but the real problem is this: an indexable condition compares the
indexed value to a constant (or at least something that doesn't
change across rows). "col1 > foo(col2)" is not able to use an index
on col1, nor an index on foo(col2). But if you indexed the whole
expression col1 - foo(col2), you could get an index search on
comparisons of that to a constant.
regards, tom lane
From | Date | Subject | |
---|---|---|---|
Next Message | Terry | 2010-01-19 14:50:09 | Re: data dump help |
Previous Message | Dean Rasheed | 2010-01-19 13:54:15 | Re: Index on immutable function call |