How does postgresql perform queries on one table using more than one
index? 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;
By default, both indices will be btree's making them sorted. Therefore,
does postgresql retrieve all matching records from t1_f1 and t1_f2 into
intermediate tables and then performs somekind of merge sort before
retrieving the final results from t1? If so or if not, are intermediate
files created for this kind of operation or does the postgresql support
queries to multiple fields directly in its indexing system (perhaps aided
by "analyze")? Or, does this kind of operation rely much on memory?
I have tried making heads or tails out of the source code, but postgresql
is far more daunting than I had expected. Nevertheless, for future
reference, how could I find answers to questions about query management by
postgresql?
Many thanks for your excellent ordbms,
Marc Tardif