Marc Tardif <admin@wtbwts.com> writes:
> How does postgresql perform queries on one table using more than one
> index?
It doesn't. Simple enough, eh?
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.
If you have a multi-column index, eg
create index t1_f1_f2 on t1 (f1, f2);
then the system can and will use both clauses of the WHERE with
that single index. But again, it's not entirely clear that that's
all that much faster than just using the more-selective clause
in a smaller index. Furthermore, a multi-column index is more
specialized than single-column indexes because it is useful for
only a narrower range of queries; so you have to consider the extra
work done at insert/update to manage the extra index, and decide
if it's really a win overall for your application.
> how could I find answers to questions about query management by
> postgresql?
Asking questions on the mailing lists isn't a bad way to start.
Seeing what EXPLAIN says about how queries will be executed is
another nice learning tool.
There is some high-level implementation info in the SGML documentation,
and more scattered in various README files, but you won't really
understand a lot until you start burrowing into the source code.
regards, tom lane