Re: [PERFORM] Overriding the optimizer

[Tomasz Rybak]

Dnia 16-12-2005, pią o godzinie 16:16 +1300, Mark Kirkwood napisał(a):
> Craig A. James wrote:
> 
> > 
> > What would be cool would be some way the developer could alter the plan, 
> > but they way of doing so would strongly encourage the developer to send 
> > the information to this mailing list.  Postgres would essentially say, 
> > "Ok, you can do that, but we want to know why!"
> > 
> 
> Yeah it would - an implementation I have seen that I like is where the 
> developer can supply the *entire* execution plan with a query. This is 
> complex enough to make casual use unlikely :-), but provides the ability 
> to try out other plans, and also fix that vital query that must run 
> today.

I think you could use SPI for that.
There is function SPI_prepare, which prepares plan,
and SPI_execute_plan, executing it.
These functions are defined in src/backend/executor/spi.c.

I think (someone please correct me if I'm wrong) you could
prepare plan yourself, instead of taking it from SPI_prepare,
and give it to SPI_execute_plan.

SPI_prepare calls _SPI_prepare_plan, which parses query and calls
pg_analyze_and_rewrite. In your version don't call this function,
but provide PostgreSQL with your own plan (not-optimised according to
PostrgeSQL, but meeting your criteria).

-- 
Tomasz Rybak <[EMAIL PROTECTED]>


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

Re: [PERFORM] Need help in setting optimal configuration for a huge

[Tomasz Rybak]

Dnia 23-10-2005, nie o godzinie 09:31 -0700, Craig A. James napisał(a):

> MIN() and MAX() -- These are surprisingly slow, because they seem to do a 
> full table scan EVEN ON AN INDEXED COLUMN!
In 8.1 this is no true, see the changelog.

> I don't understand why, but happily there is an effective substitute:
> 
>select mycolumn from mytable order by mycolumn limit 1;  -- same as MIN()
> 
>select mycolumn from mytable order by mycolumn desc limit 1;  -- same as 
> MAX()

In 8.1 these queries are equivalent:

select mycolumn from mytable order by mycolumn limit 1;
select min(mycolumn) from mytable;

-- 
Tomasz Rybak <[EMAIL PROTECTED]>


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