From: | Ivan Voras <ivoras(at)freebsd(dot)org> |
---|---|
To: | pgsql-performance(at)postgresql(dot)org |
Subject: | Re: oracle to psql migration - slow query in postgres |
Date: | 2010-10-14 20:32:09 |
Message-ID: | i97pca$f1h$1@dough.gmane.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin pgsql-performance |
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.
From | Date | Subject | |
---|---|---|---|
Next Message | Rudolf van der Leeden | 2010-10-14 21:18:10 | How to find the primary server of a hot standby server? |
Previous Message | bricklen | 2010-10-14 20:23:12 | Re: oracle to psql migration - slow query in postgres |
From | Date | Subject | |
---|---|---|---|
Next Message | Cédric Villemain | 2010-10-14 21:25:41 | Re: oracle to psql migration - slow query in postgres |
Previous Message | bricklen | 2010-10-14 20:23:12 | Re: oracle to psql migration - slow query in postgres |