Обсуждение: Index scan ???

I really want somebody's help and advice concerning to my problem, which
is the following :

I have a table with 3 attributes(int8, int8, int respectively) and I load
to it a batch file of 150000 tuples, which each of them contains a series
of java long, long and integer parameters respectively too. The first
attribute is classified in descending order and there are indexes on it
and to the third atribute respectively. I make some SQL questions on them
in order to use the created b-trees on the first attribute and for 3-4
times there isan index scan on it as it used to be. However, after those 3-4 times the
database start to use sequential scan on this first index. Then I stop the
session where i ask those SQL questions, i stop the running postmaster
with theneeeded number of buffers and i except that consecutively
running the postmaster again in a next session there will be again an
index scan on the first b-tree as it was in the beginning. However, index
scandoesn't work in that case as it should be. The only way is to make index
scan  work is to drop the whole database where the table is in and
re-installed it from the beggining. I wonder if there is an explanation of
that phenomenon and how i can assure the index scan function without the
reinstallation of the whole table. Thank you.



                                                 SWTHRHS TOYRTOYNHS
                                                (tourtoun@csd.uch.gr)

Tourtounis Sotiris dijo:

> The first attribute is classified in descending order and there are
> indexes on it and to the third atribute respectively. I make some SQL
> questions on them in order to use the created b-trees on the first
> attribute and for 3-4 times there isan index scan on it as it used to
> be. However, after those 3-4 times the database start to use
> sequential scan on this first index.

I don't really believe the query plan simply changes without something
else changing (query or data). Note that different queries _will_ have
different plans: ones will use index, others will not.  Also, concurrent
updates may be altering what you are seeing.  See the FAQ about indexes
not being used.

--
Alvaro Herrera (<alvherre[a]atentus.com>)
"Find a bug in a program, and fix it, and the program will work today.
Show the program how to find and fix a bug, and the program
will work forever" (Oliver Silfridge)