From: Tom Lane <tgl@sss.pgh.pa.us>
To: josh@agliodbs.com
Cc: Itai Zukerman <zukerman@math-hat.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: count(*), EXISTS, indexes
Date: Fri, 11 Apr 2003 20:00:06 -0400
Message-ID: <14783.1050105606@sss.pgh.pa.us> (raw)
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>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: tgl@sss.pgh.pa.us, josh@agliodbs.com, zukerman@math-hat.com
Subject: Re: count(*), EXISTS, indexes
In-Reply-To: <14783.1050105606@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox