[postgis-users] Poor performance

[David Mitchell]

I have a table with 20 million rows and a geometric index. When I 
perform the following query:

select the_geom from links where the_geom && 'BOX3D((-73.8255319148936 
0,-69.1744680851064 44)'::BOX3D and func_class(flag) in (1,2);

It takes a lot longer to complete than it should.
The geometric index has a where clause: 'where func_class(flag) in (1,2)'

Func_class is an immutable function.

Explain analyze yields this:

                                                             QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------
  Index Scan using links_gist_majhwys2 on links  (cost=0.00..6.04 rows=1 
width=240) (actual time=0.060..731.866 rows=23535 loops=1)
    Index Cond: (the_geom && 'SRID=-1;BOX3D(-73.8255319148936 40 
0,-69.1744680851064 44 0)'::geometry)
    Filter: ((((ascii((flag)::text) / 10))::text = '1'::text) OR 
(((ascii((flag)::text) / 10))::text = '2'::text))
  Total runtime: 1350.587 ms

This is only returning 23 thousand rows, so it's got me why it takes so 
long. Any help would be appreciated.
-- 
David Mitchell
Software Engineer
Telogis

NOTICE:
This message (including any attachments) contains CONFIDENTIAL
INFORMATION intended for a specific individual and purpose, and
is protected by law.  If you are not the intended recipient,
you should delete this message and are hereby notified that any
disclosure, copying, or distribution of this message, or the
taking of any action based on it, is strictly prohibited.