Re: Index usage question

I tested it and it was using an index scan. Have you
vacuum analyzed lately? I reformatted the tables to
look more friendly :-)

CREATE TABLE AvailablePlugins (
DirID int4,
ID int4 primary key,
Key text,
nMaxInstances int4,
bEnable int2 DEFAULT 0,
unique(DirID,Key)
);

CREATE TABLE PluginDir (
DirID int4 primary key,
nAccountID int4,
sPluginDirKey varchar(32)
);

explain select ap.DirID,pd.DirID
FROM AvailablePlugins ap, PluginDir pd
WHERE pd.DirID=ap.DirID;

NOTICE: QUERY PLAN:
Merge Join (cost=0.00..143.01 rows=10000 width=8)
-> Index Scan using availableplugins_dirid_key on availableplugins ap (cost=0.00..59.00 rows=1000 width=4)
-> Index Scan using plugindir_pkey on plugindir pd (cost=0.00..59.00 rows=1000 width=4)
EXPLAIN

- Einar Karttunen

On Tue, Sep 04, 2001 at 07:36:51PM +0200, Jefim Matskin wrote:
>
> 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/
> >
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly