Tom Lane wrote:
The CLUSTER may be affecting things in some other way, like by squeezing out dead tuples causing a
reduction in the total table and index sizes.
I didn't mention I was the only user with transactions open on the
system during this. Would cluster eliminate more rows then vacuum full
if the only open transaction is the one running the vacuum and it is a
clean transaction?
You should try increasing the statistics targets on the columns you use
in the WHERE conditions.
We set it to 500 and couldn't get it to repeat the plan where it was
using the pair_idx, so that certainly helps.
I'm not at all sure I believe your premise that querying for a different
key value excludes cache effects, btw. On modern hardware it's likely
that CLUSTER would leave the *whole* of these tables sitting in kernel
disk cache.
You are exactly right. After rebooting the entire box and running
the query the query time was 15 seconds. Rebooting the box, running
cluster on all three tables and then executing the query was 120 ms. Is
calling cluster the only way to ensure that these tables get loaded into
cache? Running select * appeared to cache some but not all.
Thanks
