Re: count(*), EXISTS, indexes

Josh Berkus <josh(at)agliodbs(dot)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