Re: [HACKERS] Slow - grindingly slow - query - Mailing list pgsql-hackers

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