Index usage question

From: Jefim Matskin <mjefim(at)sphera(dot)com>
To: "'pgsql-general(at)postgresql(dot)org'" <pgsql-general(at)postgresql(dot)org>
Subject: Index usage question
Date: 2001-09-04 17:36:51
Message-ID: 11EB6F74CACFD21199370050DAB8AA12A35F3D@EXCHSPHERA
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general


I have a question on index usage:
I have 2 tables:

CREATE TABLE tblAccountAvailablePlugins (
nAcctPluginDirID int4,
nAvailPluginID int4,
sPluginKey varchar(255),
nMaxInstances int4,
bEnable int2 DEFAULT 0
);

CREATE UNIQUE INDEX XPKtblAccountAvailablePlugins ON
tblAccountAvailablePlugins
(
nAvailPluginID
);

CREATE UNIQUE INDEX XAK1tblAccountAvailablePlugins ON
tblAccountAvailablePlugins
(
nAcctPluginDirID,
sPluginKey
);

CREATE TABLE tblAccountPluginDir (
nAcctPluginDirID int4,
nAccountID int4,
sPluginDirKey varchar(32)
);

CREATE UNIQUE INDEX XPKtblAccountPluginDir ON tblAccountPluginDir
(
nAcctPluginDirID
);

CREATE UNIQUE INDEX XAK1tblAccountPluginDir ON tblAccountPluginDir
(
nAccountID,
sPluginDirKey
);

When I execute the explain on a simple join query I see that the indices are
NOT used for
the join:

explain select tblAccountAvailablePlugins.nAcctPluginDirID,
tblAccountPluginDir.nAcctPluginDirID FROM tblAccountAvailablePlugins,
tblAccountPluginDir WHERE
tblAccountPluginDir.nAcctPluginDirID=tblAccountAvailablePlugins.nAcctPluginD
irID;
NOTICE: QUERY PLAN:

Hash Join (cost=21.45..640.50 rows=6530 width=8)
-> Seq Scan on tblaccountavailableplugins (cost=0.00..187.52 rows=10452
width=4)
-> Hash (cost=18.76..18.76 rows=1076 width=4)
-> Seq Scan on tblaccountplugindir (cost=0.00..18.76 rows=1076
width=4)

EXPLAIN

can anyone explain me what is wrong with my query?

select version();
version
---------------------------------------------------------------
PostgreSQL 7.1.2 on i686-pc-linux-gnu, compiled by GCC 2.95.3

> Jefim Matskin
> ---------------------------------------------
> Senior SW engeneer
> Sphera Corporation
> Tel: +972.3.613.2424 Ext:104
> mailto:mjefim(at)sphera(dot)com
> http://www.sphera.com/
>

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Einar Karttunen 2001-09-04 17:37:15 Re: Index usage question
Previous Message Jan Wieck 2001-09-04 17:32:48 Re: nested SQL with SPI