Re: Performance differences 7.1 to 7.3

Thank you Tom, your suggestion was exactly what I needed.

Two tables in view "tpv" were being joined on a column with different
data types.
One was "text" and the other one was "varchar(10)". The 'old' system
did not complain.
The 'new' system does not allow this sloppyness on my part. When I made
both table columns
the same, i.e. "varchar(10)" the time to execute the command became less
on the 'new' system than on the 'old' system.

Then I went to postgresql.org to make a contribution but the PayPal
thing ALWAYS trips me up.
When this PayPal thing is resolved, I will make a contribution.
It would be much better if postgresql.org accepted credit cards rather
than PayPal.

Anyway, I am most grateful to this pgsql_general list for answering my
questions which are often 'stupid' and you will soon
see my contribution.

Thank you,

Jim Apsey
-------------------------------------------
Tom Lane wrote:

>"Jimmie H. Apsey" <japsey(at)futuredental(dot)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
>
>
>