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. ---------------------------(end of broadcast)--------------------------- TIP 2: Don't 'kill -9' the postmaster
