Thanks for all your responses. What's interesting is that an index is
used when this query is executed in Oracle. It appears to do some
parallel processing:
SQL> set line 200
delete from plan_table;
explain plan for
select websiteid, emailaddress
from members
where emailok = 1
and emailbounced = 0;
SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());
SQL>
3 rows deleted.
SQL> 2 3 4 5
Explained.
SQL> SQL>
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 4247959398
-------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes
| Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
-------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 237M|
7248M| 469K (2)| 01:49:33 | | | |
| 1 | PX COORDINATOR | | |
| | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10000 | 237M|
7248M| 469K (2)| 01:49:33 | Q1,00 | P->S | QC (RAND) |
| 3 | PX BLOCK ITERATOR | | 237M|
7248M| 469K (2)| 01:49:33 | Q1,00 | PCWC | |
|* 4 | INDEX FAST FULL SCAN| MEMBERS_SORTED_IDX_001 | 237M|
7248M| 469K (2)| 01:49:33 | Q1,00 | PCWP | |
-------------------------------------------------------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("EMAILBOUNCED"=0 AND "EMAILOK"=1)
16 rows selected.
On Fri, 2010-10-15 at 13:43 -0400, Igor Neyman wrote:
>
> > -----Original Message-----
> > From: Tony Capobianco [mailto:tcapobianco@xxxxxxxxxxxxxx]
> > Sent: Thursday, October 14, 2010 3:43 PM
> > To: pgsql-performance@xxxxxxxxxxxxxx
> > Subject: oracle to psql migration - slow query in postgres
> >
> > 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
> >
> >
>
>
> Tony,
> For your query:
>
> > select
> > emailaddress, websiteid
> > from members
> > where emailok = 1
> > and emailbounced = 0;
>
> your table doesn't have any indexes where "emailok" or "emailbounced"
> are leading columns.
> That's why existing indexes can not be used.
>
> If you specified "websiteid" in the "where" clause then (most probably)
> the index members_sorted_idx_001 will be used (based on selectivity and
> statistics known to optimizer).
>
> If this query (as is - without "websiteid") is important for your app,
> create another index on (emailok, emailbounced) which should help, of
> course if selectivity of your where clause is good enough (not to
> perform full table scan).
>
> Regards,
> Igor Neyman
>
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
