I asked earlier about ways of doing an UPDATE involving a left outer
join and got some very useful feedback.
This has thrown up a (to me) strange anomaly about the speed of such
an update.
The input to this query is a fairly large (the example I'm working
with has 335,000 rows) set of records containing numbers to be looked
up in the lookup table. This lookup table has 239 rows.
I'm always reading the suggestion that doing a 'VACUUM ANALYZE' on a
database is 'A Good Thing' as it helps the planner to do the best thing, so
I arranged a vacuum analyze on the input records.
Running the query takes about 13 mins or so.
If, however I *don't* do an analyze, but leave the input table as
it was when imported the run takes about 2.5 mins!
Looking at the output from 'explain' I can see that the main difference
in the way the planner does it is that it does a merge join in the non-analyze
case, and a hash join in the analyze case.
Unfortunately I don't really know what this is implying, hence the call
for assistance.
I have a file with all sorts of info about the problem (details of tables,
output of 'explain' etc) but as it is about 5K in size, and wide as well, I
didn't want to dump it in the list without any warning!
Regards,
Harry.
