Postgres Pro
Downloads
Home   >   mailing lists

Thread: I don't understand this

I don't understand this

From
Uros Gruber
Date:
Hi!

i create some table with primary key and one index key
Then i insert data and try some explain on my select.

it tells me that index was used.


then i do some other queries and vacuum and when i try again
SAME explain it prints that it was used Seq Scan

It this normal or what. Or is some kind of a bug.


--
tia,
 Uros                          mailto:uros.gruber@sir-mag.com

Re: I don't understand this

From
Martijn van Oosterhout
Date:
On Mon, Apr 29, 2002 at 07:09:47PM +0200, Uros Gruber wrote:
> then i do some other queries and vacuum and when i try again
> SAME explain it prints that it was used Seq Scan
>
> It this normal or what. Or is some kind of a bug.

PostgreSQL attempt to adjust it's plan depending on what data is in the
table. If by adding new data the distribution has changed, it's decided it's
now better to do this other plan.

Is this other plan significantly slower?
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Canada, Mexico, and Australia form the Axis of Nations That
> Are Actually Quite Nice But Secretly Have Nasty Thoughts About America

Re: I don't understand this

From
Jan Wieck
Date:
Uros Gruber wrote:
> Hi!
>
> i create some table with primary key and one index key
> Then i insert data and try some explain on my select.
>
> it tells me that index was used.
>
>
> then i do some other queries and vacuum and when i try again
> SAME explain it prints that it was used Seq Scan
>
> It this normal or what. Or is some kind of a bug.

    Yes, this is normal.

    The  statistics  defaults  for  an  un-vacuumed table usually
    cause the optimizer to use an index scan.  When  you  analyze
    the  table  later, the statistics reflect the real status and
    for very small tables, a sequential scan is  normally  better
    than an index scan.


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #