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@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
