Jan Ploski <jpljpl@gmx.de> writes:
> What I still cannot grasp is why
> select msgnum into v_cnt from message where sectionID = 241
> order by dateSent desc limit 1;
> is so much faster than
> v_sid := 241;
> select msgnum into v_cnt from message where sectionID = v_sid
> order by dateSent desc limit 1;
The latter cannot use a partial index because the sectionID parameter
is a parameter, not a literal constant. The system has no way to
know that the SELECT won't be re-executed with a different value of
v_sid, so it can't generate a query plan that relies on the specific
value of v_sid. Thus, no partial-index-using plan will be produced.
You can get around that by judicious use of EXECUTE, because it doesn't
cache a query plan. But I see no need to; the partial-index approach is
going to be inferior to a correctly used single index anyway, because
the sheer number of indexes will bog things down (especially updates).
>> Another possible gotcha is that depending on datatype details the
>> planner might be using only one of the two index columns. As far
>> as I noticed, you didn't tell us the exact column datatypes or the
>> exact form in which the comparison values are supplied?
> The column types are integer for sectionID is and timestamp for dateSent.
> I am passing parameters of these types into a PL/pgSQL procedure, which then
> executes a "select into" with these parameters in the where clause.
That should be okay. People tend to get burnt with int2 and int8
columns ...
regards, tom lane
