>
> While this is not your questions, I still noticed you seem to be on 8.3 -
> it might be a bit faster to use GROUP BY instead of DISTINCT.
It didn't do a big difference, I already tried that before for this query.
Anyway, as you said, it's not the query having problems :)
> Your effective_cache_size is really small for the system you seem to have -
> its the size of IO caching your os is doing and uses no resources itself.
> And 800MB of that on a system with that amount of data seems a bit unlikely
> ;-)
>
> Using `free` you can see the amount of io caching your OS is doing atm. in
> the 'cached' column.
>
> That possibly might tip some plans in a direction you prefer.
>
> What kind of machine are you running this on?
I played with this parameter too, and it didn't influence the plan. Anyway, the
doc says it's the OS cache available for one query, and there may be a lot of
insert queries at the same time, so I chose to be conservative with this
value. I tried it with 8GB too, the plans were the same.
The OS cache is around 8-10GB by the way.
The machine is a dell PE2900, with 6 disks dedicated to this database (raid 10
config)