Skip site navigation (1) Skip section navigation (2)

Re: BUG #5294: Sorts on more than just the order-by clause

From: Allen Johnson <akjohnson78(at)gmail(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: BUG #5294: Sorts on more than just the order-by clause
Date: 2010-01-21 21:27:57
Message-ID: 6786ed4f1001211327v1bd65dffpeb921658cdb4c22f@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-bugs
> In practice, I really doubt this would make a measurable performance
> difference, since most row comparisons would arrive at a result before
> they got to the lowest-order columns.
>
> I think your gripe may actually have to do with a misestimate of the
> relative costs of hash- and sort-based grouping, but analyze results
> on a toy example don't illuminate that sort of problem at all :-(

Yes, this toy example doesn't show how much time was spent on the
actual sorting (of the production data, obviously). What I can do is
assemble a test database with similar amount of data and repost the
`explain analyze` from that if there is any interest.

What I noticed in the production query was that ~1000ms was spent on
sorting alone. The hack query reduced that to ~400ms. I should also
note that there was plenty of work_mem and that the sort was not
hitting disk.

I should be able to get that going sometime early tomorrow. All I'm
going to do is generate a lot of contacts by randomly choosing from a
set of lastnames, firstnames, etc as well as randomly insert some
number of attachments for each.

I'm open to any suggestions on testing methodologies.

AJ

In response to

Responses

pgsql-bugs by date

Next:From: Craig RingerDate: 2010-01-22 03:30:25
Subject: Re: add primary key doesn't block?
Previous:From: Tom LaneDate: 2010-01-21 21:00:06
Subject: Re: BUG #5294: Sorts on more than just the order-by clause

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group