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