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
<dalroi@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?
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!