For 8.3, I'd like to add the following two related features to assist
with Index Tuning and usability:

- Virtual Indexes

An index which only exists in the catalog, so is visible to the planner
but not the executor. This is useful where a specific SQL query is being
hand-tuned, allowing very specific options to be selected.

Virtual indexes would only be seen by the planner when performing an
EXPLAIN and when enable_virtual_index = on (default: off, Userset).
Normal SQL statements would ignore them completely, whatever
enable_virtual_index is set to.

It would not be possible to have both a virtual and a real index defined
identically at the same time. (If facilities existed to make temporary
tables exist only for a single backend, rather than requiring catalog
access then that implementation route would also work here, but until
that does, simple updates seem fine).

SQL: CREATE [VIRTUAL] [UNIQUE] INDEX ...


- RECOMMEND command

Similar in usage to an EXPLAIN, the RECOMMEND command would return a
list of indexes that need to be added to get the cheapest plan for a
particular query (no explain plan result though).

At planning time, all possible single column indexes would be assumed to
exist, plus all groups of cols that make up a multi-col Foreign Key
would be assumed to make a multi-col index. (PKs always exist,
remember). We track whether hypothetical indexes exist on the plan, so
once the cheapest plan has been decided we can report what they are (if
any). Hypothetical indexes last only for the duration of planning - no
catalog changes are made.

Command will return 1 row per selected index (can be more than one for a
complex query), first col gives list of indexed cols, second col shows
the SQL required to create that index. Virtual indexes will be noted,
though treated identically to hypothetical indexes.


The changes to do this would not be very invasive to the planner and
mainly involve adding additional fields to the planner data structures,
some additional branching code and command changes/additions.


Overall we need both of these new features: RECOMMEND covers many cases
in an easy to use form, with VIRTUAL indexes covers the rest of the
search space for possible new indexes for specific cases.

There's a host of other little tweaky bits we might imagine to enhance
this capability further, but this seems to cover the basic requirements.
Specifically, multi-column indexes are not considered very heavily in
RECOMMEND. This is deliberate because
a) we don't have good multi-col interaction stats (though we might have
for 8.3?)
b) it greatly increases the run-time of exhaustive searching and
c) because we have bitmap index interaction the usefulness of
multi-column indexes is much reduced anyhow, so cost/benefit not good.

Comments? (I'll do a summary of feedback tomorrow.)

--
Simon Riggs
EnterpriseDB http://www.enterprisedb.com


---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Simon Riggs wrote:
> For 8.3, I'd like to add the following two related features to assist
> with Index Tuning and usability:
>
> - Virtual Indexes

This seems useful, but I'm not sure we need a catalog object for that.
It might be sufficient to declare these hypothetical indexes within the
EXPLAIN command. That is after all the only place where they are
applied.

> - RECOMMEND command
>
> Similar in usage to an EXPLAIN, the RECOMMEND command would return a
> list of indexes that need to be added to get the cheapest plan for a
> particular query (no explain plan result though).

This functionality also seems useful, but maybe it should be the job of
a user-space tool?

--
Peter Eisentraut
http://developer.postgresql.org/~petere/

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Simon Riggs <simon@2ndquadrant.com> writes:
> - Virtual Indexes

> An index which only exists in the catalog, so is visible to the planner
> but not the executor.

Say what? What would that possibly be useful for, other than crashing
any bit of code that failed to know about it?

> - RECOMMEND command

> Similar in usage to an EXPLAIN, the RECOMMEND command would return a
> list of indexes that need to be added to get the cheapest plan for a
> particular query (no explain plan result though).

Both of these seem to assume that EXPLAIN results, without EXPLAIN
ANALYZE results to back them up, are sufficient for tuning. I find
this idea a bit dubious, particularly for cases of "marginal" indexes.

> Specifically, multi-column indexes are not considered very heavily in
> RECOMMEND.

That seems like a bad idea as well --- multicol indexes are exactly the
sort of thing a novice DBA might fail to consider. If you're going to
do this then you should consider all cases.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Peter Eisentraut wrote:
> Simon Riggs wrote:
>> For 8.3, I'd like to add the following two related features to assist
>> with Index Tuning and usability:
>>
>> - Virtual Indexes
>
> This seems useful, but I'm not sure we need a catalog object for that.
> It might be sufficient to declare these hypothetical indexes within the
> EXPLAIN command. That is after all the only place where they are
> applied.
>
>> - RECOMMEND command
>>
>> Similar in usage to an EXPLAIN, the RECOMMEND command would return a
>> list of indexes that need to be added to get the cheapest plan for a
>> particular query (no explain plan result though).
>
> This functionality also seems useful, but maybe it should be the job of
> a user-space tool?

On this same vein I thought it would be interesting if we added a
suggestion to explain analyze... Something like:


Your estimated number of rows appears to be off. Have you ran analyze
lately?

Sincerely,

Joshua D. Drake




--

=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/



---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

On Tue, Oct 10, 2006 at 06:06:09PM +0200, Peter Eisentraut wrote:
> Simon Riggs wrote:
> > For 8.3, I'd like to add the following two related features to assist
> > with Index Tuning and usability:
> >
> > - Virtual Indexes
>
> This seems useful, but I'm not sure we need a catalog object for that.
> It might be sufficient to declare these hypothetical indexes within the
> EXPLAIN command. That is after all the only place where they are
> applied.

If you wanted to try multiple scenarios, that might become a pain. I
guess it depends on how verbose the syntax was...

> > - RECOMMEND command
> >
> > Similar in usage to an EXPLAIN, the RECOMMEND command would return a
> > list of indexes that need to be added to get the cheapest plan for a
> > particular query (no explain plan result though).
>
> This functionality also seems useful, but maybe it should be the job of
> a user-space tool?

I think it makes the most sense to have this in core, though I guess an
argument could be made for having it be seperate from the backend. But
it'd have to be easy to call from an external tool, such as pgAdmin,
which means in probably needs to speak libpq.
--
Jim Nasby jim@nasby.net
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

On Tuesday 10 October 2006 12:06, Tom Lane wrote:
> > Similar in usage to an EXPLAIN, the RECOMMEND command would return a
> > list of indexes that need to be added to get the cheapest plan for a
> > particular query (no explain plan result though).
>
> Both of these seem to assume that EXPLAIN results, without EXPLAIN
> ANALYZE results to back them up, are sufficient for tuning. I find
> this idea a bit dubious, particularly for cases of "marginal" indexes.
>

While I agree with Tom that generally EXPLAIN is not enough for tuning, I also
know that when your dealing with queries that have run times in multiples of
hours (and the corresponding hour long index builds) EXPLAIN ANALYZE just
isn't an option. Anything that can be done to wheedle down your choices
before you have to run EXPLAIN ANALYZE is a bonus.

--
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Robert Treat <xzilla@users.sourceforge.net> writes:
> Anything that can be done to wheedle down your choices
> before you have to run EXPLAIN ANALYZE is a bonus.

Fair enough, but I prefer Peter's suggestion of attaching the
hypothetical index definitions to EXPLAIN itself, rather than making
bogus catalog entries. Something along the line of

EXPLAIN <statement>
ASSUMING INDEX fooi ON foo ....
[ ASSUMING INDEX ... ]

although this exact syntax probably doesn't work unless we're willing
to make ASSUMING a fully reserved word :-(

I have some vague recollection that this idea has been discussed
before...

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

> Simon Riggs <simon@2ndquadrant.com> writes:

>> - RECOMMEND command
>
>> Similar in usage to an EXPLAIN, the RECOMMEND command would return a
>> list of indexes that need to be added to get the cheapest plan for a
>> particular query (no explain plan result though).
>
> Both of these seem to assume that EXPLAIN results, without EXPLAIN
> ANALYZE results to back them up, are sufficient for tuning. I find
> this idea a bit dubious, particularly for cases of "marginal" indexes.


I think the idea of "virtual indexes" is pretty interesting, but
ultimately a lesser solution to a more fundimental issue, and that would
be "hands on" control over the planner. Estimating the effect of an index
on a query "prior" to creating the index is a great idea, how that is done
is something different than building concensus that it should be done.

Another thing that this brings up is "hints" to a query. Over the years, I
have run into situation where the planner wasn't great. It would be nice
to try forcing different strategies on the planner and see if performance
caan be improved.

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Mark,

> Another thing that this brings up is "hints" to a query. Over the years,
> I have run into situation where the planner wasn't great. It would be
> nice to try forcing different strategies on the planner and see if
> performance caan be improved.

See discussion on -performance.

--
--Josh

Josh Berkus
PostgreSQL @ Sun
San Francisco

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

On 10/10/06, Mark Woodward <pgsql@mohawksoft.com> wrote:
> I think the idea of "virtual indexes" is pretty interesting, but
> ultimately a lesser solution to a more fundimental issue, and that would
> be "hands on" control over the planner. Estimating the effect of an index
> on a query "prior" to creating the index is a great idea, how that is done
> is something different than building concensus that it should be done.
>
> Another thing that this brings up is "hints" to a query. Over the years, I
> have run into situation where the planner wasn't great. It would be nice
> to try forcing different strategies on the planner and see if performance
> caan be improved.
>

you can do this by setting enable_"access_method" type parameters.

--
regards,
Jaime Casanova

"Programming today is a race between software engineers striving to
build bigger and better idiot-proof programs and the universe trying
to produce bigger and better idiots.
So far, the universe is winning."
Richard Cook

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match