Re: oracle to psql migration - slow query in postgres

Just my take on this.

The first thing I'd do is think real hard about whether you really
really want 'numeric' instead of boolean, smallint, or integer. The
second thing is that none of your indices (which specify a whole bunch
of fields, by the way) have only just emailok, emailbounced, or only
the pair of them. Without knowing the needs of your app, I would
reconsider your index choices and go with fewer columns per index.

For this particular query I would think either two indexes (depending
on the cardinality of the data, one for each of emailok, emailbounced)
or one index (containing both emailok, emailbounced) would make quite
a bit of difference. Consider creating the indexes using a WITH
clause, for example:

CREATE INDEX members_just_an_example_idx ON members (emailok,
emailbounced) WHERE emailok = 1 AND emailbounced = 0;

Obviously that index is only useful in situations where both fields
are specified with those values. Furthermore, if the result is such
that a very high percentage of the table has those conditions a
sequential scan is going to be cheaper, anyway.

--
Jon