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