Forum OpenACS Development: Re: PREPARE considered beneficial

6: Re: PREPARE considered beneficial (response to 1)

Posted by Dirk Gomez on 06/09/04 05:36 AM

I was going through http://search.cpan.org/~rudy/DBD-Pg-1.32/Pg.pm - the docs for the Perl DBI Postgresql driver and it explicitly says this:

prepare

$sth = $dbh->prepare($statement, \%attr);

PostgreSQL does not have the concept of preparing a statement. Hence the prepare method just stores the statement after checking for place-holders. No information about the statement is available after preparing it.

(The driver seems to be quite old and it's a rather recent PostgreSQL version (7.3 I think) that has added some kind of prepared statements.)

Mark, on which database do you see the speed improvements?

7: Re: PREPARE considered beneficial (response to 6)

Posted by Mark Aufflick on 06/09/04 06:18 AM

Wow - so much discussion!

Postgres version - in this case I'm using 7.4.2, but I have also used prepare in 7.3.4 (not sure exactly what version it appeared in - either 7.2 or 7.3 something)

I first thought of preparing all queries on startup, but discarded that for the obvious reasons. I was imagining a tag in the xql query section for the developer to judiciously indicate "pre-prepare this query". The preparation could be done on first use rather than startup so we don't impact the (already long) startup time.

I didn't consider how to deal with the fact that the prepare statement needs to know the data types. Since I am planning for the developer to have to flag that they want the query prepared, i can also extend the queryset group to have a way to specify the types for a particular query.

Also since it is developer instigated per query, there won't be any problem with non-complete query fragments (like listbuilder queries with filters - just won't be able to prepare them unless we radically change the way filters work. Shouldn't be too hard to make sort by queries prepareable I imagine).

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).

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.

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;