From: Tom Lane <tgl@sss.pgh.pa.us>
To: Michael Richards <michael@fastmail.ca>
Cc: pgsql-sql@postgresql.org
Subject: Re: index/join madness
Date: Wed, 23 May 2001 12:58:36 -0400
Message-ID: <15610.990637116@sss.pgh.pa.us> (raw)
In-Reply-To: <3B0B4776.0001A9.50190@frodo.searchcanada.ca>
References: <3B0B4776.0001A9.50190@frodo.searchcanada.ca>
"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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: tgl@sss.pgh.pa.us, michael@fastmail.ca
Subject: Re: index/join madness
In-Reply-To: <15610.990637116@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox