Re: Any better plan for this query?..

Hi,

Dimitri <dimitrik(dot)fr(at)gmail(dot)com> writes:

>>> So, why I don't use prepare here: let's say I'm testing the worst
>>> stress case :-) Imagine you have thousands of such kind of queries -
>>> you cannot prepare all of them! :-)
>>
>> Thousands? Surely there'll be a dozen or three of most common queries,
>> to which you pass different parameters. You can prepare thoseu
>
> Ok, and if each client just connect to the database, execute each kind
> of query just *once* and then disconnect?.. - cost of prepare will
> kill performance here if it's not reused at least 10 times within the
> same session.

In a scenario which looks like this one, what I'm doing is using
pgbouncer transaction pooling. Now a new connection from client can be
served by an existing backend, which already has prepared your
statement.

So you first SELECT name FROM pg_prepared_statements; to know if you
have to PREPARE or just EXECUTE, and you not only maintain much less
running backends, lower fork() calls, but also benefit fully from
preparing the statements even when you EXECUTE once per client
connection.

> Well, I know, we always can do better, and even use stored procedures,
> etc. etc.

Plain SQL stored procedure will prevent PostgreSQL to prepare your
queries, only PLpgSQL functions will force transparent plan caching. But
calling this PL will cost about 1ms per call in my tests, so it's not a
good solution.

It's possible to go as far as providing your own PostgreSQL C module
where you PREPARE at _PG_init() time and EXECUTE in a SQL callable
function, coupled with pgbouncer it should max out the perfs. But maybe
you're not willing to go this far.

Anyway, is hammering the server with always the same query your real
need or just a simplified test-case? If the former, you'll see there are
good ways to theorically obtain better perfs than what you're currently
reaching, if the latter I urge you to consider some better benchmarking
tools, such as playr or tsung.

https://area51.myyearbook.com/trac.cgi/wiki/Playr
http://tsung.erlang-projects.org/
http://pgfouine.projects.postgresql.org/tsung.html
http://archives.postgresql.org/pgsql-admin/2008-12/msg00032.php

Regards,
--
dim