Re: Inheritance efficiency - Mailing list pgsql-general

On 1 May 2010, at 5:33, John R Pierce wrote:

> Greg Smith wrote:
> my sql developer, who's been doing oracle for 15+ years, says postgres' partitioning is flawed from his perspective
becauseif you have a prepared statement like.. 
>
>   SELECT fields FROM partitioned_table WHERE primarykey = $1;
>
> it doesn't optimize this very well and ends up looking at all the sub-table indicies.

Yes it would, for a very logical reason.

A prepared statement is nothing but a stored query plan - its benefits are mostly that you can skip the query planning
stepbefore performing a query, which helps queries that are performed very frequently in a short time or that take a
longtime planning. 

But skipping the query planner also has a drawback; the planner has to make a general assumption about what kind of
datayou'll be querying. It can't vary the query plan depending on what data you're querying for. 

If someone is writing a query on a partitioned table and wants to rely on constraint exclusion and they're trying to
usea prepared statement then they don't understand what prepared statements are. 

You could argue that some logic could be added to the handling of prepared statements to insert query-subplans
dependingon what data you use for your parameters, but then you're moving back in the direction of unprepared
statements(namely invoking the query planner). It would help cases like this one, but it would hurt all other prepared
statements.It would at the least add a parse tree back into the queries path, which would be a fairly simplistic one in
thecase of table partitioning, but would get fairly complex for prepared statements involving more parameters - so much
sothat the benefit of using a prepared statement (not spending time planning the query) would get reduced
significantly.
It's possible that Oracle implemented something like this, but as you see it's not necessarily an improvement.

In practice people either query the correct table partition directly or do not use a prepared statement.

>   ir you instead execute the statement
>
>   SELECT fields FROM parritioned_table WHERE primarykey = constant;
>
> he says the planner will go straight to the correct partition.
>
> i haven't confirmed this for myself.

Alban Hertroys

--
Screwing up is an excellent way to attach something to the ceiling.


!DSPAM:737,4bdc08fc10416246414315!