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)


Wed, 7 Feb 2001 10:10:24 +0800
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
Auto-indexing
* 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."

Tue, 6 Feb 2001 18:36:16 -0800
Alfred Perlstein <bright@wintelcom.net>
Re: Auto-indexing
> 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, Pennsylvania 19026

Tue, 6 Feb 2001 22:14:17 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Auto-indexing