Re: [HACKERS] Slow - grindingly slow - query

Vadim wrote:

> > I did the same on Informix Online 7 and it took less than two minutes...
>
> Could you run the query above in Informix?
> How long would it take to complete?

I include both explain and timing for the queries for both postgres and
Informix.

Explain from postgres for the two queries.
------------------------------------------

explain select accountdetail.domain from accountdetail where
accountdetail.domain not in
(select accountmaster.domain from accountmaster);
NOTICE: QUERY PLAN:

Seq Scan on accounts (cost=3667.89 rows=34958 width=12)
SubPlan
-> Index Scan using registrationtype_idx on accounts (cost=2444.62 rows=33373 width=12)

EXPLAIN

explain select accountdetail.domain from accountdetail
where not exists (
select accountmaster.domain from accountmaster where
accountmaster.domain = accountdetail.domain);
NOTICE: QUERY PLAN:

Seq Scan on accounts (cost=3667.89 rows=34958 width=12)
SubPlan
-> Index Scan using domain_type_idx on accounts (cost=2.04 rows=1 width=12)

EXPLAIN

Explain from informix online 7 for the two queries
--------------------------------------------------

QUERY:
------
select accountdetail.domain from accountdetail where
accountdetail.domain not in (select accountmaster.domain from accountmaster)

Estimated Cost: 8995
Estimated # of Rows Returned: 47652

1) informix.accounts: SEQUENTIAL SCAN

Filters: (informix.accounts.domain != ALL <subquery> AND informix.accounts.registrationtype != 'N' )

Subquery:
---------
Estimated Cost: 4497
Estimated # of Rows Returned: 5883

1) informix.accounts: SEQUENTIAL SCAN

Filters: informix.accounts.registrationtype = 'N'

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'

Timing from postgres 6.5.3 for the two queries
----------------------------------------------
explain select accountdetail.domain from accountdetail where
accountdetail.domain not in
(select accountmaster.domain from accountmaster);

Greater than 5 hours and 30 minutes

explain select accountdetail.domain from accountdetail
where not exists (
select accountmaster.domain from accountmaster where
accountmaster.domain = accountdetail.domain);

0.00user 0.01system 0:04.75elapsed 0%CPU (0avgtext+0avgdata 0maxresident)k

Timing from Informix Online 7 for the two queries
----------------------------------------------
explain select accountdetail.domain from accountdetail where
accountdetail.domain not in
(select accountmaster.domain from accountmaster);

0.03user 0.01system 0:10.35elapsed 0%CPU (0avgtext+0avgdata 0maxresident)k

explain select accountdetail.domain from accountdetail
where not exists (
select accountmaster.domain from accountmaster where
accountmaster.domain = accountdetail.domain);

0.03user 0.00system 0:03.56elapsed 0%CPU (0avgtext+0avgdata 0maxresident)k

The machine is a Pentium II 400 MHz with Fast Wide SCSI and is the same
for both Informix and Postgres. Informix uses Linux I/O ie. it does not
use a raw partition. The datasets are the same.

Regards
Theo