Re: Select query ignores index on large table

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(at)solfertje(dot)student(dot)utwente(dot)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!
>
>
>