Re: Index usage question - Mailing list pgsql-general

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@sphera.com
> > http://www.sphera.com/
> >
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly