Re: Expressional Indexes
От | Tom Lane |
---|---|
Тема | Re: Expressional Indexes |
Дата | |
Msg-id | 9824.1069397116@sss.pgh.pa.us обсуждение исходный текст |
Ответ на | Re: Expressional Indexes ("Randolf Richardson, DevNet SysOp 29" <rr@8x.ca>) |
Ответы |
Re: Expressional Indexes
|
Список | pgsql-sql |
"Randolf Richardson, DevNet SysOp 29" <rr@8x.ca> writes: > For the application I will need to develop in the future, it would be > okay for this index to hold data more than 30 days old, and then be > redefined on a monthly basis along with regular database vacuuming (and > other maintenance). Okay ... > Could this be done with a "partial index" as follows? > CREATE INDEX my_index on my_table (create_date) > WHERE (create_date > age(timestamp '30 days')); No, because the above represents a moving cutoff; it will (and should) be rejected as a non-immutable predicate condition. You could do something like CREATE INDEX my_Nov_03_index on my_table (create_date) WHERE (create_date >= date '2003-11-01'); and then a month from now replace this with CREATE INDEX my_Dec_03_index on my_table (create_date) WHERE (create_date >= date '2003-12-01'); bearing in mind that this index can be used with queries that contain WHERE conditions like "create_date >= some-date-constant". The planner must be able to convince itself that the right-hand side of the WHERE condition is >= the cutoff in the index's predicate condition. Since the planner is not very bright, both items had better be simple DATE constants, or it won't be able to figure it out ... > So an "Expression Index" could, for example, be used to sort alpha- > numeric data in a case-insensitive manner? I just want to make sure I'm > understanding this correctly. It won't do anything you could not have done in prior releases using a custom-built function as the named function of a functional index. In that sense it's just a notational improvement rather than a real advance in capability. (Actually I guess there is an advance in capability: 7.4 lets you build a multi-column index on multiple expressions, whereas in prior releases a functional index could only have one index column. Not sure how important this is in practice though.) regards, tom lane
В списке pgsql-sql по дате отправления: