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
