Re: AW: AW: [HACKERS] Some notes on optimizer cost estimates

At 12:06 PM 1/26/00 -0500, Tom Lane wrote:
>"Ross J. Reedstrom" <reedstrm@wallace.ece.rice.edu> writes:
>> Ah, but you _can_ affect how the plans chosen, which in turn can affect
>> the optimizer. Not as part of a running, production system, I grant you,
>> but for debugging performance problems (and in particular, changes from
>> one release to the next) it can be useful. What I'm talking about are
>> the switches to the backend that tell pgsql not use particular kinds
>> of joins/scans in planning a query
>
>BTW, I have been thinking that it'd be a lot better if these flags could
>be twiddled via SET/SHOW commands, instead of having to restart psql.
>Nothing done about it yet, but it's an idea...

If something like this could be done for only the current transaction
or only the current backend, you'd have a sledgehammer-quality tool
for tuning individual queries, no?  Obviously not an ideal tool but
maybe helpful to some folks?

I've not looked at the code, maybe doing a SET would only affect
the current backend?

>Also, you already can twiddle the basic cost parameters (cpu_page_weight
>and cpu_index_page_weight) via SET variables whose names I forget at the
>moment.  There will be probably be at least one more such variable
>before 7.0 comes out, to control cost of random page fetch vs. sequential.

These should be helpful, too...again, are they system-wide or
limited to the current backend?  It would probably be nice to be
able to futz them for a particular query without impacting all other
queries going on at the same time.



- Don Baccus, Portland OR <dhogaza@pacifier.com> Nature photos, on-line guides, Pacific Northwest Rare Bird Alert
Serviceand other goodies at http://donb.photo.net.