Re: Sorting performance vs. MySQL

On Mon, Feb 22, 2010 at 11:10, Yang Zhang  wrote:
> I have the exact same table of data in both MySQL and Postgresql. In ?> Postgresql:

FWIW on a stock (unchanged postgresql.conf) 8.3.9 I get (best of 3
runs) 79 seconds, 26 using an index and 27 seconds with it clustered.
Now yes it goes a lot faster because im skipping all the overhead of
sending the data to the client...  But still that means it has almost
nothing with the sort or indexs.  Leaves pretty much your cpu, disk,
filesystem and network...  BTW the first time I did it it had to write
out the hint bits so that took (a bit) longer... Is this freshly
loaded data?

---

# create table metarelcould_transactionlog (
        id serial primary key,
        transactionid integer not null,
        queryid smallint not null,
        tableid varchar(30) not null,
        tupleid integer not null,
        querytype varchar not null,
        graphpartition smallint,
        replicatedpartition smallint,
        justifiedpartition smallint,
        hashpartition smallint,
        modeid integer,
        manualpartition smallint
);

# insert into metarelcould_transactionlog (transactionid, queryid,
tableid, tupleid, querytype, graphpartition, replicatedpartition,
justifiedpartition, hashpartition, modeid, manualpartition) select
foo,  1, 'sometable', 1, 's', 1, 1, 1, 1, 1, 1 from generate_series(1,
50000000) as foo;

# select count(1) from (SELECT * from metarelcould_transactionlog
order by transactionid) as foo;
  count
----------
 50000000
(1 row)

Time: 79017.186 ms

# create index idx on metarelcould_transactionlog (transactionid);
# select count(1) from (SELECT * from metarelcould_transactionlog
order by transactionid) as foo;
  count
----------
 50000000
(1 row)

Time: 26230.534 ms

# cluster metarelcould_transactionlog USING  idx;
CLUSTER
Time: 342381.535 ms

# select count(1) from (SELECT * from metarelcould_transactionlog
order by transactionid) as foo;
  count
----------
 50000000
(1 row)

Time: 27704.794 ms