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
Views: Raw Message | Whole Thread | Download mbox | Resend email
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

Browse pgsql-performance by date

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