pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedGeneral key issues when comparing performance between PostgreSQL and oracle
4+ messages / 4 participants
[nested] [flat]
* General key issues when comparing performance between PostgreSQL and oracle
@ 2013-07-16 16:51 Brian Fehrle <brianf@consistentstate.com>
0 siblings, 2 replies; 4+ messages in thread
From: Brian Fehrle @ 2013-07-16 16:51 UTC (permalink / raw)
To: pgsql-performance
Hi all (Hopefully this is the correct mailing list for this).
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.
Are there any known differences between the database systems in terms of
query planners or general operations (sorting, unions) that are notable
different between the systems that would make postgres slow down when
executing the exact same queries?
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?
I know this is rather general and high level, but any tips or experience
anyone has would be appreciated.
Thanks,
- Brian F
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: General key issues when comparing performance between PostgreSQL and oracle
@ 2013-07-17 03:01 Samuel Gendler <sgendler@ideasculptor.com>
parent: Brian Fehrle <brianf@consistentstate.com>
1 sibling, 0 replies; 4+ messages in thread
From: Samuel Gendler @ 2013-07-17 03:01 UTC (permalink / raw)
To: Brian Fehrle <brianf@consistentstate.com>; +Cc: pgsql-performance
On Tue, Jul 16, 2013 at 9:51 AM, Brian Fehrle <brianf@consistentstate.com>wrote:
>
> Are there any known differences between the database systems in terms of
> query planners or general operations (sorting, unions) that are notable
> different between the systems that would make postgres slow down when
> executing the exact same queries?
>
>
yes.
But unless you provide more detail, it's impossible to say what those
differences might be. In all probability, postgresql can be configured to
provide comparable performance, but without details, who can really say?
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: General key issues when comparing performance between PostgreSQL and oracle
@ 2013-07-17 04:14 Scott Marlowe <scott.marlowe@gmail.com>
parent: Brian Fehrle <brianf@consistentstate.com>
1 sibling, 1 reply; 4+ messages in thread
From: Scott Marlowe @ 2013-07-17 04:14 UTC (permalink / raw)
To: Brian Fehrle <brianf@consistentstate.com>; +Cc: pgsql-performance
On Tue, Jul 16, 2013 at 10:51 AM, Brian Fehrle
<brianf@consistentstate.com> wrote:
> Hi all (Hopefully this is the correct mailing list for this).
>
> 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.
>
> Are there any known differences between the database systems in terms of
> query planners or general operations (sorting, unions) that are notable
> different between the systems that would make postgres slow down when
> executing the exact same queries?
>
> 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?
>
> I know this is rather general and high level, but any tips or experience
> anyone has would be appreciated.
Here's the thing. The Postgres team is small, and they have to choose
wisely what to work on. So, do they work on making everything else
better, faster and more reliable, or do they dedicate precious man
hours to making bad queries run well?
Fact is that if the query can be made better, then you get to do that.
After optimizing the query, if you find a corner case where
postgresql's query planner is making a bad decision or where some new
db method would make it faster then it's time to appeal to the
community to see what they can do. If you don't have the time to fix
bad queries, then you might want to stick to Oracle or MSSQL server.
OR spend the money you'd spend on those on a postgres hacker to see
what they can do for you.
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: General key issues when comparing performance between PostgreSQL and oracle
@ 2013-07-17 04:51 Tom Lane <tgl@sss.pgh.pa.us>
parent: Scott Marlowe <scott.marlowe@gmail.com>
0 siblings, 0 replies; 4+ messages in thread
From: Tom Lane @ 2013-07-17 04:51 UTC (permalink / raw)
To: Scott Marlowe <scott.marlowe@gmail.com>; +Cc: Brian Fehrle <brianf@consistentstate.com>; pgsql-performance
Scott Marlowe <scott.marlowe@gmail.com> writes:
> On Tue, Jul 16, 2013 at 10:51 AM, Brian Fehrle
> <brianf@consistentstate.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
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2013-07-17 04:51 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2013-07-16 16:51 General key issues when comparing performance between PostgreSQL and oracle Brian Fehrle <brianf@consistentstate.com>
2013-07-17 03:01 ` Samuel Gendler <sgendler@ideasculptor.com>
2013-07-17 04:14 ` Scott Marlowe <scott.marlowe@gmail.com>
2013-07-17 04:51 ` Tom Lane <tgl@sss.pgh.pa.us>
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