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

Re: automated index suggestor -- request for comment

From: johnnnnnn <john(at)phaedrusdeinus(dot)org>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: automated index suggestor -- request for comment
Date: 2002-12-13 15:20:54
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-performance
On Fri, Dec 13, 2002 at 09:49:53AM -0500, Tom Lane wrote:
> 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. 

That's good news. The easier it is, the more likely i am to actually
get it working and available to people.

> 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.)

Agreed. But for a first development iteration, "Make 'em all" could
certainly include the combinatorial explosion of all single- and
multi-column indices. It might be slow as a dog, but it would exist.

> 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.

Again, agreed. My intent was to start with something simple which
could only deal with one query at a time, and then build a more robust
tool from that point.

That said, i wasn't planning on grafting onto the EXPLAIN syntax, but
rather creating a new SUGGEST command, which could take a query or
eventually a workload file. The other option was to decouple it from
pg proper and have an independent application to live in contrib/ or


In response to

pgsql-performance by date

Next:From: johnnnnnnDate: 2002-12-13 15:37:19
Subject: Re: automated index suggestor -- request for comment
Previous:From: Tom LaneDate: 2002-12-13 14:49:53
Subject: Re: automated index suggestor -- request for comment

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