Search Postgresql Archives

Re: does postgres has the same limitation as MySQL?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



tekion <tekion@xxxxxxxxx> 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@~S" "cbbrowne" "linuxfinances.info")
http://linuxfinances.info/info/sgml.html
The only problem
with Haiku is that you just
get started and then

-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux