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/
>
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 |