Re: Query much slower when run from postgres function

From: decibel
Subject: Re: Query much slower when run from postgres function
Date: ,
Msg-id: E6757F02-1F73-4DDE-A5D4-0606CC517B1C@decibel.org
(view: Whole thread, Raw)
In response to: Re: Query much slower when run from postgres function  (Tom Lane)
Responses: Re: Query much slower when run from postgres function  (Віталій Тимчишин)
List: pgsql-performance

Tree view

Query much slower when run from postgres function  (Mario Splivalo, )
 Re: Query much slower when run from postgres function  (Tom Lane, )
  Re: Query much slower when run from postgres function  (Guillaume Cottenceau, )
   Re: [JDBC] Query much slower when run from postgres function  (Guillaume Smet, )
    Re: [JDBC] Query much slower when run from postgres function  (Tom Lane, )
     Re: [JDBC] Query much slower when run from postgres function  (Andreas Wenk, )
     Re: [JDBC] Query much slower when run from postgres function  (Dave Cramer, )
      Re: [JDBC] Query much slower when run from postgres function  (James Mansion, )
    Re: [JDBC] Query much slower when run from postgres function  (Scott Carey, )
   Re: Query much slower when run from postgres function  (Mario Splivalo, )
  Re: Query much slower when run from postgres function  (Mario Splivalo, )
   Re: Query much slower when run from postgres function  (Tom Lane, )
    Re: Query much slower when run from postgres function  (Mario Splivalo, )
     Re: Query much slower when run from postgres function  (Tom Lane, )
  Re: Query much slower when run from postgres function  ( (Frank Ch. Eigler), )
   Re: Query much slower when run from postgres function  (Tom Lane, )
    Re: Query much slower when run from postgres function  (decibel, )
     Re: Query much slower when run from postgres function  (Віталій Тимчишин, )
 Re: Query much slower when run from postgres function  (decibel, )

On Mar 10, 2009, at 12:20 PM, Tom Lane wrote:
>  (Frank Ch. Eigler) writes:
>> For a prepared statement, could the planner produce *several* plans,
>> if it guesses great sensitivity to the parameter values?  Then it
>> could choose amongst them at run time.
>
> We've discussed that in the past.  "Choose at runtime" is a bit more
> easily said than done though --- you can't readily flip between plan
> choices part way through, if you've already emitted some result rows.

True, but what if we planned for both high and low cardinality cases,
assuming that pg_stats indicated both were a possibility? We would
have to store multiple plans for one prepared statement, which
wouldn't work well for more complex queries (if you did high and low
cardinality estimates for each table you'd end up with 2^r plans,
where r is the number of relations), so we'd need a way to cap it
somehow. Of course, whether that's easier than having the ability to
throw out a current result set and start over with a different plan
is up for debate...

On a related note, I wish there was a way to tell plpgsql not to pre-
plan a query. Sure, you can use EXECUTE, but building the query plan
is a serious pain in the rear.
--
Decibel!, aka Jim C. Nasby, Database Architect  
Give your computer some brain candy! www.distributed.net Team #1828




pgsql-performance by date:

From: decibel
Date:
Subject: Re: Proposal of tunable fix for scalability of 8.4
From: Matteo Beccati
Date:
Subject: Re: Query performance over a large proportion of data