pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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



view thread (4+ messages)

Message-ID: <15610.990637116@sss.pgh.pa.us>
Permalink:  ../15610.990637116@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/15610.990637116@sss.pgh.pa.us

 · 

reply

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