pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: admin <admin@wtbwts.com>
To: pgsql-sql@postgresql.org
Subject: can't join with indices
Date: Fri, 17 Dec 1999 21:34:49 +0000 (GMT)
Message-ID: <Pine.BSF.4.10.9912172133390.8044-100000@server.b0x.com> (raw)

I can't seem to make pgsql use my indices when joining two tables. To
start, here's my query:
SELECT distinct on pid prod_base.pid, manu_base.mid
FROM prod_base, manu_base
WHERE prod_base.mid = manu_base.mid;

and these are the indices I have created on both tables:
CREATE INDEX prod_mid_idx ON "prod_base" USING btree ("mid" "int2_ops");
CREATE INDEX manu_mid_idx ON "manu_base" USING btree ("mid" "int2_ops");

Then I vacuumdb'd the whole database:
vacuumdb database

Finally, I tried running the same original query using EXPLAIN. With and
without the indices, the execution is exactly the same. I returned to the
manual and read some more, but couldn't find any concrete information. I
did find out I will be able to use a hash table later on instead of a
btree for joining, because I only join with '=' and I use LIMIT 100 which
makes it possible to use memcmp() without concern.

For completeness, there are the steps displayed by EXPLAIN:
Unique
  ->  Sort
        ->  Hash Join
              -> Seq Scan on prod_base
              -> Hash
                   -> Seq Scan on manu_base

Any information to make my original query quicker would be much
appreciated. Thanks in advance,
Marc





Message-ID: <Pine.BSF.4.10.9912172133390.8044-100000@server.b0x.com>
Permalink:  ../Pine.BSF.4.10.9912172133390.8044-100000@server.b0x.com/
Also on:    postgresql.org/message-id/Pine.BSF.4.10.9912172133390.8044-100000@server.b0x.com

 · 

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 from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: admin@wtbwts.com
  Subject: Re: can't join with indices
  In-Reply-To: <Pine.BSF.4.10.9912172133390.8044-100000@server.b0x.com>
  References: <Pine.BSF.4.10.9912172133390.8044-100000@server.b0x.com>

* 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