Sorting performance vs. MySQL

[Yang Zhang <yanghatespam@xxxxxxxxx>]

I have the exact same table of data in both MySQL and Postgresql. In Postgresql:

tpcc=# \d metarelcloud_transactionlog
                                       Table
"public.metarelcloud_transactionlog"
       Column        |         Type          |
       Modifiers
---------------------+-----------------------+--------------------------------------------------------------------------
 id                  | integer               | not null default
nextval('metarelcloud_transactionlog_id_seq'::regclass)
 transactionid       | integer               | not null
 queryid             | smallint              | not null
 tableid             | character varying(30) | not null
 tupleid             | integer               | not null
 querytype           | character varying     | not null
 graphpartition      | smallint              |
 replicatedpartition | smallint              |
 justifiedpartition  | smallint              |
 hashpartition       | smallint              |
 nodeid              | integer               |
 manualpartition     | smallint              |
Indexes:
    "metarelcloud_transactionlog_pkey" PRIMARY KEY, btree (id)
Check constraints:
    "metarelcloud_transactionlog_graphpartition_check" CHECK
(graphpartition >= 0)
    "metarelcloud_transactionlog_hashpartition_check" CHECK (hashpartition >= 0)
    "metarelcloud_transactionlog_justifiedpartition_check" CHECK
(justifiedpartition >= 0)
    "metarelcloud_transactionlog_manualpartition_check" CHECK
(manualpartition >= 0)
    "metarelcloud_transactionlog_querytype_check" CHECK
(querytype::text = ANY (ARRAY['select'::character varying,
'insert'::character varying, 'delete'::character varying,
'update'::character varying]::text[]))
    "metarelcloud_transactionlog_replicatedpartition_check" CHECK
(replicatedpartition >= 0)

In MySQL:

CREATE TABLE `metarelcloud_transactionlog` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `transactionid` int(11) NOT NULL,
  `queryid` tinyint(4) NOT NULL,
  `tableid` varchar(30) NOT NULL,
  `tupleid` int(11) NOT NULL,
  `querytype` enum('select','insert','delete','update') NOT NULL,
  `graphpartition` tinyint(3) unsigned DEFAULT NULL,
  `replicatedpartition` tinyint(3) unsigned DEFAULT NULL,
  `justifiedpartition` tinyint(3) unsigned DEFAULT NULL,
  `hashpartition` tinyint(3) unsigned DEFAULT NULL,
  `nodeid` int(11) DEFAULT NULL,
  `manualpartition` tinyint(3) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `transactionid` (`transactionid`),
  KEY `tableid` (`tableid`,`tupleid`),
  KEY `nodeid` (`nodeid`)
) ENGINE=MyISAM AUTO_INCREMENT=50410166 DEFAULT CHARSET=latin1

I'm running:

  select * from metarelcloud_transactionlog order by transactionid;

It takes MySQL 6 minutes, but Postgresql is still running after 70
minutes. Is there something like a glaring misconfiguration that I'm
overlooking? Thanks in advance.
--
Yang Zhang
http://www.mit.edu/~y_z/

-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general