Re: PostgreSQL vs oracle doing 1 million sqrts am I doing it wrong?

Hi

2014-08-09 10:20 GMT+02:00 Guillaume Lelarge <guillaume(at)lelarge(dot)info>:

> Hi,
>
> Le 9 août 2014 05:57, "Ramirez, Danilo" <Danilo(dot)Ramirez(at)hmhco(dot)com> a
> écrit :
> >
> > Thanks to all for the great info. We are new to postgresql and this
> discussion has both instructed us and increased our respect for the
> database and the community.
> >
> > I am seeing a behavior that I don’t understand and hopefully you guys
> can clear it up.
> >
> > I am using AWS postgresql db.m3.2xlarge and using pgadmin III 1.18
> comparing against AWS oracle on db.m3.2xlarge using sql developer and TOAD.
> >
> > I am running a query with 30 tables in the from clause, getting 137
> columns back (this is our most basic query, they get a lot more more
> complex). It returns back 4800 rows.
> >
> > In oracle 1st run takes 3.92 seconds, 2nd .38 seconds. Scrolling to end
> takes and extra 1.5 seconds for total of 5.5.
> >
> > Using pgadmin, I run the query. Looking at the lower right hand I can
> see the time going up. It stops at 8200 ms or close to it every time, then
> it takes an extra 6 seconds before it displays the rows on the screen.
> 2nd, 3rd, etc. runs all take about same amount of time 8 sec plus 6 sec
> >
> > I then changed it to return only 1 column back. In oracle/sqldeveloper
> identical behavior as before, same time. In postgresql it now goes down to
> 1.8 seconds for 1st, 2nd, etc. runs.
> >
> > I then change it so that I am asking for the sum of 1 column. In oracle
> time goes down to .2 seconds and postgresql now goes down to .2 seconds
> also.
> >
> > I then change it back to get the full result set and behavior goes back
> to original, oracle .38 since its cached, postgresql 8 seconds.
> >
>
> Are you sure this is postgresql 8 seconds? I'd believe this is more
> something like postgresql something really low and PgAdmin around 8 seconds
> displaying it. What I mean is, PgAdmin uses really slow UI components and
> the time it shows is the time to execute the query and display the data.
> IOW, you shouldn't use it to benchmark. You should better use psql. Or,
> much better, you should set log_min_duration_statement to 0 and see exactly
> how much time postgresql needs to execute it.
>
yes, try to eliminate a impact of PgAdmin

for this purpose use psql

\timing
\o /dev/null
SELECT ... -- your query

Regards

Pavel

p.s. you can send a plans of slow and fast variants.