Re: oracle to psql migration - slow query in postgres

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

 



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


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

  Powered by Linux