Very poor estimates from planner

Since this is a large query, attachments for the explains / query.

Configuration:
        dev_iqdb=# select version();
                                        version
        ------------------------------------------------------------------------
         PostgreSQL 7.4beta1 on i386-portbld-freebsd4.7, compiled by GCC
        2.95.4
        (1 row)

        SET default_statistics_target = 1000;
        ANALYZE;
        set from_collapse_limit = 100;
        set join_collapse_limit = 20;

The query is a segment of a report but shows the issue well enough.

Effectively, the planner has amazingly inaccurate row estimates.  With
nestloop on, it estimates 1 row returned. With it off it estimates 12
rows returned.  The query actually returns several hundred thousand
entries.

ANALYZE output is in the nestloopoff file. With nestloopon I actually
run out of memory prior to query completion (1GB ram to the single
process).

Any hints? I'm basically stuck. Oh, and I would like to ask for a
pgadmin feature -- visual explain :)