From: Ivan Voras <ivoras@freebsd.org>
To: pgsql-performance@postgresql.org
Subject: Re: oracle to psql migration - slow query in postgres
Date: Thu, 14 Oct 2010 22:32:09 +0200
Message-ID: <i97pca$f1h$1@dough.gmane.org> (raw)
In-Reply-To: <1287085384.1684.52.camel@tony1.localdomain>
References: <1287085384.1684.52.camel@tony1.localdomain>
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: ivoras@freebsd.org
Subject: Re: oracle to psql migration - slow query in postgres
In-Reply-To: <i97pca$f1h$1@dough.gmane.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox