Re: Partitioned Database and Choosing Subtables

>> Try using "dynamic" sql:
>>
>> EXECUTE 'SELECT lions, tigers, bears, statecode FROM WildLife WHERE
>> state_pt = ' || statecode INTO ...;

> Thanks Igor. It was a nice try -- and I thought it would work, but the Planner had other plans. Basically, I tried the interactive method using a PREPARE statement with one text parammeter followed by an EXECUTE statement. Unfortunately, the query still went rummaging across the entire database sequentially.

That's not the same as using dynamic SQL. In fact, that would behave just like your previous, problematic, query.

Dynamic SQL makes that the planner sees a new query each time, that needs to be planned from scratch.

OTOH, using prepared statements the query plan is stored with placeholders for the parameter values at the moment the PREPARE executes. That query plan is already set in stone - it will be a generic plan that can't be optimised for different values of parameters anymore. Not what you want.

Alban Hertroys

--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.

!DSPAM:737,4d8069c0235881427912932!