Thread
-
Slow - grindingly slow - query
Theo Kramer <theo@flame.co.za> — 1999-11-11T19:41:09Z
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). -
Re: [HACKERS] Slow - grindingly slow - query
Marc G. Fournier <scrappy@hub.org> — 1999-11-11T20:33:47Z
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 -
Re: [HACKERS] Slow - grindingly slow - query
Hannu Krosing <hannu@tm.ee> — 1999-11-11T20:41:31Z
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); try using create index registrationtype_index on accounts (registrationtype); ------ Hannu -
Re: [HACKERS] Slow - grindingly slow - query
Theo Kramer <theo@flame.co.za> — 1999-11-11T20:50:14Z
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 -
Re: [HACKERS] Slow - grindingly slow - query
Theo Kramer <theo@flame.co.za> — 1999-11-11T21:10:20Z
Hannu Krosing wrote: > try using > create index registrationtype_index on accounts (registrationtype); OK did that, and am rerunning the query. The explain now shows explain select accountdetail.domain from accountdetail where accountdetail.domain not in (select accountmaster.domain from accountmaster); NOTICE: QUERY PLAN: Seq Scan on accounts (cost=3667.89 rows=34958 width=12) SubPlan -> Index Scan using registrationtype_idx on accounts (cost=2444.62 rows=33373 width=12) EXPLAIN Will let you all know when it completes. -------- Regards Theo