Re: oracle to psql migration - slow query in postgres

On 10/14/10 21:43, Tony Capobianco 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;
>                                                            QUERY
> PLAN
> ------------------------------------------------------------------------------------------------------------------------------
>   Seq Scan on members  (cost=0.00..14137154.64 rows=238177981 width=29)
> (actual time=0.052..685834.785 rows=236660930 loops=1)
>     Filter: ((emailok = 1::numeric) AND (emailbounced = 0::numeric))
>   Total runtime: 850306.220 ms
> (3 rows)

> Indexes:
>      "email_website_unq" UNIQUE, btree (emailaddress, websiteid),
> tablespace "members_idx"
>      "member_addeddateid_idx" btree (addeddate_id), tablespace
> "members_idx"
>      "member_changedateid_idx" btree (changedate_id), tablespace
> "members_idx"
>      "members_fdate_idx" btree (to_char_year_month(addeddate)),
> tablespace "esave_idx"
>      "members_memberid_idx" btree (memberid), tablespace "members_idx"
>      "members_mid_emailok_idx" btree (memberid, emailaddress, zipcode,
> firstname, emailok), tablespace "members_idx"
>      "members_sorted_idx_001" btree (websiteid, emailok, emailbounced,
> addeddate, memberid, zipcode, statecode, emailaddress), tablespace
> "members_idx"
>      "members_src_idx" btree (websiteid, emailbounced, sourceid),
> tablespace "members_idx"
>      "members_wid_idx" btree (websiteid), tablespace "members_idx"

PostgreSQL doesn't fetch data directly from indexes, so there is no way 
for it to reasonably use an index declared like:

"members_sorted_idx_001" btree (websiteid, emailok, emailbounced, 
addeddate, memberid, zipcode, statecode, emailaddress)

You need a direct index on the fields you are using in your query, i.e. 
an index on (emailok, emailbounced).

OTOH, those columns look boolean-like. It depends on what your data set 
is, but if the majority of records contain (emailok=1 and 
emailbounced=0) an index may not help you much.