pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Tony Capobianco <tcapobianco@prospectiv.com>
To: pgsql-performance@postgresql.org
Subject: Re: oracle to psql migration - slow query in postgres
Date: Fri, 15 Oct 2010 15:22:52 -0400
Message-ID: <1287170572.1730.173.camel@tony1.localdomain> (raw)
In-Reply-To: <F4C27E77F7A33E4CA98C19A9DC6722A2069F34DE@EXCHANGE.corp.perceptron.com>
References: <1287085384.1684.52.camel@tony1.localdomain>
	<F4C27E77F7A33E4CA98C19A9DC6722A2069F3459@EXCHANGE.corp.perceptron.com>
	<1287166429.1730.170.camel@tony1.localdomain>
	<F4C27E77F7A33E4CA98C19A9DC6722A2069F34DE@EXCHANGE.corp.perceptron.com>

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
> 





view thread (16+ messages)

Message-ID: <1287170572.1730.173.camel@tony1.localdomain>
Permalink:  ../1287170572.1730.173.camel@tony1.localdomain/
Also on:    postgresql.org/message-id/1287170572.1730.173.camel@tony1.localdomain

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-admin@postgresql.org
  Cc: tcapobianco@prospectiv.com, pgsql-performance@postgresql.org
  Subject: Re: oracle to psql migration - slow query in postgres
  In-Reply-To: <1287170572.1730.173.camel@tony1.localdomain>
  References: <1287085384.1684.52.camel@tony1.localdomain> <F4C27E77F7A33E4CA98C19A9DC6722A2069F3459@EXCHANGE.corp.perceptron.com> <1287166429.1730.170.camel@tony1.localdomain> <F4C27E77F7A33E4CA98C19A9DC6722A2069F34DE@EXCHANGE.corp.perceptron.com> <1287170572.1730.173.camel@tony1.localdomain>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox