Re: PostgreSQL vs ...

From: Andrei N. Sobchuck (2:5020/400)
To: Tolik Tentser (2:5054/37.63)
Date: 2005-01-31T12:39:38Z
Area: SU.DBMS
From: "Andrei N. Sobchuck" <andrei@mart.cherkassy.ua>

Tolik Tentser <Tolik.Tentser@p17.f292.n5000.z2.fidonet.org> wrote:
 TT> Единственное, что будет замедлять - у большого индекса будет больше глубина, 
 TT> от корня до нижних страниц, ну так она вообще-то растет зело медленно.

Вернёмся к сабжу. Здаётся, что в PostgreSQL есть фича, как раз
для такой ситуации:
http://tinyurl.com/4wyr5
...
A partial index is an index built over a subset of a table; the subset is defined by a conditional expression (called the predicate of the partial index). The index contains entries for only those table rows that satisfy the predicate.

A major motivation for partial indexes is to avoid indexing common values. Since a query searching for a common value (one that accounts for more than a few percent of all the table rows) will not use the index anyway, there is no point in keeping those rows in the index at all. This reduces the size of the index, which will speed up queries that do use the index. It will also speed up many table update operations because the index does not need to be updated in all cases.
...

http://tinyurl.com/52n6y
...
When the WHERE clause is present, a partial index is created. A partial index is an index that contains entries for only a portion of a table, usually a portion that is more useful for indexing than the rest of the table. For example, if you have a table that contains both billed and unbilled orders where the unbilled orders take up a small fraction of the total table and yet that is an often used section, you can improve performance by creating an index on just that portion. Another possible application is to use WHERE with UNIQUE to enforce uniqueness over a subset of a table. See Section 11.7 for more discussion.

The expression used in the WHERE clause may refer only to columns of the underlying table, but it can use all columns, not just the ones being indexed. Presently, subqueries and aggregate expressions are also forbidden in WHERE. The same restrictions apply to index fields that are expressions. 
...

-- 
Andrei N.Sobchuck
JabberID: andreis@jabber.ru. ICQ UIN: 46466235.

Отправлено через сервер Форумы@mail.ru - http://talk.mail.ru
--- ifmail v.2.15dev5.3
 * Origin: Talk.Mail.Ru (2:5020/400)
SEEN-BY: 50/203 520 450/159 186 451/30 452/25 100 454/9 455/15 461/33 43 74
SEEN-BY: 461/106 132 640 463/92 464/34 465/213 469/125 999 478/44 550/5068
SEEN-BY: 4614/20 4616/3 4625/8 9 4627/10 4635/4 1024 4651/25 4653/10 4657/50
SEEN-BY: 5000/76 5000 5001/50 211 5001 5002/5002 5003/34 5006/1 5007/1 5009/14
SEEN-BY: 5010/53 70 146 5011/13 5012/23 5015/4 28 5019/5 22 5020/52 104 115
SEEN-BY: 5020/118 128 133 150 175 201 362 371 400 545 639 642 715 755 758 780
SEEN-BY: 5020/794 892 894 902 921 968 982 1057 1100 1169 1200 1212 1234 1523
SEEN-BY: 5020/1604 1626 1642 1826 1835 1873 1922 1930 1992 2020 2200 2238 4400
SEEN-BY: 5020/4441 8383 12000 5022/5 128 5023/11 5025/750 5026/14 45 5029/32
SEEN-BY: 5030/69 115 195 217 382 436 473 556 611 920 966 1016 1900 5031/47
SEEN-BY: 5033/5 21 35 5034/8 5035/10 5036/1 13 5037/21 31 36 5041/4 5042/8 13
SEEN-BY: 5045/7 5049/157 5050/9 41 5051/15 35 5053/16 5054/1 8 9 10 28 35 37
SEEN-BY: 5054/45 50 63 5055/95 5056/16 5057/1 5058/24 77 5059/20 5060/88
SEEN-BY: 5061/15 5062/10 5063/41 51 5064/5 7 35 5066/18 5070/26 66 1222
SEEN-BY: 5071/22 5075/10 5079/49 5080/1003 5081/2 5082/6 5083/13 21 5084/32
SEEN-BY: 5093/4 27 5095/1 20 5100/113 6001/3 6009/1 6023/1 6033/2727 6045/7
PATH: 5020/400 4441 52 5054/1 37