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.

Hope this helps,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Support bacteria! They're the only culture some people have.