Re: oracle to psql migration - slow query in postgres

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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


[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux