Index usage question
От | Jefim Matskin |
---|---|
Тема | Index usage question |
Дата | |
Msg-id | 11EB6F74CACFD21199370050DAB8AA12A35F3D@EXCHSPHERA обсуждение исходный текст |
Ответы |
Re: Index usage question
Re: Index usage question |
Список | 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@sphera.com > http://www.sphera.com/ >
В списке pgsql-general по дате отправления: