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

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@hub.org           secondary: scrappy@{freebsd|postgresql}.org