Re: Performance weirdness with/without vacuum analyze

On Monday 13 October 2003 19:17, Harry Broomhall wrote:
> 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.

Can yu lookup those 239 values in 335,000 rows instead. The result will be
same but probably it will be lot moer faster...
>
> 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!

Thats weird..

> 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.

Turn the comparison other way round as I suggested. See if that makes any
difference..

>
> 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!

An explain analyze in both cases+postgresql.conf tuning and hardware/software
information would be very good. Compress it before you post if you think its
too big..

HTH

Shridhar