Forum OpenACS Development: Re: PREPARE considered beneficial

Collapse
8: Re: PREPARE considered beneficial (response to 7)
Posted by Jeff Davis on 06/09/04 09:53 AM
> Shouldn't be too hard to make sort by queries prepareable I imagine).

I am pretty sure that is not true. In order to sort the query itself changes. you can't use a bind variable for the sort columns for example.

I saw a benchmark for doing inserts where going from 30k straight inserts to 1 prepare + 30k executes was ~25% faster. That's a bad example though since the inserts were pretty simple statements so for a complex query you might see something higher than that.

In terms of the timeline, PREPARE/EXECUTE showed up in 7.3 but the libpq support for prepare/execute with bind variables was a 7.4 thing (see PG History).

The 7.4 docs cover PQexecParams and PQexecPrepared which at some point we should start using (although not before we are ready to drop 7.3 compatibility).

Collapse
9: Re: PREPARE considered beneficial (response to 8)
Posted by Mark Aufflick on 06/09/04 04:09 PM
Mmm PQexecParams looks great - couldn't the nsd driver do a version check and use it if it's available?

Anyway, it's not needed for using prepare statements, it will just take the hassle of quoting off our driver for prepared and normal statements.

The case I am using is using a lot of fairly simple statements as well. i am imagining we can get a good speed boost for permissions type queries and complex queries like the crazy ones used by news aggregator.

RE: sorting - maybe you could have a fixed (unsorted) query prepared, and use it as a subselect within a select * from (execute foo) order by foo desc - I haven't tested that form though.

I haven't heard back on the postgres performance list about the effect or usefulness of prepare with queries in pgplsql - I would imagine that internally they would be "prepared" when the plpgsql is compiled, but I don't know.

Collapse
12: Re: PREPARE considered beneficial (response to 8)
Posted by Alfred Werner on 06/11/04 03:51 PM
To stretch it - I wonder if postresql is smart enough to recognize invariant subselects and keep those or if it runs the optimizer each time (in the case of sorted results). If it CAN recognize subselects, an additional layer of nesting might keep things fast (?)

e.g.

select a,b from foo where a=:a, b=:b order by $a_or_b;
select a,b from (select a,b from foo where a=:a, b=:b) order by $a_or_b;