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

From: John Edstrom <edstrom(at)jnrcom(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Re: Creating index does not make any change in query plan.
Date: 2003-02-17 21:45:21
Message-ID: 3E5157F1.8080900@jnrcom.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

> 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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Patrick Nelson 2003-02-17 21:49:03 Re: RE in where
Previous Message Jonathan Bartlett 2003-02-17 21:40:54 Re: Table Partitioning in Postgres: