Re: force re-planning of prepared statements?

On Tue, 2008-12-30 at 07:34 -0800, pgdba(at)hush(dot)com wrote:
>
> Hi Andrew,
>
> You are correct in assuming that there is some unexpected
> selectivity. It hinges on the client id being used as the filter,
> in this case, that id comprises only a very small fraction of the
> table (448 rows out of 43352606). My question isn't really whether
> or not the incorrect plan is being chosen, that part is pretty
> obvious by looking at the plan, but more along the lines of what I
> can do about it.
> I'll try your suggestion about "PDO::Statementexecute" vs
> "PDOStatement::bindParam()" and see if that makes a difference. If
> not, I'll re-post on the pgsql-perf list.

It seems to me that if PDO can *only* do prepared statements with
positional/named parameters then that is a pretty serious bug.

Potentially it can be fixed in the PostgreSQL driver, or in a wrapper
layer, but there should really be a way of calling PDO::query with
positional parameters as well, without the need for a prepare, as you
can in DBI.

I haven't used PDO myself yet, and was hoping to switch to it in a month
or two, but I can imagine a lot of circumstances where this would be
problematic.

A couple of maybe helpful suggestions, from further reading the PDO
documentation:

- Perhaps PDO::BindValue gives a different effect (I wouldn't hold my
breath though).

- Perhaps a partial index on client id would solve your bad plan.

CREATE INDEX client_id_partial ON client_whatsist(client_id) WHERE
client_id > 0;

Or something like that. Then in your query you can add a static part to
the WHERE clause that says client_id > 0 AND ... so that gets picked as
a high selectivity index.

It's a complete hack, but it's about the most likely thing I can think
of to work. In fact it may just be sufficient to add that in there.

Of course equally you can put the " ... WHERE client_id =
".intval($client_id)." ..." into the statement directly, so the client
ID is part of the preparation (or use PDO::quote if it isn't an intval,
of course). Sometimes a bit of pragmatism is easier than tracking down
the purist's solution.

Regards,
Andrew McMillan.

------------------------------------------------------------------------
andrew (AT) morphoss (DOT) com +64(272)DEBIAN
Q: What's a WASP's idea of open-mindedness?
A: Dating a Canadian.

------------------------------------------------------------------------