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 <t...@sss.pgh.pa.us>: > Jesper Krogh <jes...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers