From: | rihad <rihad(at)mail(dot)ru> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: prepared statements suboptimal? |
Date: | 2007-11-07 16:57:59 |
Message-ID: | 4731EE97.1050104@mail.ru |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Tom Lane wrote:
> rihad <rihad(at)mail(dot)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=?;
From | Date | Subject | |
---|---|---|---|
Next Message | Scott Marlowe | 2007-11-07 17:00:18 | Re: DB on a ramdisk (was Re: Temporary, In-memory Postgres DB?) |
Previous Message | Rainer Bauer | 2007-11-07 16:53:38 | Re: list of postgres related unexpected 'features' |