Very true Igor! Free is my favorite price. I'll figure a way around this issue. Thanks for your help. Tony On Fri, 2010-10-15 at 14:54 -0400, Igor Neyman wrote: > > -----Original Message----- > > From: Tony Capobianco [mailto:tcapobianco@xxxxxxxxxxxxxx] > > Sent: Friday, October 15, 2010 2:14 PM > > To: pgsql-performance@xxxxxxxxxxxxxx > > Subject: Re: oracle to psql migration - slow query in postgres > > > > 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. > > > > > > 1. Postgres doesn't have "FAST FULL SCAN" because even if all the info > is in the index, it need to visit the row in the table ("visibility" > issue). > > 2. Postgres doesn't have parallel executions. > > BUT, it's free anf has greate community support, as you already saw. > > 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
