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