Re: prepared statements suboptimal?

Tom Lane wrote:
> rihad <rihad@mail.ru> writes:
>> I don't understand why postgres couldn't plan this:
>> SELECT foo.e, foo.f
>> FROM foo
>> WHERE pk=$1 AND b=$2 AND status='1' AND c <= $3;
>
>> to be later executed any slower than
>
>> SELECT foo.e, foo.f
>> FROM foo
>> WHERE pk='abcabc' AND b='13' AND status='1' AND c <= '2007-11-20 13:14:15';
>
> The reason is that without knowing the parameter values, the planner has
> to pick a "generic" plan that will hopefully not be too awful regardless
> of what the actual values end up being.  When it has the actual values
> it can make much tighter estimates of the number of matching rows, and
> possibly choose a much better but special-purpose plan.  As an example,
> if the available indexes are on b and c then the best query plan for the
> first case is probably bitmap indexscan on b.  But in the second case,
> the planner might be able to determine (by consulting the ANALYZE stats)
> that there are many rows matching b='13' but very few rows with c <=
> '2007-11-20 13:14:15', so for those specific parameter values an
> indexscan on c would be better.  It would be folly to choose that as the
> generic plan, though, since on the average a one-sided inequality on c
> could be expected to not be very selective at all.
>
Aha, thanks for a thorough explanation. Now I understand that while
looking for a way to fulfill the query postgres will try hard to pick
the one requiring the least number of rows visits. I've skimmed over my
queries: almost all of them make use of the primary key as the first
thing in the WHERE clause (say, a username, which is the only pk in the
table): shouldn't that be enough for postgres to *always* decide to scan
the pk's index (since a query on a pk always returns either one or zero
results)?

  Same question for any number of joins where bar.id or baz.id is always
aPK:

select ... from foo JOIN bar ON(foo.bar_id=bar.id) JOIN baz
ON(foo.baz_id=baz.id) WHERE asd=? AND dsa=?;