Re: count(*), EXISTS, indexes

Josh Berkus <josh@agliodbs.com> writes:
> Your query problem is that basically you have custom operators which the 
> planner doesn't know how to evaluate the return results on correctly.  This 
> is a radically different situation from how you presented it in your first 
> posting.

> This explains why the planner thinks that the exists clause will return 
> 255,000 rows instead of the handful it actually does return.   I'd suggest 
> re-building the query in several different syntaxes, until you find the one 
> the planner gets right.

Or more likely to work: build some custom selectivity estimation
functions to attach to the custom operators.

> Or build your own custom index types to take advantage of your custom 
> operators.    B-tree indexes are optimized for =, LIKE, <, and > queries; I 
> don't think they know what to do with "~<="

They certainly don't.  Possibly GIST could be taught what to do with
such things, but it won't happen by magic.
        regards, tom lane