Re: oracle to psql migration - slow query in postgres - Mailing list pgsql-performance

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@prospectiv.com]
> > Sent: Friday, October 15, 2010 2:14 PM
> > To: pgsql-performance@postgresql.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
>