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

From: Deepa <kdeepa(at)midascomm(dot)com>
To: Shridhar Daithankar <shridhar_daithankar(at)persistent(dot)co(dot)in>
Cc: <pgsql-general(at)postgresql(dot)org>
Subject: Re: Creating index does not make any change in query plan.
Date: 2003-02-17 08:45:04
Message-ID: Pine.LNX.4.33.0302171413410.9962-100000@ws1154.midascomm.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi Shridhar,
Its 100,000 records.

Thanks a lot. When I do a select as explained by you I can able to
see the difference between Query plan for indexed and non indexed columns.

Here an integer used as a string. Why when it selected as a
integer, the query plan doesn't do a index scan, but when selected as a string,
the query planner uses index scan. What could be the possible reason?

On Mon, 17 Feb 2003, Shridhar Daithankar wrote:

> On 17 Feb 2003 at 14:02, Deepa wrote:
> > After doing Vacuum analyzing only I started doing the test. The
> > table also had nearly 1 lakh record. Then what could be the possible
> > reason.
>
> 1 lakh == 100,000 records, just to make things clear..
> >
> > On Mon, 17 Feb 2003, Martijn van Oosterhout wrote:
> >
> > > 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';
>
> Did you try this or explicitly casting this '2' to smallint/bigint?
>
> Bye
> Shridhar
>
> --
> Fun Facts, #14: In table tennis, whoever gets 21 points first wins. That's how
> it once was in baseball -- whoever got 21 runs first won.
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
>

--

Bye,
Deepa. K

--
Engineer,
Network Management System,
Midas Communication Technologies private Ltd,
Chennai.

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Shridhar Daithankar 2003-02-17 09:11:22 Re: Creating index does not make any change in query plan.
Previous Message Shridhar Daithankar 2003-02-17 08:36:55 Re: Creating index does not make any change in query plan.