On Mon, 2005-11-14 at 11:25, Sebastian Böck wrote:
> 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).
I don't get this problem in 7.4. I'll try 8.1 and get back to you.
