Thanks Jeff. These queries in particular relate to a set of data that is
rebuilt on a periodic basis. For all intents and purposes, the data is
newly populated and unlikely to reside in cache - hence the need to
perform my tests under similar conditions.
It's probably better than I adjust the ran
On Sat, Aug 10, 2013 at 5:24 PM, Tim Kane wrote:
>
> Again, just thinking out loud here.. In a scenario where specific
> clustering isn't an option...
> I wonder if the query planner should consider the physical
> distribution/ordering of values on disk, and use that as a factor when
> applyin
On Sat, Aug 10, 2013 at 10:32 AM, Timothy Kane wrote:
>
> Hi all,
>
> I seem to be having some grief with the 9.1.9 query planner favouring an
> index scan + merge join, over a sequential scan + hash join.
> Logically I would have considered the index+merge to be faster, as suggested
> by the expl
Yep, the effective_cache_size was specifically because we have lots of RAM to
play with, and want to ensure we are caching wherever possible.
The reduced random_page_cost was primarily to allow for the fact we're using
relatively fast disk (indexes are SSD, table data on SAS drives), though I
d
Tim Kane writes:
> I guess the clustering approach managed to work around the need to mess with
> the statistics target. I did previously increase the target to 1000 (from
> 100) for that field and had no impact, but this is an aspect of tuning I'm
> not so familiar with - I didn't consider pu
Ahh, thanks Tom.
I hadn't seen your email before I posted my own followup.
I guess the clustering approach managed to work around the need to mess with
the statistics target. I did previously increase the target to 1000 (from 100)
for that field and had no impact, but this is an aspect of tuni
Okay, so I've played with this a bit more. I think I have it cracked.
I had to increase random_page_cost and significantly reduce
effective_cache_size in order for the planner to prefer a sequential scan. (It
turns out this is what the 8.3.8 instance was doing all along, so it's not
anything s
Timothy Kane writes:
> I seem to be having some grief with the 9.1.9 query planner favouring an
> index scan + merge join, over a sequential scan + hash join.
I believe the reason it's preferring the merge join plan is that it thinks
the executor will be able to terminate the merge join early as
Hi all,
I seem to be having some grief with the 9.1.9 query planner favouring an index
scan + merge join, over a sequential scan + hash join.
Logically I would have considered the index+merge to be faster, as suggested by
the explain output - but in practice, it is in fact slower by orders of
