Re: Performance differences 7.1 to 7.3

"Jimmie H. Apsey" <japsey@futuredental.com> writes:
> On the 'old' Red Hat AS 2.1 here is the results of explain and the query:

The major problem seems to be that the old system is using a nestloop
with inner indexscan on ada_code:

>   ->  Nested Loop  (cost=870.92..4563.01 rows=342 width=56)
>         ...
>         ->  Index Scan using ada_code_pkey on ada_code  (cost=0.00..2.01 rows=1 width=12)

where the new system is using an inner seqscan:

>    ->  Nested Loop  (cost=6262.46..202496.78 rows=12948 width=45)
>          Join Filter: ("outer".service_code = ("inner".ada_code)::text)
>          ...
>          ->  Seq Scan on ada_code  (cost=0.00..10.06 rows=406 width=9)

The planner is well aware that this is a bad plan (note the much higher
cost estimate) --- I can only suppose that it is not able to select an
indexscan, most likely because of a datatype compatibility problem.
The cast to text appearing in the join condition is a tad suspicious
in this context.  What are the data types of service_code and ada_code,
and why aren't they the same?

IIRC, 7.3 is a lot less cavalier than 7.1 about the semantic differences
between char(n) and varchar(n)/text comparisons.  It's fairly likely
that the 7.1 plan is playing fast and loose with the comparison
semantics in order to generate an indexscan plan.  7.3 won't do that.
You need to make the column types the same to get good performance in
7.3 ... but if this is a foreign-key-reference arrangement, they ought
to be the same anyway.

            regards, tom lane