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:tcapobia...@prospectiv.com]
> > Sent: Friday, October 15, 2010 2:14
> -Original Message-
> From: Tony Capobianco [mailto:tcapobia...@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
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 emailboun
> -Original Message-
> From: Tony Capobianco [mailto:tcapobia...@prospectiv.com]
> Sent: Thursday, October 14, 2010 3:43 PM
> To: pgsql-performance@postgresql.org
> Subject: oracle to psql migration - slow query in postgres
>
> We are in the process of testing migration of our oracle d
>> This table has approximately 300million rows.
>
> and your query grab rows=236 660 930 of them. An index might be
> useless in this situation.
I want to point out that this is probably the most important comment
here. A couple of people have noted out that the index won't work for
this query, b
The recommendations on the numeric columns are fantastic. Thank you
very much. We will revisit our methods of assigning datatypes when we
migrate our data over from Oracle.
Regarding the full table scans; it appears inevitable that full table
scans are necessary for the volume of data involved an
On Thu, Oct 14, 2010 at 3:43 PM, Tony Capobianco
wrote:
> explain analyze create table tmp_srcmem_emws1
> as
> select emailaddress, websiteid
> from members
> where emailok = 1
> and emailbounced = 0;
*) as others have noted, none of your indexes will back this
expression. For an index to ma
Samuel Gendler wrote:
On Thu, Oct 14, 2010 at 8:59 PM, Mladen Gogala
mailto:mladen.gog...@vmsinfo.com>> wrote:
If working with partitioning, be very aware that PostgreSQL
optimizer has certain problems with partitions, especially with
group functions. If you want speed, everythi
On Thu, Oct 14, 2010 at 8:59 PM, Mladen Gogala wrote:
> If working with partitioning, be very aware that PostgreSQL optimizer has
> certain problems with partitions, especially with group functions. If you
> want speed, everything must be prefixed with partitioning column: indexes,
> expressions,
On 10/14/2010 4:10 PM, Jon Nelson wrote:
The first thing I'd do is think real hard about whether you really
really want 'numeric' instead of boolean, smallint, or integer. The
second thing is that none of your indices (which specify a whole bunch
of fields, by the way) have only just emailok, e
Just my take on this.
The first thing I'd do is think real hard about whether you really
really want 'numeric' instead of boolean, smallint, or integer. The
second thing is that none of your indices (which specify a whole bunch
of fields, by the way) have only just emailok, emailbounced, or only
emailok | numeric(2,0)|
Note that NUMERIC is meant for
- really large numbers with lots of digits
- or controlled precision and rounding (ie, order total isn't
99. $)
Accordingly, NUMERIC is a lot slower in all operations, and uses a lot
more spac
2010/10/14 Tony Capobianco :
> We are in the process of testing migration of our oracle data warehouse
> over to postgres. A potential showstopper are full table scans on our
> members table. We can't function on postgres effectively unless index
> scans are employed. I'm thinking I don't have s
On 10/14/10 21:43, Tony Capobianco wrote:
We have 4 quad-core processors and 32GB of RAM. The below query uses
the members_sorted_idx_001 index in oracle, but in postgres, the
optimizer chooses a sequential scan.
explain analyze create table tmp_srcmem_emws1
as
select emailaddress, websiteid
On Thu, Oct 14, 2010 at 12:43 PM, Tony Capobianco
wrote:
> We have 4 quad-core processors and 32GB of RAM. The below query uses
> the members_sorted_idx_001 index in oracle, but in postgres, the
> optimizer chooses a sequential scan.
>
> explain analyze create table tmp_srcmem_emws1
> as
> select
We are in the process of testing migration of our oracle data warehouse
over to postgres. A potential showstopper are full table scans on our
members table. We can't function on postgres effectively unless index
scans are employed. I'm thinking I don't have something set correctly
in my postgres
16 matches
Mail list logo
