Tom,
There seems to exist some opportunities now with GIST which relate to
geometry/geography types (but not only...):
1. Index-only scans on geometry columns with SP-GIST (being able to do
a "SELECT id FROM my_table WHERE mygeom...;").
2. Index clustering incuding NULL values (i.e. being able to do a
"CLUSTER mygeom_index ON mytable;" ).
This discussion suggests that at least 1. is close to be implemented.
The problem of 2. has to do with handling NULL values; it's mentioned
in the PostGIS manual [1]. I'm aware of kd-tree index development [2].
Don't know if clustering and index-only scans would be resolved there.
But I can't find neither in the Todo List [3] ? What do you think?
Yours, Stefan
[2] http://postgis.refractions.net/docs/ch06.html#id2635907
[3] http://old.nabble.com/IMPORTANT%3A-%28Still%29-Seeking-Funding-for-Faster-PostGIS-Indexes-td32633545.html
[3] http://wiki.postgresql.org/wiki/Todo#Indexes
2011/12/14 Tom Lane <tgl@sss.pgh.pa.us>:
> Jesper Krogh <jesper@krogh.cc> writes:
>> On 2011-12-14 19:48, Tom Lane wrote:
>>> I think this is somewhat wishful thinking unfortunately. The difficulty
>>> is that if the index isn't capable of reconstructing the original value,
>>> then it's probably giving only an approximate (lossy) answer, which
>>> means we'll have to visit the heap to recheck each result, which
>>> pretty much defeats the purpose of an index-only scan.
>
>> I can see that it is hard to generalize, but in the tsvector case the
>> we are indeed not capable of reconstructing the row since the
>> positions are not stored in the index, the actual lookup is not a
>> lossy and I'm fairly sure (based on experience) that pg dont
>> revisit heap-tuples for checking (only for visibillity).
>
> Well, the way the tsvector code handles this stuff is that it reports
> the result as lossy only if the query actually poses a constraint on
> position (some do, some don't). That case was actually what made us
> move the determination of lossiness from plan time to execution time,
> since in the case of a non-constant tsquery, there's no way for the
> planner to know about it (and even with the constant case, you'd need a
> helper function that doesn't exist today). But this behavior is
> problematic for index-only scans because the planner can't tell whether
> a query will be lossy or not, and it makes a heck of a lot bigger
> difference than it used to.
>
> regards, tom lane
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
