Re: Thinking about IN/EXISTS optimization

Mike Benoit <mikeb@netnation.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?

> 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?
        regards, tom lane