Re: [BUGS] BUG #5228: Execution of prepared query is slow when timestamp parameter is used

aftab wrote:
> The following bug has been logged online:
>
> Bug reference: 5228
> Logged by: aftab
> Email address: akhangd(at)hotmail(dot)co(dot)uk
> PostgreSQL version: 8.3.8
> Operating system: Centos 5
> Description: Execution of prepared query is slow when timestamp
> parameter is used

It's far from clear that this is a bug. I've replied to the
pgsql-performance list to direct further discussion there.

Why this is happening: PostgreSQL has more information to use to plan a
query when it knows the actual values of parameters at planning time. It
can use statistics about the distribution of data in the table to make
better choices about query plans.

When you prepare a parameterized query, you're telling PostgreSQL to
plan a query that'll work well for _any_ value in those parameters. It
can't make as much use of statistics about the column(s) involved.

There has been periodic discussion on the mailing list about having an
'PREPARE NOCACHE' or 'EXECUTE REPLAN' command or something like that,
where you can use a parameterized query, but query planning is done each
time the query is executed based on the actual values of the parameters.
I don't know if this has come to anything or if anybody thinks it's even
a good idea.

( If it's thought to be, perhaps a TODO entry would be warranted? It
certainly needs a FAQ entry or an article in [[Category:Performance]] ).

At present, the only way I'm aware of to force re-planning while still
using query parameters is to wrap your parameterized query up in a
PL/PgSQL function that uses EXECUTE ... USING :

http://www.postgresql.org/docs/8.4/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN

... but PL/PgSQL has its own performance costs.

One thing that might help your query perform better without making any
changes to it is to give it more work_mem, which might let it use a
different sort or sort more efficiently. You can set work_mem per-user,
per-connection, per-database or globally - see the PostgreSQL documentation.

--
Craig Ringer