From: | Mike Benoit <mikeb(at)netnation(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | pgsql-hackers(at)postgreSQL(dot)org |
Subject: | Re: Thinking about IN/EXISTS optimization |
Date: | 2002-11-29 22:34:32 |
Message-ID: | 1038609272.2919.28.camel@mikeb.staff.netnation.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
On Fri, 2002-11-29 at 13:22, Tom Lane wrote:
> Mike Benoit <mikeb(at)netnation(dot)com> 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
From | Date | Subject | |
---|---|---|---|
Next Message | Daniele Orlandi | 2002-11-29 23:06:46 | Re: 7.4 Wishlist |
Previous Message | Matthew T. O'Connor | 2002-11-29 22:29:32 | Re: 7.4 Wishlist |