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