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.
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
