From: | Yang Zhang <yanghatespam(at)gmail(dot)com> |
---|---|
To: | Alban Hertroys <dalroi(at)solfertje(dot)student(dot)utwente(dot)nl> |
Cc: | Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>, pgsql-general(at)postgresql(dot)org |
Subject: | Re: Sorting performance vs. MySQL |
Date: | 2010-02-22 19:07:21 |
Message-ID: | 9066fa251002221107x39450bb8h55b5bdc06ae48e59@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Mon, Feb 22, 2010 at 1:48 PM, Alban Hertroys
<dalroi(at)solfertje(dot)student(dot)utwente(dot)nl> wrote:
> On 22 Feb 2010, at 19:35, Yang Zhang wrote:
>
>> I also wouldn't have imagined an external merge-sort as being very
>
>
> Where's that external merge-sort coming from? Can you show an explain analyze?
I just assumed that the "Sort" in the EXPLAIN output meant an external
merge-sort, given that the table has over 50 million tuples and is
over 3GB, *and* there is no index on the sort key:
tpcc=# explain select * from metarelcloud_transactionlog order by transactionid;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------
Sort (cost=8408637.34..8534662.95 rows=50410244 width=17)
Sort Key: a.transactionid
-> Seq Scan on metarelcloud_transactionlog a
(cost=0.00..925543.44 rows=50410244 width=17)
(3 rows)
Anyway, I added the INDEX as suggested by Frank, but it's been 20
minutes and it's still running. With the index, EXPLAIN says:
tpcc=# explain select * from metarelcloud_transactionlog order by transactionid;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------
Index Scan using i_transactionid on metarelcloud_transactionlog
(cost=0.00..4453076.81 rows=50410164 width=44)
(1 row)
> If your work-mem is too low there's a good chance that Postgres has to use your disks for sorting, which will obviously be quite slow.
Relative to the non-terminating 80-minute-so-far sort, Unix sort runs
much faster (on the order of several minutes).
--
Yang Zhang
http://www.mit.edu/~y_z/
From | Date | Subject | |
---|---|---|---|
Next Message | Yang Zhang | 2010-02-22 19:08:24 | Re: Sorting performance vs. MySQL |
Previous Message | Alvaro Herrera | 2010-02-22 19:03:43 | Re: Sorting performance vs. MySQL |