Content-Type: text/plain;
  charset="iso-8859-1"
From: Josh Berkus <josh@agliodbs.com>
Reply-To: josh@agliodbs.com
Organization: Aglio Database Solutions
To: Itai Zukerman <zukerman@math-hat.com>
Subject: Re: count(*), EXISTS, indexes
Date: Fri, 11 Apr 2003 15:36:23 -0700
User-Agent: KMail/1.4.3
Cc: pgsql-sql@postgresql.org
References: <874r54sr8q.fsf@matt.w80.math-hat.com>
	<200304111442.48107.josh@agliodbs.com>
	<87he94r8ua.fsf@matt.w80.math-hat.com>
In-Reply-To: <87he94r8ua.fsf@matt.w80.math-hat.com>
MIME-Version: 1.0
Content-Transfer-Encoding: quoted-printable
Message-Id: <200304111536.23905.josh@agliodbs.com>

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.=20=20

Your query problem is that basically you have custom operators which the=20
planner doesn't know how to evaluate the return results on correctly.    Th=
is=20
is a radically different situation from how you presented it in your first=
=20
posting.

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

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

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?
>=20
> 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 sing=
le=20
index on all three columns.=20=20

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

--=20
-Josh Berkus
 Aglio Database Solutions
 San Francisco