Re: oracle to psql migration - slow query in postgres

On Thu, Oct 14, 2010 at 3:43 PM, Tony Capobianco
<tcapobianco(at)prospectiv(dot)com> wrote:
> explain analyze create table tmp_srcmem_emws1
> as
> select emailaddress, websiteid
>  from members
>  where emailok = 1
>   and emailbounced = 0;

*) as others have noted, none of your indexes will back this
expression. For an index to match properly the index must have all
the fields matched in the 'where' clause in left to right order. you
could rearrange indexes you already have and probably get things to
work properly.

*) If you want things to go really fast, and the combination of
emailok, emailbounced is a small percentage (say, less than 5) in the
table, and you are not interested in the schema level changes your
table is screaming, and the (1,0) combination is what you want to
frequently match and you should consider:

create function email_interesting(ok numeric, bounced numeric) returns bool as
$$
select $1 = 1 and $2 = 0;
$$ language sql immutable;

create function members_email_interesting_idx on
members(email_interesting(emailok, emailbounced)) where email_interesting();

This will build a partial index which you can query via:
select emailaddress, websiteid
from members
where email_interesting(emailok, emailbounced);

merlin