Re: index/join madness

"Michael Richards" <michael(at)fastmail(dot)ca> writes:
> [ a severely incomplete problem description ]

Table schema? Full text of the query?

> It has one index defined on:
> Index "formdata_pkey"
> Attribute | Type
> -----------+---------
> formid | integer
> occid | integer
> userid | integer
> fieldid | integer
> unique btree (primary key)

> In my case I'm ignoring occid since it's always 1 for these values.
> Is there any way I can coerce this into using a multifield index?

It won't use *that* multifield index, at least not as a multifield
index, if you provide no constraint on occid. Per the documentation:

: The query optimizer can use a multi-column index for queries that
: involve the first n consecutive columns in the index (when used with
: appropriate operators), up to the total number of columns specified in
: the index definition. For example, an index on (a, b, c) can be used in
: queries involving all of a, b, and c, or in queries involving both a and
: b, or in queries involving only a, but not in other combinations. (In a
: query involving a and c the optimizer might choose to use the index for
: a only and treat c like an ordinary unindexed column.)

> Finally, I'm planning on moving this to 7.2 and converting all the
> joins to use outer joins. Will there be a significant penalty in
> performance running outer joins?

Compared to what? Outer joins are surely a lot faster than most of the
possible substitutes, but you didn't tell us what you're doing instead.

regards, tom lane