Re: Performance cost of a sort-merge join

Yang Zhang <yanghatespam@gmail.com> writes:
> Hi, I have the given tables and query, but I'm confused why the cost
> of the query is so high.

The reason the estimated cost is so high is that the estimated number of
rows out of the join is enormous.  It's going to take awhile.  One
question worth asking is what you've got work_mem set to.  Another
possible problem is that you're doing a lot of sorts/comparisons on
varchars, which could be pretty expensive if you're using a non-C locale.

> I've left it running over night. By
> comparison, a "select * from metarelcloud_transactionlog order by
> transactionid" takes on the order of seconds/minutes (at least in
> MySQL).

That's got approximately nothing to do with this query.

            regards, tom lane