Re: oracle to psql migration - slow query in postgres

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(at)prospectiv(dot)com]
> > Sent: Friday, October 15, 2010 2:14 PM
> > To: pgsql-performance(at)postgresql(dot)org
> > 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
>