Re: [HACKERS] Slow - grindingly slow - query

The Hermit Hacker wrote:
> 
> What does:
> 
> explain select domain from accountdetail
>         where domain not in (
>                 select domain from accountmaster);
> 
> show?

NOTICE:  QUERY PLAN:

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

EXPLAIN


> Also, did you do a 'vacuum analyze' on the tables?

Yes - should have mentioned that.
 
> Also, how about if you get rid of the views
> 
> SELECT domain FROM account
> WHERE registrationtype <> 'N';
> 
> *shakes head*  am I missing something here?  I'm reading your SELECT and
> 'CREATE VIEW's and don't they negate each other? *scratch head*

No - a domain can both be new (registrationtype 'N') and updated 
(registrationtype 'U') ie. one or more rows with the same domain with one row
containing a domain with registrationtype 'N' and zero or more rows containing
the same domain with registrationtype not 'N'. The reason for the <> 'N' and 
not just = 'U' is that we have a couple of rows with registrationtype set to
something else.
 
> The subselect is saying give me all domains whose registration type = 'N'.
> The select itself is saying give me all domains whoe registration type <>
> 'N' (select accountdetail.domain from accountdetail), and narrow that
> listing down further to only include those domains whose registration type
> <> 'N'?
> 
> Either I'm reading this *totally* wrong, or you satisfy that condition
> ujust by doing a 'SELECT domain FROM accountdetail;' ...
> 
> No?

No :). See above

--------
Regards
Theo