Thread
-
Re: [HACKERS] Slow - grindingly slow - query
Tom Lane <tgl@sss.pgh.pa.us> — 1999-11-11T21:56:36Z
Theo Kramer <theo@flame.co.za> writes: > The query is > select accountdetail.domain from accountdetail where > accountdetail.domain not in > (select accountmaster.domain from accountmaster); Try something like select accountdetail.domain from accountdetail where not exists (select accountmaster.domain from accountmaster where accountmaster.domain = accountdetail.domain); I believe this is in the FAQ... regards, tom lane -
Re: [HACKERS] Slow - grindingly slow - query
Theo Kramer <theo@flame.co.za> — 1999-11-12T05:09:15Z
Tom Lane wrote: > > Theo Kramer <theo@flame.co.za> writes: > > The query is > > > select accountdetail.domain from accountdetail where > > accountdetail.domain not in > > (select accountmaster.domain from accountmaster); This takes more than 5 hours and 30 minutes. > Try something like > > select accountdetail.domain from accountdetail where > not exists (select accountmaster.domain from accountmaster where > accountmaster.domain = accountdetail.domain); This takes 5 seconds - wow! > I believe this is in the FAQ... Will check out the FAQs. Many thanks. -------- Regards Theo
-
Re: [HACKERS] Slow - grindingly slow - query
Vadim Mikheev <vadim@krs.ru> — 1999-11-12T05:52:29Z
Theo Kramer wrote: > > > Try something like > > > > select accountdetail.domain from accountdetail where > > not exists (select accountmaster.domain from accountmaster where > > accountmaster.domain = accountdetail.domain); > > This takes 5 seconds - wow! > I did the same on Informix Online 7 and it took less than two minutes... ^^^^^^^^^^^ Could you run the query above in Informix? How long would it take to complete? Vadim -
Re: [HACKERS] Slow - grindingly slow - query
Theo Kramer <theo@flame.flame.co.za> — 1999-11-12T08:04:58Z
Vadim wrote: > > I did the same on Informix Online 7 and it took less than two minutes... > > Could you run the query above in Informix? > How long would it take to complete? I include both explain and timing for the queries for both postgres and Informix. Explain from postgres for the two queries. ------------------------------------------ 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 explain select accountdetail.domain from accountdetail where not exists ( select accountmaster.domain from accountmaster where accountmaster.domain = accountdetail.domain); NOTICE: QUERY PLAN: Seq Scan on accounts (cost=3667.89 rows=34958 width=12) SubPlan -> Index Scan using domain_type_idx on accounts (cost=2.04 rows=1 width=12) EXPLAIN Explain from informix online 7 for the two queries -------------------------------------------------- QUERY: ------ select accountdetail.domain from accountdetail where accountdetail.domain not in (select accountmaster.domain from accountmaster) Estimated Cost: 8995 Estimated # of Rows Returned: 47652 1) informix.accounts: SEQUENTIAL SCAN Filters: (informix.accounts.domain != ALL <subquery> AND informix.accounts.registrationtype != 'N' ) Subquery: --------- Estimated Cost: 4497 Estimated # of Rows Returned: 5883 1) informix.accounts: SEQUENTIAL SCAN Filters: informix.accounts.registrationtype = 'N' QUERY: ------ select accountdetail.domain from accountdetail where accountdetail.domain not in (select accountmaster.domain from accountmaster) Estimated Cost: 4510 Estimated # of Rows Returned: 58810 1) informix.accounts: SEQUENTIAL SCAN Filters: (informix.accounts.domain != ALL <subquery> AND informix.accounts.registrationtype != 'N' ) Subquery: --------- Estimated Cost: 12 Estimated # of Rows Returned: 10 1) informix.accounts: INDEX PATH (1) Index Keys: registrationtype Lower Index Filter: informix.accounts.registrationtype = 'N' Timing from postgres 6.5.3 for the two queries ---------------------------------------------- explain select accountdetail.domain from accountdetail where accountdetail.domain not in (select accountmaster.domain from accountmaster); Greater than 5 hours and 30 minutes explain select accountdetail.domain from accountdetail where not exists ( select accountmaster.domain from accountmaster where accountmaster.domain = accountdetail.domain); 0.00user 0.01system 0:04.75elapsed 0%CPU (0avgtext+0avgdata 0maxresident)k Timing from Informix Online 7 for the two queries ---------------------------------------------- explain select accountdetail.domain from accountdetail where accountdetail.domain not in (select accountmaster.domain from accountmaster); 0.03user 0.01system 0:10.35elapsed 0%CPU (0avgtext+0avgdata 0maxresident)k explain select accountdetail.domain from accountdetail where not exists ( select accountmaster.domain from accountmaster where accountmaster.domain = accountdetail.domain); 0.03user 0.00system 0:03.56elapsed 0%CPU (0avgtext+0avgdata 0maxresident)k The machine is a Pentium II 400 MHz with Fast Wide SCSI and is the same for both Informix and Postgres. Informix uses Linux I/O ie. it does not use a raw partition. The datasets are the same. Regards Theo -
Re: [HACKERS] Slow - grindingly slow - query
Brian Hirt <bhirt@mobygames.com> — 1999-11-12T09:49:01Z
> > > select accountdetail.domain from accountdetail where > > > accountdetail.domain not in > > > (select accountmaster.domain from accountmaster); > > This takes more than 5 hours and 30 minutes. > > > select accountdetail.domain from accountdetail where > > not exists (select accountmaster.domain from accountmaster where > > accountmaster.domain = accountdetail.domain); > > This takes 5 seconds - wow! > I have a general comment/question here. Why do in/not in clauses seem to perform so slowly? I've noticed this type of behavior with with my system also. I think the above queries will always return the exact same results regardless of the data. From looking at the query plan with explain, it's clear the second query makes better use of the indexes. Can't the rewrite engine recognize a simple case like the one above and rewrite it to use exists and not exists with the proper joins? Or possibly the optimizer can generate a better plan? Sometimes it's not so easy to just change a query in the code. Sometimes you can't change the code because you only have executables and sometimes you are using a tool that automatically generates SQL using in clauses. Additionally, since intersect and union get rewritten as in clauses they suffer the same performance problems. -brian -- The world's most ambitious and comprehensive PC game database project. http://www.mobygames.com -
Re: [HACKERS] Slow - grindingly slow - query
Tom Lane <tgl@sss.pgh.pa.us> — 1999-11-12T14:58:14Z
Brian Hirt <bhirt@mobygames.com> writes: > Can't the rewrite engine recognize a simple case like the > one above and rewrite it to use exists and not exists with the proper > joins? Or possibly the optimizer can generate a better plan? This is on the TODO list, and will get done someday. IMHO it's not as urgent as a lot of the planner/optimizer's other shortcomings, because it can usually be worked around by revising the query. If it's bugging you enough to go fix it now, contributions are always welcome ;-) regards, tom lane
-
Re: [HACKERS] Slow - grindingly slow - query
Brian Hirt <bhirt@mobygames.com> — 1999-11-12T18:02:07Z
On Fri, Nov 12, 1999 at 09:58:14AM -0500, Tom Lane wrote: > Brian Hirt <bhirt@mobygames.com> writes: > > Can't the rewrite engine recognize a simple case like the > > one above and rewrite it to use exists and not exists with the proper > > joins? Or possibly the optimizer can generate a better plan? > > This is on the TODO list, and will get done someday. IMHO it's not as > urgent as a lot of the planner/optimizer's other shortcomings, because > it can usually be worked around by revising the query. > > If it's bugging you enough to go fix it now, contributions are always > welcome ;-) > Okay, what would be the correct approach to solving the problem, and where would be a good place to start? I'v only been on this list for a few weeks, so I'm missed discussion on the approach to solving this problem. Should this change be localized to just the planner? Should the rewrite system be creating a different query tree? Will both need to be changed? If a lot of work is being done to this part of the system, is now a bad time to try this work? I'm willing to jump in to this, but I may take a while to figure it out and ask a lot of questions that are obvious to the hardened postgres programmer. I'm not famaliar with the postgres code, yet. -brian -- The world's most ambitious and comprehensive PC game database project. http://www.mobygames.com -
replication
Aaron J. Seigo <aaron@gtv.ca> — 1999-11-16T16:26:50Z
hi... is anyone working on replication services in pgsql? -- Aaron J. Seigo Sys Admin