Re: Creating tons of tables to support a query

Jan Ploski <jpljpl(at)gmx(dot)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