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
copy link · copy postgr.es
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