I am answering just for the sake of answering your questions. What hubert depesz lubaczewski suggested had fixed the problem i had.
I have other queries that need event_id to be the clustered index Veh_id is spread all over the table. (for veh_id 3 there are no records) Due to the spread of records all over the table the second plan that scans the whole table to get enough records takes ages. A planner should probably predict this (the fragmentation of veh_id) and mostly consider the second solution. Maybe it does that..who knows...all I know is my problem is fixed with the second two field index. Thank you for your effort to help. On Thu, Jan 27, 2011 at 8:14 PM, Alban Hertroys < dal...@solfertje.student.utwente.nl> wrote: > On 27 Jan 2011, at 15:04, Michael Kemanetzis wrote: > > > Hello, I'm experiencing a strange behavior running a simple select query > on a table that has about 12 million rows. Specifically, changing the > "LIMIT" value seems to change the execution plan but the result in one of > the cases is unjustifiably slow, as if it ignores all indexes. > > > > The table structure, the queries used and the results are here: > http://pastebin.com/fn36BuKs > > > > Is there anything I can do to improve the speed of this query? > > What does explain analyse say about query B? > > According to the query plan there are about 30k rows with veh_id = 3. From > the amount of disk I/O you describe it would appear that the rows > corresponding to that ID are all over the place. I expect that clustering > that table on the veh_id index will help in that case. > > It does seem a bit strange that the planner is choosing an index scan for > 30k records, I'd expect a sequential scan to be more efficient. That seems > to be another indication that your records are very fragmented with regards > to the veh_id. > > That, or you are running out of memory (the setting to look at is work_mem > I think). Did you change any settings from the defaults? > > > BTW, 12M records isn't considered a large table by many Postgres users. > It's certainly not small though, I suppose "average" fits the bill. > > Alban Hertroys > > -- > If you can't see the forest for the trees, > cut the trees and you'll see there is no forest. > > > !DSPAM:1227,4d41b61c11731997733516! > > >