Re: BUG #7598: Loss of view performance after dump/restore of the view definition

vaclav(dot)juza(at)xitee(dot)com writes:
> when a view (with the below properites) is dump and restored (no matter if
> using pg_dump, copied from pg_admin or using pg_views) it's performance is
> worse than before. The view was using tables with columns of type "character
> varying(xx)" and the dump inserts "::text" casts into the join conditions on
> these columns.

This is not the dump code's fault: those casts are legitimate, and
indeed present in the original view anyway. However, the planner was
having a problem with thinking that implicit and explicit casts weren't
equivalent. I've committed a fix into 9.2. I'm hesitant to change the
behavior further back than that, even though arguably this has been
broken since 8.3.

> In the real case we faced this problem, the performance loss was much higher
> on PostgreSQL 9.2.1 (3 seconds vs. 3 minutes) than on 9.1.4 (1.3 seconds vs.
> 7 seconds) and both variants were slower on 9.2.1 than on 9.1.4. In the test
> case below the behaviour is similar on both Postgres version.

It's difficult to tell whether there's any real issue here beyond that.
I sometimes get a noticeably slower plan out of HEAD, but sometimes I
don't, if I regenerate the random table contents. It looks to me like
the slower plans occur when it changes the join ordering, but that's
contingent on rowcount estimates that are equally awful in both versions;
the lvl = (SELECT max/min(lvl) ...) conditions are not estimated well by
any existing Postgres release. So I'm not inclined to ascribe a lot of
significance to the planner's choices here.

regards, tom lane