> 1. Are the row estimates accurate? If not increase your statistics.
No, they aren't accurate, though they are up to date. I've found a
situation or two where there are definite patterns to the data that
tend to skew the gathered statistics, forcing estimates to be off by
an order of magnitude or more even at the highest resolution (1000),
and I suspect this will be such a case. I'm giving it a try, though
(on such a large table, it'll take a while to finish..) but that
should only affect which plan chosen, right? I'm perfectly fine with
the plan, I'd just like it to not die! Even if it does cause a
different plan to be chosen that works, that still doesn't answer the
question of why the earlier query failed.
> 2. Is the explain analyze different?
I cannot get to the point where explain analyze prints out results, as
'explain analyze' involves actually executing the query underneath. I
discovered this problem while running an 'explain analyze', and when
that failed I ran it straight out in case something in 'explain
analyze' was the problem. It wasn't.
> 3. Do these tables receive a lot of update/deletes? Could you possibly
> have index bloat?
Nope, the only actions done to them have been loading the data,
creating the index, analyzing, and various SELECTs. The data's
relatively new, untouched and static :( However, it took days to
create the index, and the index itself ended up being about 60GB
large, which I guess is reasonable on such a huge ginormous data set.
All the other queries I've run (involving slightly less complex plans)
haven't failed, and have executed in reasonable times.
One more interesting thing: after the process failes and the
transaction is rolled back, the process still has a 2GB memory
footprint! Don't most memory allocations palloc'd during query
execution get freed after the transaction is done? That's not
happening here..
-Aaron
