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
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
