Re: Creating index does not make any change in query plan. - Mailing list pgsql-general

From Shridhar Daithankar
Subject Re: Creating index does not make any change in query plan.
Date
Msg-id 3E50F492.26732.915C9CB@localhost
Whole thread Raw
In response to Re: Creating index does not make any change in query plan.  (Deepa <kdeepa@midascomm.com>)
Responses Re: Creating index does not make any change in query plan.
List pgsql-general
On 17 Feb 2003 at 14:43, Deepa wrote:

> On Mon, 17 Feb 2003, Shridhar Daithankar wrote:
> Actually the indexed field is an bigint field. But when a query plan is
> did on a selection using indexed field as a string it actually uses the
> index. Otherwise, it does a ordinary query plan  (i.e an non indexed query
> plan).
>
> >
> > What would be good in planner is to have field promotion like in C/C++,
> > smallint->bigint->float->double as and when required. That would help a hell
> > lot many people..
> >
>
>  Will you please tell me how does the field promotion helps in planner.

when you say id=2, it uses default int type which is smallint, AFAIK. So
smallint != bigint and hence planner does not consider using index.

When it is a string, conversion takes place which is to bigint because of field
you are comparing against. Now bigint == bigint and hence planner uses the
index.

With field promotion, planner would convert smallint to bigint and hence will
use the index if appropriate.

Please correct me if I am wrong. This is what my impressions are from listening
to list.

HTH


Bye
 Shridhar

--
QOTD:    On a scale of 1 to 10 I'd say...  oh, somewhere in there.


pgsql-general by date:

Previous
From: Deepa
Date:
Subject: Re: Creating index does not make any change in query plan.
Next
From: Deepa
Date:
Subject: Re: Creating index does not make any change in query plan.