7.2.1 optimises very badly against 7.2

I have a 10GB database which serves up customised tv listings selections at
www.ananova.com/tv <http://www.ananova.com/tv> (see "Personalise Listings"
in the left column)

We had it running very well under postgres 7.2 on a 4xPentium 700mhz with
8GB RAM

For a personalised selection from "start" to "end" of 7 channels [url1
below]
takes a fraction of a second to do the query and if I'm lucky enough to spot
it on "top" it uses low CPU percentage.

Under 7.2.1 it takes 99% CPU for between 5-9 seconds.
Rolling back to 7.2 is also very slow unless we vacuum analyse after rolling
back, then it is very fast again.
[We normally vacuum analyse every 24 hours]

We have max connections=128, 4520 shared buffers 91268 sortmem and collect
row level and block level stats.

I also have this anecodotal information;
we installed 7.2.1 on a fresh box and then restored a pg_dump of the tv
database and found it always very slow, stopping postgres and transferring
the binary DB files and restarting was very fast but degraded slowly over a
few days.

So I imagine there is something about stats gathering and use changed with
7.2.1 (I hear it has a new optimiser).

The query we do is complex and really does need a good optimiser (why we
don't use mysql) as it has to join programmes against broadcasts (restricted
by time) to channels (restricted to interested channels). It has to be
careful not to initially start with all broadcasts of a interested channel
as well as not all broadcasts on the interested channels.

[url1, 7 channels from midnight to about 6:00am day after]
http://www.ananova.com/tv_listings/tv_mainlisting.html?day=day1
<http://www.ananova.com/tv_listings/tv_mainlisting.html?day=day1&start=Start
&end=End&tvregion=n_14&i=1&p=0&h=&c=12.25.36.53.54.86.33&S=1>
&start=Start&end=End&tvregion=n_14&i=1&p=0&h=&c=12.25.36.53.54.86.33&S=1


_____

Samuel Liddicott
Support Consultant
sam(at)ananova(dot)com <mailto:sam(at)ananova(dot)com>
Direct Dial: +44 (0)113 367 4523
Fax: +44 (0)113 367 4680
Switchboard: +44 (0)113 367 4600

Ananova Limited
Marshall Mill
Marshall Street
Leeds
LS11 9YJ

http://www.ananova.com

Registered Office:
St James Court
Great Park Road
Almondsbury Park
Bradley Stoke
Bristol BS32 4QJ
Registered in England No.2858918

The information transmitted is intended only for the person or entity to
which it is addressed and may contain confidential and/or privileged
material. Any review, retransmission, dissemination or other use of, or
taking of any action in reliance upon, this information by persons or
entities other than the intended recipient is prohibited. If you receive
this in error, please contact the sender and delete the material from any
computer.