Обсуждение: Problematic Index Scan

Anyone for help !!!!
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 is
an 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 the
neeeded 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 scan
doesn'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.




                                                 SWTHRHS TOYRTOYNHS
                                                (tourtoun@csd.uch.gr)

On Monday 29 Jul 2002 1:35 pm, Tourtounis Sotiris wrote:
> Anyone for help !!!!
> I have a table with 3 attributes(int8, int8, int respectively) and I load
> to it a batch file of 150000 tuples,
> 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 is an 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.

Can't say I've ever seen this.

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

Never seen this. What people are going to need if they are to help you is the
EXPLAIN output both before and after this problem occurs. I'm assuming you
are running VACUUM ANALYSE after loading the data.

- Richard Huxton