Hi
I have a single table with two views. The table effectively contains both
master and detail info (legacy stuff I'm afraid). The query in question is
used to see if any records exist in the detail that do not exist in the
master. The table and index definition is as follows
create table accounts ( domain text, registrationtype char /* Plus a couple of other irrelevant fields */ );
create index domain_idx on accounts (domain); create index domain_type_idx on accounts (domain, registrationtype);
The views are
create view accountmaster as SELECT * from accounts where registrationtype =
'N'; create view accountdetail as SELECT * from accounts where registrationtype <>
'N';
The query is
select accountdetail.domain from accountdetail where accountdetail.domain not in (select accountmaster.domain
fromaccountmaster);
I started the query about 5 hours ago and it is still running. I did the same
on Informix Online 7 and it took less than two minutes...
My system details are postgres: 6.5.3 O/S: RH6.0 Kernel 2.2.5-15smp
Explain shows the following
explain select accountdetail.domain from accountdetail where accountdetail.domain not in (select
accountmaster.domainfrom accountmaster) limit 10; NOTICE: QUERY PLAN:
Seq Scan on accounts (cost=3667.89 rows=34958 width=12) SubPlan -> Seq Scan on accounts (cost=3667.89
rows=33373width=12)
EXPLAIN
The number of records in the two views are
psql -c "select count(*) from accountmaster" coza; count ----- 45527 (1 row)
psql -c "select count(*) from accountdetail" coza; count ----- 22803
I know of exactly one record (I put it there myself) that satisfies the
selection criteria.
Any ideas would be appreciated
--------
Regards
Theo
PS We have it running live at http://co.za (commercial domains in South Africa).