Re: Inheritance efficiency

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 because if 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 step before performing a query, which helps queries that are performed very frequently in a short time or that take a long time planning.

But skipping the query planner also has a drawback; the planner has to make a general assumption about what kind of data you'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 use a 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 depending on 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 the case of table partitioning, but would get fairly complex for prepared statements involving more parameters - so much so that 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!