Partial indexes
Mladen Gogala
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?
Marco Mariani
> My question is whether anybody here is using partial indexes?
Yes, I use them. Actually, mostly as UNIQUE constraints with exceptions.
Laurenz Albe
> PostgreSQL has some rather strange indexes, like partial indexes:
>
>
> 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?
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
Thomas Kellerer
> 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:
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
Mladen Gogala
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.