Tom Lane wrote:
> The Informix EXPLAIN results that Theo Kramer posted (a few messages
> back in this thread) are pretty interesting too. If I'm reading that
> printout right, Informix is not any smarter than we are about choosing
> the scan types for the outer and inner queries; and yet they have a much
> faster runtime for the WHERE IN query.
The informix EXPLAIN for the 'not in' query was when I did not have an
index on registrationtype (the explain appends to file sqexplain.out so I
missed it :(). Anyway here is the Informix EXPLAIN with the index on
registrationtype.
QUERY:
------
select accountdetail.domain from accountdetail where accountdetail.domain not in (select accountmaster.domain from
accountmaster)
Estimated Cost: 4510
Estimated # of Rows Returned: 58810
1) informix.accounts: SEQUENTIAL SCAN
Filters: (informix.accounts.domain != ALL <subquery> AND
informix.accounts.registrationtype != 'N' )
Subquery: --------- Estimated Cost: 12 Estimated # of Rows Returned: 10
1) informix.accounts: INDEX PATH
(1) Index Keys: registrationtype Lower Index Filter: informix.accounts.registrationtype = 'N'
The speed difference with or without the subquery index is neglible for
Informix.
--------
Regards
Theo
