From: Andrew McMillan <andrew@morphoss.com>
To: pgdba@hush.com
Cc: pgsql-php@postgresql.org
Subject: Re: force re-planning of prepared statements?
Date: Wed, 31 Dec 2008 10:31:46 +1300
Message-ID: <1230672706.28257.14.camel@happy.mcmillan.net.nz> (raw)
In-Reply-To: <20081230153444.DC59020040@smtp.hushmail.com>
References: <20081230153444.DC59020040@smtp.hushmail.com>
On Tue, 2008-12-30 at 07:34 -0800, pgdba@hush.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.
------------------------------------------------------------------------
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-php@postgresql.org
Cc: andrew@morphoss.com, pgdba@hush.com
Subject: Re: force re-planning of prepared statements?
In-Reply-To: <1230672706.28257.14.camel@happy.mcmillan.net.nz>
References: <20081230153444.DC59020040@smtp.hushmail.com> <1230672706.28257.14.camel@happy.mcmillan.net.nz>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox