Re: Sorting performance vs. MySQL

There is no index on the column transactionid in your PostgreSQL-
table, as there is in your MySQL-table. This explains the difference.

CREATE INDEX i_transactionid ON public.metarelcloud_transactionlog
(transactionid);


Op 22 feb 2010, om 19:10 heeft Yang Zhang het volgende geschreven:

> 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@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general

Frank Heikens
frankheikens@mac.com