Skip site navigation (1) Skip section navigation (2)

Re: automated index suggestor -- request for comment

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Hannu Krosing <hannu(at)tm(dot)ee>
Cc: johnnnnnn <john(at)phaedrusdeinus(dot)org>,pgsql-performance(at)postgresql(dot)org
Subject: Re: automated index suggestor -- request for comment
Date: 2002-12-13 14:49:53
Message-ID: 9274.1039790993@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-performance
Hannu Krosing <hannu(at)tm(dot)ee> writes:
> That could become the EXPLAIN SPECULATE command ?

[ snicker... ]  Seriously, it wouldn't be hard to inject a slew of phony
index definitions into the planner to see what it comes up with.  You
just have to cons up an IndexOptInfo record, the planner will be none
the wiser.  The tricky part is deciding which indexes are even worth
expending planner cycles on.  ("Make 'em all" doesn't seem very
practical when you consider multi-column or functional indexes.)

Also, I don't see any reasonable way to automatically suggest partial
indexes; certainly not on the basis of individual queries.

The big boys approach this sort of problem with "workload analysis"
tools, which start from a whole collection of sample queries not just
one.  I don't think EXPLAIN applied to individual queries can hope to
produce similarly useful results.

			regards, tom lane

In response to

Responses

pgsql-performance by date

Next:From: johnnnnnnDate: 2002-12-13 15:20:54
Subject: Re: automated index suggestor -- request for comment
Previous:From: brewDate: 2002-12-13 13:27:19
Subject: Capping CPU usage?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group