Re: General key issues when comparing performance between PostgreSQL and oracle

Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com> writes:
> On Tue, Jul 16, 2013 at 10:51 AM, Brian Fehrle
> <brianf(at)consistentstate(dot)com> wrote:
>> I'm working on performance tuning a host of queries on PostgreSQL 9.2 from
>> an application, each query having its own issues and fixes, however from
>> what I understand this application runs the exact same queries on the exact
>> same data in half the time on oracle and SQL server.
>>
>> It's worth noting that the queries are not that good, they have issues with
>> bad sub-selects, Cartesian products, and what looks like bad query design in
>> general, so the blame isn't completely with the database being slow, but I
>> wonder what makes oracle preform better when given not-so-great queries?

> Fact is that if the query can be made better, then you get to do that.

Another point worth making here: we often find that ugly-looking queries
got that way by being hand-optimized to exploit implementation
peculiarities of whichever DBMS they were being run on before. This is
a particularly nasty form of vendor lock-in, especially if you're
dealing with legacy code whose current custodians don't remember having
done such hacking on the queries.

I'm not necessarily claiming that your particular cases meet that
description, since you're saying that the queries perform well on both
Oracle and SQL Server. But without details it's hard to judge. It
seems most likely from here that your first problem is a badly tuned
Postgres installation.

regards, tom lane