Обсуждение: Auto-indexing
Is it a feasible idea that PostgreSQL could detect when an index would be handy, and create it itself, or at least log that a table is being queried but the indices are not appropriate? I suggest this as it's a feature of most windows databases, and MySQL does it. I think it would be a great timesaver as we have hundreds of different queries, and it's a real pain to have to EXPLAIN them all, etc. Is that possible? Feasible? Chris -- Christopher Kings-Lynne Family Health Network (ACN 089 639 243)
* Christopher Kings-Lynne <chriskl@familyhealth.com.au> [010206 18:29] wrote: > Is it a feasible idea that PostgreSQL could detect when an index would be > handy, and create it itself, or at least log that a table is being queried > but the indices are not appropriate? > > I suggest this as it's a feature of most windows databases, and MySQL does > it. I think it would be a great timesaver as we have hundreds of different > queries, and it's a real pain to have to EXPLAIN them all, etc. Is that > possible? Feasible? Probably both, but if it's done there should be options to: .) disable it completely or by table/database or even threshold or disk free parameters (indicies can be large) .) log any auto-created databases to inform the DBA. .) if disabled optionally log when it would have created an index on the fly. (suggest an index) .) expire old and unused auto-created indecies. Generally Postgresql assumes the user knows what he's doing, but it couldn't hurt too much to provide an option to have it assist the user. -- -Alfred Perlstein - [bright@wintelcom.net|alfred@freebsd.org] "I have the heart of a child; I keep it in a jar on my desk."
> Probably both, but if it's done there should be options to: > > .) disable it completely or by table/database or even threshold or > disk free parameters (indicies can be large) > .) log any auto-created databases to inform the DBA. > .) if disabled optionally log when it would have created an index on > the fly. (suggest an index) > .) expire old and unused auto-created indecies. > > Generally Postgresql assumes the user knows what he's doing, but > it couldn't hurt too much to provide an option to have it assist > the user. I want to implement a SET PERFORMANCE_TIPS, hopefully for 7.2: * Add SET PERFORMANCE_TIPS option to suggest INDEX, VACUUM, VACUUM ANALYZE, and CLUSTER I think suggesting items to the adminstrator is the way to go. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026