Re: [HACKERS] Slow count(*) again...

On Fri, Feb 4, 2011 at 6:05 AM, Grant Johnson <grant(at)amadensor(dot)com> wrote:
>
>> Yes.  And this has little to do with hints.  It has to do with years
>> of development lead with THOUSANDS of engineers who can work on the
>> most esoteric corner cases in their spare time.  Find the pg project a
>> couple hundred software engineers and maybe we'll catch Oracle a
>> little quicker.  Otherwise we'll have to marshall our resources to do
>> the best we can on the project ,and that means avoiding maintenance
>> black holes and having the devs work on the things that give the most
>> benefit for the cost.  Hints are something only a tiny percentage of
>> users could actually use and use well.
>>
>> Write a check, hire some developers and get the code done and present
>> it to the community.  If it's good and works it'll likely get
>> accepted.  Or use EDB, since it has oracle compatibility in it.
>>
> I have to disagree with you here.   I have never seen Oracle outperform
> PostgreSQL on complex joins, which is where the planner comes in.  Perhaps
> on certain throughput things, but this is likely do to how we handle dead
> rows, and counts, which is definitely because of how dead rows are handled,
> but the easier maintenance makes up for those.  Also both of those are by a
> small percentage.
>
> I have many times had Oracle queries that never finish (OK maybe not never,
> but not over a long weekend) on large hardware, but can be finished on
> PostgreSQL in a matter or minutes on cheap hardware.   This happens to the
> point that often I have set up a PostgreSQL database to copy the data to for
> querying and runnign the complex reports, even though the origin of the data
> was Oracle, since the application was Oracle specific.   It took less time
> to duplicate the database and run the query on PostgreSQL than it did to
> just run it on Oracle.

It very much depends on the query. With lots of tables to join, and
with pg 8.1 which is what I used when we were running Oracle 9, Oracle
won. With fewer tables to join in an otherwise complex reporting
query PostgreSQL won. I did the exact thing you're talking about. I
actually wrote a simple replication system fro Oracle to PostgreSQL
(it was allowed to be imperfect because it was stats data and we could
recreate at a moment).

PostgreSQL on a PIV workstation with 2G ram and 4 SATA drives in
RAID-10 stomped Oracle on much bigger Sun hardware into the ground for
reporting queries. Queries that ran for hours or didn't finish in
Oracle ran in 5 to 30 minutes on the pg box.

But not all queries were like that.