To: josh@agliodbs.com
Cc: Itai Zukerman <zukerman@math-hat.com>, pgsql-sql@postgresql.org
Subject: Re: count(*), EXISTS, indexes 
In-reply-to: <200304111536.23905.josh@agliodbs.com> 
References: <874r54sr8q.fsf@matt.w80.math-hat.com>
	<200304111442.48107.josh@agliodbs.com>
	<87he94r8ua.fsf@matt.w80.math-hat.com>
	<200304111536.23905.josh@agliodbs.com>
Comments: In-reply-to Josh Berkus <josh@agliodbs.com>
	message dated "Fri, 11 Apr 2003 15:36:23 -0700"
Date: Fri, 11 Apr 2003 20:00:06 -0400
Message-ID: <14783.1050105606@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

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