Re: oracle to psql migration - slow query in postgres

On Thu, Oct 14, 2010 at 12:43 PM, Tony Capobianco
<tcapobianco(at)prospectiv(dot)com> wrote:
> We have 4 quad-core processors and 32GB of RAM.  The below query uses
> the members_sorted_idx_001 index in oracle, but in postgres, the
> optimizer chooses a sequential scan.
>
> explain analyze create table tmp_srcmem_emws1
> as
> select emailaddress, websiteid
>  from members
>  where emailok = 1
>   and emailbounced = 0;

Maybe a couple indexes to try:

create index members_emailok_emailbounced_idx on members (emailok,emailbounced);

or a functional index (will likely be smaller, depending on the
contents of your table):
create index members_emailok_emailbounced_idx on members
(emailok,emailbounced) where emailok = 1 and emailbounced = 0; -- if
you use that combination of 1 and 0 regularly