From: | Cédric Villemain <cedric(dot)villemain(dot)debian(at)gmail(dot)com> |
---|---|
To: | Tony Capobianco <tcapobianco(at)prospectiv(dot)com> |
Cc: | pgsql-performance(at)postgresql(dot)org |
Subject: | Re: oracle to psql migration - slow query in postgres |
Date: | 2010-10-14 21:25:41 |
Message-ID: | AANLkTikgXPx+jMKtkObvBdnek++xw=OL0LqmHiW0tVbN@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin pgsql-performance |
2010/10/14 Tony Capobianco <tcapobianco(at)prospectiv(dot)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(at)postgresql(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Guillaume Lelarge | 2010-10-14 22:39:51 | Re: How to find the primary server of a hot standby server? |
Previous Message | Rudolf van der Leeden | 2010-10-14 21:18:10 | How to find the primary server of a hot standby server? |
From | Date | Subject | |
---|---|---|---|
Next Message | Pierre C | 2010-10-14 22:50:20 | Re: oracle to psql migration - slow query in postgres |
Previous Message | Ivan Voras | 2010-10-14 20:32:09 | Re: oracle to psql migration - slow query in postgres |