Thread: Creating index does not make any change in query plan.
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.
The same procedure I followed for a non primary key value, which
retrieves 10 rows. The result is given below.
EXPLAIN SELECT * from activealarms where agentid = 2;
NOTICE: QUERY PLAN:
Seq Scan on activealarms (cost=0.00..7122.86 rows=10 width=189)
EXPLAIN
Here 'agentid' is the non primary key whose datatype is also bigint.
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.
--
regards,
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. Hope this helps, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > Support bacteria! They're the only culture some people have.
Attachment
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: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 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: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"
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 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: > 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 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: 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/ > Support bacteria! They're the only culture some people have.
Attachment
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