Re: [HACKERS] Slow - grindingly slow - query

What does:

explain select domain from accountdetail
where domain not in (
select domain from accountmaster);

show?

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

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*

If I'm reading your select properly, and with the amount of sleep I've had
recently, its possible I'm not...

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?

On Thu, 11 Nov 1999, Theo Kramer wrote:

> 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 from accountmaster);
>
> 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.domain from 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=33373 width=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).
>
> ************
>

Marc G. Fournier ICQ#7615664 IRC Nick: Scrappy
Systems Administrator @ hub.org
primary: scrappy(at)hub(dot)org secondary: scrappy(at){freebsd|postgresql}.org