Received: from sss.pgh.pa.us (sss.pgh.pa.us [216.151.103.158]) by postgresql.org (8.11.3/8.11.1) with ESMTP id f4NGweA62294 for ; Wed, 23 May 2001 12:58:41 -0400 (EDT) (envelope-from tgl@sss.pgh.pa.us) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.11.3/8.11.3) with ESMTP id f4NGwbR15613; Wed, 23 May 2001 12:58:37 -0400 (EDT) To: "Michael Richards" cc: pgsql-sql@postgresql.org Subject: Re: index/join madness In-reply-to: <3B0B4776.0001A9.50190@frodo.searchcanada.ca> References: <3B0B4776.0001A9.50190@frodo.searchcanada.ca> Comments: In-reply-to "Michael Richards" message dated "Wed, 23 May 2001 01:15:34 -0400" Date: Wed, 23 May 2001 12:58:36 -0400 Message-ID: <15610.990637116@sss.pgh.pa.us> From: Tom Lane X-Archive-Number: 200105/254 X-Sequence-Number: 3082 "Michael Richards" 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