Received: from localhost (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id DC03664FDA5 for ; Tue, 30 Dec 2008 17:31:55 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 92846-09 for ; Tue, 30 Dec 2008 17:31:53 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from mail.morphoss.com (hoppy.mcmillan.net.nz [202.78.240.82]) by mail.postgresql.org (Postfix) with ESMTP id EB29F64FD33 for ; Tue, 30 Dec 2008 17:31:52 -0400 (AST) Received: from [IPv6:2404:130:b055:0:215:afff:fe48:6c48] (unknown [IPv6:2404:130:b055:0:215:afff:fe48:6c48]) by mail.morphoss.com (Postfix) with ESMTPS id E18A122F9A; Wed, 31 Dec 2008 10:31:48 +1300 (NZDT) Subject: Re: force re-planning of prepared statements? From: Andrew McMillan To: pgdba@hush.com Cc: pgsql-php@postgresql.org In-Reply-To: <20081230153444.DC59020040@smtp.hushmail.com> (sfid-20081231_043509_484283_5215F482) References: <20081230153444.DC59020040@smtp.hushmail.com> (sfid-20081231_043509_484283_5215F482) Content-Type: text/plain Date: Wed, 31 Dec 2008 10:31:46 +1300 Message-Id: <1230672706.28257.14.camel@happy.mcmillan.net.nz> Mime-Version: 1.0 X-Mailer: Evolution 2.22.3.1 Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none X-Spam-Level: X-Archive-Number: 200812/18 X-Sequence-Number: 3952 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. ------------------------------------------------------------------------