Re: Creating index does not make any change in query plan.

> On Mon, Feb 17, 2003 at 01:06:26PM +0530, Deepa wrote:
>> Hi,
>> When I do explain on 'activealarms' table while selecting
>> a row with primary key (AFAIK while creating primary key, an index will be
>> created on that column), the following result occurs.
>>
>> EXPLAIN SELECT * from activealarms where recordid = 2;
>> NOTICE: QUERY PLAN:
>>
>> Seq Scan on activealarms (cost=0.00..7122.86 rows=1 width=189)
>>
>> EXPLAIN
>>
>> Here 'recordid' is the primary key whose datatype is bigint.
>
> Out of curiosity, what happens with:
>
> EXPLAIN SELECT * from activealarms where recordid = '2';
>
>> I cannot see difference in Query plan for a select query using primary key
>> and non primary key value. Then what could be the use of a field to be
>> used as a primary key.
>
> The planner doesn't care about primary and non-primary keys, it cares about
> indexes (unique and non-unique).
>
> Make sure you've run analyze recently and your tables are big enough to make
> an index scan worthwhile.
>

I don't think that this will solve the problem. I've uncovered a
similar problem recently. Vacuuming invalidates indexes, at
least as far as I can tell. Here is an example:
----------------------------------------------------------------------
web=# create table t1 ( i int primary key );
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
't1_pkey' for table 't1'
CREATE
web=# explain select * from t1 where i = 10;
NOTICE: QUERY PLAN:

Index Scan using t1_pkey on t1 (cost=0.00..4.82 rows=1 width=4)

EXPLAIN
web=# vacuum analyze t1;
VACUUM
web=# explain select * from t1 where i = 10;
NOTICE: QUERY PLAN:

Seq Scan on t1 (cost=0.00..0.00 rows=1 width=4)

EXPLAIN
web=# reindex table t1;
REINDEX
web=# explain select * from t1 where i = 10;
NOTICE: QUERY PLAN:

Index Scan using t1_pkey on t1 (cost=0.00..4.82 rows=1 width=4)

EXPLAIN
--------------------------------------------------------------------

That is not what I expected to happen.

> Hope this helps,

--
John Edstrom