Re: does postgres has the same limitation as MySQL?

tekion <tekion(at)gmail(dot)com> writes:
> I know that MySQL can only use one index at at time for query. Does
> Postgres has this same limitation? For example, the following query:
>
> select uid,count(uid) from A, B where A.uid = B.uid and date between
> <date> and <date>
>
> MySQL will either use index on uid or the date (Both uid and date are
> indexed). Could Postgres use mulitple index in a query?

Preface: I assume that the date column is on table B, and that both A
and B have indexes on their respective "uid" columns.

There are two perspectives on this, both of which involve using
multiple indices :-).

1. A sensible query plan for this would be quite likely to involve
using the index on B(date) to find the relevant entries in table B,
and then join against relevant entries in A via the index on A(uid).

If one or the other table is small enough, or if the indexes otherwise
don't seem useful, then the query planner may choose *not* to use such
indices, but if the tables are reasonably large, then you are very
likely to find PostgreSQL using multiple indices for this query.

2. Since v8.1, PostgreSQL has been able to do "bitmap index scans,"
which allows composing multiple indexes together.

It wouldn't likely be relevant to this query, but here are the
relevant release notes from v8.1:

Allow index scans to use an intermediate in-memory bitmap (Tom)

In previous releases, only a single index could be used to do
lookups on a table. With this feature, if a query has WHERE
tab.col1 = 4 and tab.col2 = 9, and there is no multicolumn index
on col1 and col2, but there is an index on col1 and another on
col2, it is possible to search both indexes and combine the
results in memory, then do heap fetches for only the rows
matching both the col1 and col2 restrictions. This is very useful
in environments that have a lot of unstructured queries where it
is impossible to create indexes that match all possible access
conditions. Bitmap scans are useful even with a single index, as
they reduce the amount of random access needed; a bitmap index
scan is efficient for retrieving fairly large fractions of the
complete table, whereas plain index scans are not.
--
(format nil "~S(at)~S" "cbbrowne" "linuxfinances.info")
http://linuxfinances.info/info/sgml.html
The only problem
with Haiku is that you just
get started and then