PostgreSQL has some rather strange indexes, like partial indexes:

scott=> create index ename_ind on emp(ename)
scott-> where deptno=10;
CREATE INDEX
scott=>

The index has "where" clause and indexes only the records having
deptno=10. The database version is 8.3:


scott=> select version();

version
---------------------------------------------------------------------------------------------------
PostgreSQL 8.3.8 on i486-pc-linux-gnu, compiled by GCC gcc-4.3.real
(Ubuntu 4.3.3-5ubuntu4) 4.3.3
(1 row)

My question is whether anybody here is using partial indexes? The
documentation says that those indexes are good when one wants to avoid
indexing common values, but having come from the world of Oracle RDBMS, I
have never seen anything like that. Is anybody here really using this?
Any experiences with this?


--
http://mgogala.byethost5.com

Mladen Gogala wrote:

Yes, I use them. Actually, mostly as UNIQUE constraints with exceptions.

Mladen Gogala wrote:
I don't think we have a production database using them.

Partial indexes are certainly not something you need every day
(and you have to know about them too), but they are very useful
when used in the right place.

Consider a table that contains historical information, say, you
have a "valid from" and a "valid to" timestamp. The vast majority
of the data are historical, but most queries are against the current
data.

If you create partial index only on the current records (valid until
infinity), you can save a lot of unnecessary index space, index
scans will be faster and updates will be slightly faster.

In my experience indexes can easily take up as much space as the
actual data, so saving space there is a considerable improvement.

Yours,
Laurenz Albe

Mladen Gogala, 03.12.2009 06:17:
This is actually possible in Oracle as well:

CREATE INDEX ename_ind
ON EMP
(CASE
WHEN detpno = 10 THEN depto
ELSE NULL
END);

As Oracle will not include the NULLs in the index in this case, you have essentially a partial index as well.

Thomas

On Thu, 03 Dec 2009 12:18:45 +0100, Thomas Kellerer wrote:


Thanks, Thomas. Do you work anywhere in vicinity of the New York City? If
so, my email is on my web page, in the signature.


--
http://mgogala.byethost5.com