Re: index/join madness

"Michael Richards" <michael@fastmail.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