Re: oracle to psql migration - slow query in postgres - Mailing list pgsql-performance

2010/10/14 Tony Capobianco <tcapobianco@prospectiv.com>:
> We are in the process of testing migration of our oracle data warehouse
> over to postgres.  A potential showstopper are full table scans on our
> members table.  We can't function on postgres effectively unless index
> scans are employed.  I'm thinking I don't have something set correctly
> in my postgresql.conf file, but I'm not sure what.
>
> This table has approximately 300million rows.

and your query grab rows=236 660 930 of them. An index might be
useless in this situation.

>
> Version:
> SELECT version();
>
> version
> ------------------------------------------------------------------------------------------------------------------
>  PostgreSQL 8.4.2 on x86_64-redhat-linux-gnu, compiled by GCC gcc (GCC)
> 4.1.2 20071124 (Red Hat 4.1.2-42), 64-bit
>
> 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)
>
> show shared_buffers ;
>  shared_buffers
> ----------------
>  7680MB
> (1 row)
>
> show effective_cache_size ;
>  effective_cache_size
> ----------------------
>  22GB
> (1 row)
>
> show work_mem ;
>  work_mem
> ----------
>  768MB
> (1 row)
>
> show enable_seqscan ;
>  enable_seqscan
> ----------------
>  on
> (1 row)
>
> Below are the data definitions for the table/indexes in question:
>
> \d members
>                     Table "members"
>       Column        |            Type             | Modifiers
> ---------------------+-----------------------------+-----------
>  memberid            | numeric                     | not null
>  firstname           | character varying(50)       |
>  lastname            | character varying(50)       |
>  emailaddress        | character varying(50)       |
>  password            | character varying(50)       |
>  address1            | character varying(50)       |
>  address2            | character varying(50)       |
>  city                | character varying(50)       |
>  statecode           | character varying(50)       |
>  zipcode             | character varying(50)       |
>  birthdate           | date                        |
>  emailok             | numeric(2,0)                |
>  gender              | character varying(1)        |
>  addeddate           | timestamp without time zone |
>  emailbounced        | numeric(2,0)                |
>  changedate          | timestamp without time zone |
>  optoutsource        | character varying(100)      |
>  websiteid           | numeric                     |
>  promotionid         | numeric                     |
>  sourceid            | numeric                     |
>  siteid              | character varying(64)       |
>  srcwebsiteid        | numeric                     |
>  homephone           | character varying(20)       |
>  homeareacode        | character varying(10)       |
>  campaignid          | numeric                     |
>  srcmemberid         | numeric                     |
>  optoutdate          | date                        |
>  regcomplete         | numeric(1,0)                |
>  regcompletesourceid | numeric                     |
>  ipaddress           | character varying(25)       |
>  pageid              | numeric                     |
>  streetaddressstatus | numeric(1,0)                |
>  middlename          | character varying(50)       |
>  optinprechecked     | numeric(1,0)                |
>  optinposition       | numeric                     |
>  homephonestatus     | numeric                     |
>  addeddate_id        | numeric                     |
>  changedate_id       | numeric                     |
>  rpmindex            | numeric                     |
>  optmode             | numeric(1,0)                |
>  countryid           | numeric                     |
>  confirmoptin        | numeric(2,0)                |
>  bouncedate          | date                        |
>  memberageid         | numeric                     |
>  sourceid2           | numeric                     |
>  remoteuserid        | character varying(50)       |
>  goal                | numeric(1,0)                |
>  flowdepth           | numeric                     |
>  pagetype            | numeric                     |
>  savepassword        | character varying(50)       |
>  customerprofileid   | numeric                     |
> 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"
>
> select tablename, indexname, tablespace, indexdef from pg_indexes where
> tablename = 'members';
> -[ RECORD
> 1
]----------------------------------------------------------------------------------------------------------------------------------------------------
> tablename  | members
> indexname  | members_fdate_idx
> tablespace | esave_idx
> indexdef   | CREATE INDEX members_fdate_idx ON members USING btree
> (to_char_year_month(addeddate))
> -[ RECORD
> 2
]----------------------------------------------------------------------------------------------------------------------------------------------------
> tablename  | members
> indexname  | member_changedateid_idx
> tablespace | members_idx
> indexdef   | CREATE INDEX member_changedateid_idx ON members USING btree
> (changedate_id)
> -[ RECORD
> 3
]----------------------------------------------------------------------------------------------------------------------------------------------------
> tablename  | members
> indexname  | member_addeddateid_idx
> tablespace | members_idx
> indexdef   | CREATE INDEX member_addeddateid_idx ON members USING btree
> (addeddate_id)
> -[ RECORD
> 4
]----------------------------------------------------------------------------------------------------------------------------------------------------
> tablename  | members
> indexname  | members_wid_idx
> tablespace | members_idx
> indexdef   | CREATE INDEX members_wid_idx ON members USING btree
> (websiteid)
> -[ RECORD
> 5
]----------------------------------------------------------------------------------------------------------------------------------------------------
> tablename  | members
> indexname  | members_src_idx
> tablespace | members_idx
> indexdef   | CREATE INDEX members_src_idx ON members USING btree
> (websiteid, emailbounced, sourceid)
> -[ RECORD
> 6
]----------------------------------------------------------------------------------------------------------------------------------------------------
> tablename  | members
> indexname  | members_sorted_idx_001
> tablespace | members_idx
> indexdef   | CREATE INDEX members_sorted_idx_001 ON members USING btree
> (websiteid, emailok, emailbounced, addeddate, memberid, zipcode,
> statecode, emailaddress)
> -[ RECORD
> 7
]----------------------------------------------------------------------------------------------------------------------------------------------------
> tablename  | members
> indexname  | members_mid_emailok_idx
> tablespace | members_idx
> indexdef   | CREATE INDEX members_mid_emailok_idx ON members USING btree
> (memberid, emailaddress, zipcode, firstname, emailok)
> -[ RECORD
> 8
]----------------------------------------------------------------------------------------------------------------------------------------------------
> tablename  | members
> indexname  | members_memberid_idx
> tablespace | members_idx
> indexdef   | CREATE INDEX members_memberid_idx ON members USING btree
> (memberid)
> -[ RECORD
> 9
]----------------------------------------------------------------------------------------------------------------------------------------------------
> tablename  | members
> indexname  | email_website_unq
> tablespace | members_idx
> indexdef   | CREATE UNIQUE INDEX email_website_unq ON members USING
> btree (emailaddress, websiteid)
>
>
> This table has also been vacuumed analyzed as well:
>
> select * from pg_stat_all_tables where relname = 'members';
> -[ RECORD 1 ]----+------------------------------
> relid            | 3112786
> schemaname       | xxxxx
> relname          | members
> seq_scan         | 298
> seq_tup_read     | 42791828896
> idx_scan         | 31396925
> idx_tup_fetch    | 1083796963
> n_tup_ins        | 291308316
> n_tup_upd        | 0
> n_tup_del        | 4188020
> n_tup_hot_upd    | 0
> n_live_tup       | 285364632
> n_dead_tup       | 109658
> last_vacuum      | 2010-10-12 20:26:01.227393-04
> last_autovacuum  |
> last_analyze     | 2010-10-12 20:28:01.105656-04
> last_autoanalyze | 2010-09-16 20:50:00.712418-04
>
>
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>



--
Cédric Villemain               2ndQuadrant
http://2ndQuadrant.fr/     PostgreSQL : Expertise, Formation et Support