On Fri, 2002-11-29 at 13:22, Tom Lane wrote: > Mike Benoit <[EMAIL PROTECTED]> writes: > > I'm just curious, will your proposed in/exists optimizations help for > > queries like: > > > db=# explain delete from dns_expired_domains where domain_id in (select > > domain_id from dns_expired_domains group by domain_id having count(*)=14 > > ); > > Probably, but I'm more than a tad curious about why you're concerned > about the efficiency of this particular example. Why would "count=14" > be an interesting condition for deleting groups?
The count=14 isn't really that significate, basically I'm just looking for faster execution of queries like: (delete|select) from table where id in (select id from large_table2) For cases where EXISTS won't work properly, and large_table2 has more then ~50,000 rows. > > > Seq Scan on dns_expired_domains (cost=0.00..55448724329.92 rows=324754 > > width=6) > > SubPlan > > -> Materialize (cost=85370.33..85370.33 rows=64951 width=4) > > -> Aggregate (cost=82122.79..85370.33 rows=64951 width=4) > > -> Group (cost=82122.79..83746.56 rows=649508 width=4) > > -> Sort (cost=82122.79..82122.79 rows=649508 > > width=4) > > -> Seq Scan on dns_expired_domains > > (cost=0.00..10316.08 rows=649508 width=4) > > What are the *actual*, not estimated, row counts here --- ie, how many > rows in the table, and how many distinct domain_ids are you typically > deleting? 650,000 actual rows in the table. 40,000 or so are returned by the subquery. About 500,000 rows should end up being deleted. > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html -- Best Regards, Mike Benoit NetNation Communication Inc. Systems Engineer Tel: 604-684-6892 or 888-983-6600 --------------------------------------- Disclaimer: Opinions expressed here are my own and not necessarily those of my employer ---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster