Re: [HACKERS] Slow - grindingly slow - query

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