I'm not sure I understand what is "qpqual" in your explanation. Once the
first indexscan is performed, is a temporary table created (in-file or
in-memory) containing the relevant tuples? If not, how can the remaining
part of the WHERE clause be evaluated against the previously selected
tuples during the first indexscan? Or, is the remaining part of the WHERE
clause re-evaluated again and again for each of the found tuples in the
first indexscan?
On Fri, 18 Feb 2000, Tom Lane wrote:
> Marc Tardif <admin@wtbwts.com> writes:
>
> > For example, assuming the following:
> >
> > create table t1 ( f1 int, f2 int);
> > create index t1_f1 on t1 (f1);
> > create index t1_f2 on t1 (f2);
> > select * from t1 where f1=123 and f2=456;
>
> The optimizer will attempt to guess which index is more selective
> (will return fewer tuples for its part of the WHERE clause). That
> index would be used for the indexscan, and the rest of the WHERE
> clause would be applied as a "qpqual", ie actually evaluated as
> an expression against each tuple found by the index.
>
> As you note, there's not any really efficient way to make use of
> independent indexes to evaluate an AND condition like this one.
> While Postgres' approach is pretty simplistic, I'm not sure that
> a more-complicated approach would actually be any faster.
>