From: Josh Berkus <josh@agliodbs.com>
To: Itai Zukerman <zukerman@math-hat.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: count(*), EXISTS, indexes
Date: Fri, 11 Apr 2003 15:36:23 -0700
Message-ID: <200304111536.23905.josh@agliodbs.com> (raw)
In-Reply-To: <87he94r8ua.fsf@matt.w80.math-hat.com>
References: <874r54sr8q.fsf@matt.w80.math-hat.com>
<200304111442.48107.josh@agliodbs.com>
<87he94r8ua.fsf@matt.w80.math-hat.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.
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
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: josh@agliodbs.com, zukerman@math-hat.com
Subject: Re: count(*), EXISTS, indexes
In-Reply-To: <200304111536.23905.josh@agliodbs.com>
* 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