Hi!
thanks for the quick help
i managed to reduce the response time from seemingly taking infinity to a tolerable level, which is by the way a huge improvement.
What helped was the functional index on date_trunc('day',yourfieldhere) as Scott suggested.
I tried to disable the geqo, but it didn't make any noticeable difference. For now, I am happy with the result, if the result set stays the same I can live without an upgrade.
1: Which 8.xx? 8.0.x or 8.1.x? 8.1.x is literally light years ahead
of 7.4 in terms of performance. 8.0 is somewhere between them. The
performance difference you're seeing is pretty common.
The benchmark was done on a small notebook running
8.1.4.1, versus 7.4.13 on a 2gig P4 server. The difference is astounding, even without the functional index 8.1 is 5-10 times faster than a full fledged server.
3: You are NOT Stuck on 7.4.13. I have a RHEL server that will be
running 8.1.4 or so pretty soon as a dataware house. It may get updated
to RHEL4, may not. You can either compile from the .tar.[gz|bz2] files
or download the PGDG rpms for your distro.
I know, but that's was I was trying to avoid. It is not that I would use the RHEL support provided for 7.4.13, but you know, staying official is the whole point of subscribing to RHEL4.
Moreover, since many of our other applications are running happily running under 7.4, I would be afraid to upgrade the whole thing.
So, again, thanks everybody for the help, you saved the day for me.
Regards
Eliott