Re: Bug with index-usage?

Scott Marlowe wrote:
> On Mon, 2005-11-14 at 11:07, Sebastian Böck wrote:
>
>>Hello,
>>
>>I get unpredictibale results selecting from a view depending on
>>index-usage.
>
>
> PostgreSQL uses a cost based planner.  So, it tends to not use the plan
> you might expect, especially in "toy" test cases with small data sets.
> I.e. why use an index to look up 10 values, when they all fit on the
> same page.  Just seq scan the data from the table.
>
> Fill up your table with REAL data (or a close substitute) and test
> again.  Also, read up on the admin section, specifically the part on the
> postgresql.conf file and what the settings in there mean, then read
> http://www.varlena.com/varlena/GeneralBits/Tidbits/perf.html
>

I think you didn't test my small script or don't see the same results.

I don't speak about index-usage per se, I'm talkung about the results.

Without indices I get:

SELECT * from test WHERE typ = 'a';
  id | typ | test1_id
----+-----+----------
   1 | a   |        1
   2 | a   |        2
   3 | a   |        3
(3 rows)

But with defined indices I get:

SELECT * from test WHERE typ = 'a';
  id | typ | test1_id
----+-----+----------
(0 rows)

By the way, this is 8.1 (forgot to mention in my first mail).

Sebastian