pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Gary Cowell <gary_cowell@yahoo.co.uk>
To: pgsql-performance@postgresql.org
Subject: Re: Major differences between oracle and postgres performance - what can I do ?
Date: Fri, 18 Jun 2004 16:47:21 +0100 (BST)
Message-ID: <20040618154721.37105.qmail@web25105.mail.ukl.yahoo.com> (raw)

> Try increasing sort_mem temporarily, and see if that
> makes a difference:
>    SET sort_mem = 64000;
>    EXPLAIN ANALYSE ...

I did this (actualy 65536) and got the following:
pvcsdb=# explain analyze select distinct version from
vers where version is not null;
                                                      
      QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------
 Unique  (cost=117865.77..120574.48 rows=142
width=132) (actual time=81595.178..86573.228 rows=536
loops=1)
   ->  Sort  (cost=117865.77..119220.13 rows=541741
width=132) (actual time=81595.169..84412.069
rows=541741 loops=1)
         Sort Key: "version"
         ->  Seq Scan on vers  (cost=0.00..21367.41
rows=541741 width=132) (actual time=10.068..7397.374
rows=541741 loops=1)
               Filter: ("version" IS NOT NULL)
 Total runtime: 86647.495 ms
(6 rows)


In response to Tom Lane, I have compared a
select/order by on the same data in Oracle and PG to
see if this changes things:


PG:     Time: 67438.536 ms   541741 rows
Oracle: After an hour and a half I canned it

So it seems the idea that oracle is dropping duplicate
rows prior to the sort when using distinct may indeed
be the case.



view thread (18+ messages)  latest in thread

Message-ID: <20040618154721.37105.qmail@web25105.mail.ukl.yahoo.com>
Permalink:  ../20040618154721.37105.qmail@web25105.mail.ukl.yahoo.com/
Also on:    postgresql.org/message-id/20040618154721.37105.qmail@web25105.mail.ukl.yahoo.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-performance@postgresql.org
  Cc: gary_cowell@yahoo.co.uk
  Subject: Re: Major differences between oracle and postgres performance - what can I do ?
  In-Reply-To: <20040618154721.37105.qmail@web25105.mail.ukl.yahoo.com>
  References: <20040618154721.37105.qmail@web25105.mail.ukl.yahoo.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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