Tom Lane wrote:
rihad <rihad@xxxxxxx> 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=?;
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
