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

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.

Hi Martijn,

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.

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';

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,

--

Bye,
Deepa. K

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

On 17 Feb 2003 at 14:15, Deepa wrote:

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?

It is very simple. Unless the indexed field and the field in query does not
match exactly in type, the planner does not consider using index.

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..

Bye
Shridhar

--
aquadextrous, adj.: Possessing the ability to turn the bathtub faucet on and
off with your toes. -- Rich Hall, "Sniglets"

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.

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.

On Mon, 17 Feb 2003, Shridhar Daithankar wrote:

It is very simple. Unless the indexed field and the field in query does not
match exactly in type, the planner does not consider using index.

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.

Bye
Shridhar

--

Bye,
Deepa. K

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

On 17 Feb 2003 at 15:14, Deepa wrote:

On Mon, 17 Feb 2003, Shridhar Daithankar wrote:

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.

But even when a number that has a value of 4 bytes is set in the where
condition, the planner is not uses indexed scan. In this case how come
this will take the number as small int. Is their any other reason for
that.

4 bytes != bigint.

Bye
Shridhar

--
wok, n.: Something to thwow at a wabbit.

On Mon, 17 Feb 2003, Shridhar Daithankar wrote:

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.

But even when a number that has a value of 4 bytes is set in the where
condition, the planner is not uses indexed scan. In this case how come
this will take the number as small int. Is their any other reason for
that.

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.

--

Bye,
Deepa. K

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

On Mon, 17 Feb 2003, Shridhar Daithankar wrote:

Sorry its not exactly 4 bytes, its greate than 4 bytes.

On 17 Feb 2003 at 15:14, Deepa wrote:

On Mon, 17 Feb 2003, Shridhar Daithankar wrote:

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.

But even when a number that has a value of 4 bytes is set in the where
condition, the planner is not uses indexed scan. In this case how come
this will take the number as small int. Is their any other reason for
that.

4 bytes != bigint.

Bye
Shridhar

--
wok, n.: Something to thwow at a wabbit.

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

--

Bye,
Deepa. K

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

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

On Mon, Feb 17, 2003 at 01:45:21PM -0800, John Edstrom wrote:

On Mon, Feb 17, 2003 at 01:06:26PM +0530, Deepa wrote:
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:

Umm, did you read my statement? A table with one row is not worth using an
index. What you're seeing is the ANALYZE (not the VACUUM) updating the
statistics to say "an index scan is brain dead here". What I'm more curious
about is why the REINDEX caused it to forget the statistics, thus making it
use the brain-dead index scan again.

----------------------------------------------------------------------
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
--------------------------------------------------------------------

--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/

Martijn van Oosterhout <kleptog@svana.org> writes:

What I'm more curious
about is why the REINDEX caused it to forget the statistics, thus making it
use the brain-dead index scan again.

Both CREATE INDEX and REINDEX are coded to put the initial default
relpages/reltuples values (10/1000) into the table's pg_class entry,
rather than the true counts that they computed as a byproduct of
building the index, if the true tuple count is zero. The motivation for
this is that if you do CREATE TABLE and then immediately CREATE INDEX
before loading up any data, you don't want the default values to be
replaced by zeroes --- that would make performance go to heck as soon as
any reasonable amount of data gets loaded into the table. (The defaults
are chosen with malice aforethought to be large enough to prompt
indexscans.)

VACUUM, on the other hand, figures it's okay to mark an empty table
as empty.

REINDEX behaves the way it does because it's built on top of CREATE
INDEX. I'm not sure that the it-might-be-a-brand-new-table argument
should be applied to REINDEX though. Maybe it'd be better to go ahead
and store the zeroes in that case.

regards, tom lane

I too did the similar type of test and got the same result. Will any one
tell me what could be possible solution for this.

--

Bye,
Deepa. K

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

---------- Forwarded message ----------
Date: Mon, 17 Feb 2003 13:45:21 -0800
From: John Edstrom <edstrom@jnrcom.com>
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Creating index does not make any change in query
plan.

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

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster