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 <
> 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:
> > 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.
In response to
pgsql-general by date
|Next:||From: Andre Lopes||Date: 2011-01-28 13:32:31|
|Subject: Re: Store base64 in database. Use bytea or text?|
|Previous:||From: Michael Kemanetzis||Date: 2011-01-28 12:57:45|
|Subject: Re: Select query ignores index on large table|