Re: Progress on fast path sorting, btree index creation time - Mailing list pgsql-hackers

On 6 January 2012 17:29, Robert Haas <robertmhaas@gmail.com> wrote:
> On Fri, Jan 6, 2012 at 12:10 PM, Peter Geoghegan <peter@2ndquadrant.com> wrote:
>> As you know, all queries tested have lots and lots of duplicates (a
>> ~1.5GB table that contains the same number of distinct elements as a
>> ~10MB table once did), and removing the "duplicate protection" for
>> btrees, on top of everything else, sees the qsort do an awful lot
>> better than HEAD does with the psuedo-protection.
>
> Does that also win vs. unpatched master?  If so we may as well pull
> that part out and commit it separately.

I didn't bother isolating that, because it doesn't really make sense
to (not having it is probably only of particular value when doing what
I'm doing anyway, but who knows). Go ahead and commit something to
remove that code (get it in both comparetup_index_btree and
comparetup_index_hash), as well as the tuple1 != tuple2 test now if
you like. It's patently clear that it is unnecessary, and so doesn't
have to be justified as a performance enhancement - it's a simple case
of refactoring for clarity. As I've said, we don't do this for heap
tuples and we've heard no complaints in all that time. It was added in
commit fbac1272b89b547dbaacd78bbe8da68e5493cbda, presumably when
problems with system qsorts came to light.

>> I'd imagined that it might work a bit like default_statistics_target.
>> Of course, I was just thinking out loud. Also, we do a very good job
>> on *perfectly* pre-sorted input because we check for pre-sorted input.
>
> We occasionally have people who want to do SELECT * FROM foo WHERE a >
> 100 AND a < 200 ORDER BY a, b where foo has an index on (a) but not
> (a, b).  This tends to suck, because we fail to realize that we can
> batch the sort.  We either seqscan and filter the table then sort the
> results, or else we scan the index on (a) and then ignore the fact
> that we have data which is already partially sorted.  It's
> particularly annoying if a is "mostly unique" and needs b only
> occasionally to break ties.  It would be nice to improve this, but it
> may be more of a planner/executor problem that something we can fix
> directly inside the sort implementation.

That sounds like an interesting problem. Something to look into for
9.3, perhaps.

--
Peter Geoghegan       http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training and Services