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

Frank Heikens
frankheikens(at)mac(dot)com