Re: count(*), EXISTS, indexes

Itai,

> They are my own operators and functions. profile is a integer array
> and the ~'s are subset operators. psig is a bit signature, "~" is
> complement, and the ~ operators again are subset operators.

You're going to have to work on your question-posting skills.

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 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 "~<="

At least, I think so. I'm not much of an expert on custom operators.

> > Do you have an index on rcp.profile, rcp.psig, rcp.filter?
>
> Yes, yes, and yes. ATM, though, there are only about 50 rows in
> rcount_prof. The vast majority of time is spent scanning the
> 600,000-row rprofile table.

Um, three seperate indexes on those three columns is not the same as a single
index on all three columns.

I was basically fishing for the reason why the planner got the row count so
radically wrong; now I think I know the reason ....

--
-Josh Berkus
Aglio Database Solutions
San Francisco