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.
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
