Re: oracle to psql migration - slow query in postgres

> -----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