Re: [HACKERS] PoC: Partial sort

2017-01-30 Thread Michael Paquier
On Mon, Dec 5, 2016 at 2:04 PM, Haribabu Kommi wrote: > > > On Fri, Dec 2, 2016 at 4:05 AM, Robert Haas wrote: >> >> On Tue, Sep 13, 2016 at 4:32 AM, Alexander Korotkov >> wrote: >> > On Fri, Apr 8, 2016 at 10:09 PM, Peter

Re: [HACKERS] PoC: Partial sort

2016-12-04 Thread Haribabu Kommi
On Fri, Dec 2, 2016 at 4:05 AM, Robert Haas wrote: > On Tue, Sep 13, 2016 at 4:32 AM, Alexander Korotkov > wrote: > > On Fri, Apr 8, 2016 at 10:09 PM, Peter Geoghegan wrote: > >> On Wed, Mar 30, 2016 at 8:02 AM, Alexander Korotkov >

Re: [HACKERS] PoC: Partial sort

2016-12-01 Thread Peter Geoghegan
On Mon, Nov 21, 2016 at 11:04 PM, Haribabu Kommi wrote: > you assigned as reviewer to the current patch in the 11-2016 commitfest. > But you haven't shared your review yet in this commitfest on the latest > patch posted by the author. If you don't have any comments on

Re: [HACKERS] PoC: Partial sort

2016-12-01 Thread Robert Haas
On Tue, Sep 13, 2016 at 4:32 AM, Alexander Korotkov wrote: > On Fri, Apr 8, 2016 at 10:09 PM, Peter Geoghegan wrote: >> On Wed, Mar 30, 2016 at 8:02 AM, Alexander Korotkov >> wrote: >> > Hmm... I'm not completely agree with that. In

Re: [HACKERS] PoC: Partial sort

2016-11-21 Thread Haribabu Kommi
Hi Peter, This is a gentle reminder. you assigned as reviewer to the current patch in the 11-2016 commitfest. But you haven't shared your review yet in this commitfest on the latest patch posted by the author. If you don't have any comments on the patch, please move the patch into "ready for

Re: [HACKERS] PoC: Partial sort

2016-10-02 Thread Michael Paquier
On Tue, Sep 13, 2016 at 5:32 PM, Alexander Korotkov wrote: > On Fri, Apr 8, 2016 at 10:09 PM, Peter Geoghegan wrote: >> >> On Wed, Mar 30, 2016 at 8:02 AM, Alexander Korotkov >> wrote: >> > Hmm... I'm not completely agree with that.

Re: [HACKERS] PoC: Partial sort

2016-09-13 Thread Alexander Korotkov
On Fri, Apr 8, 2016 at 10:09 PM, Peter Geoghegan wrote: > On Wed, Mar 30, 2016 at 8:02 AM, Alexander Korotkov > wrote: > > Hmm... I'm not completely agree with that. In typical usage partial sort > > should definitely use quicksort. However, fallback to

Re: [HACKERS] PoC: Partial sort

2016-04-08 Thread Peter Geoghegan
On Wed, Mar 30, 2016 at 8:02 AM, Alexander Korotkov wrote: > Hmm... I'm not completely agree with that. In typical usage partial sort > should definitely use quicksort. However, fallback to other sort methods is > very useful. Decision of partial sort usage is made by

Re: [HACKERS] PoC: Partial sort

2016-03-30 Thread Alexander Korotkov
Hi, Peter! Thank you for review! On Thu, Mar 24, 2016 at 3:39 AM, Peter Geoghegan wrote: > >> Sort Method > >> > >> > >> Even thought the explain analyze above shows "top-N heapsort" as its > >> sort method, that isn't really true. I actually ran this through

Re: [HACKERS] PoC: Partial sort

2016-03-29 Thread Alexander Korotkov
On Tue, Mar 29, 2016 at 4:56 PM, David Steele wrote: > On 3/23/16 8:39 PM, Peter Geoghegan wrote: > > This looks like an old change you missed: >> >> - * compare_path_fractional_costs >> + * compare_fractional_path_costs >> >> All in all, this looks significantly better.

Re: [HACKERS] PoC: Partial sort

2016-03-29 Thread David Steele
Hi Alexander, On 3/23/16 8:39 PM, Peter Geoghegan wrote: This looks like an old change you missed: - * compare_path_fractional_costs + * compare_fractional_path_costs All in all, this looks significantly better. Thanks for your work on this. Sorry for the delay in my response, and that my

Re: [HACKERS] PoC: Partial sort

2016-03-23 Thread Peter Geoghegan
Hi, On Tue, Mar 1, 2016 at 7:06 AM, Alexander Korotkov wrote: > I finally went over your review. I'll respond to your points here. Note that I'm reviewing "partial-sort-basic-7.patch", which you sent on March 13. I respond here because this is where you answered my

Re: [HACKERS] PoC: Partial sort

2016-03-13 Thread Alexander Korotkov
Hi! Tom committed upper planner pathification patch. Partial sort patch rebased to master is attached. It was quite huge rebase in planner part of the patch. But I think now patch becomes better, much more logical. It's probably, something was missed after rebase. I'm going to examine this

Re: [HACKERS] PoC: Partial sort

2016-02-15 Thread Peter Geoghegan
On Sun, Jan 31, 2016 at 4:16 AM, Alvaro Herrera wrote: > Great to have a new version -- there seems to be a lot of interest in > this patch. I'm moving this one to the next commitfest, thanks. I am signed up to review this patch. I was very surprised to see it in

Re: [HACKERS] PoC: Partial sort

2016-01-31 Thread Alvaro Herrera
Alexander Korotkov wrote: > I'm sorry that I didn't found time for this yet. I'm certainly planning to > get back to this in near future. The attached version is just rebased > without any optimization. Great to have a new version -- there seems to be a lot of interest in this patch. I'm

Re: [HACKERS] PoC: Partial sort

2016-01-24 Thread Alexander Korotkov
Hi, Tomas! On Sat, Jan 23, 2016 at 3:07 PM, Tomas Vondra wrote: > On 10/20/2015 01:17 PM, Alexander Korotkov wrote: > >> On Fri, Oct 16, 2015 at 7:11 PM, Alexander Korotkov >> > wrote: >> >> On Sun, Jun 7, 2015

Re: [HACKERS] PoC: Partial sort

2016-01-24 Thread Alexander Korotkov
Hi! On Sat, Jan 23, 2016 at 10:07 PM, Peter Geoghegan wrote: > On Sat, Jan 23, 2016 at 4:07 AM, Tomas Vondra > wrote: > > The main thing I'm particularly interested in is how much is this coupled > > with the Sort node, and whether it's possible

Re: [HACKERS] PoC: Partial sort

2016-01-23 Thread Tomas Vondra
Hi, On 10/20/2015 01:17 PM, Alexander Korotkov wrote: On Fri, Oct 16, 2015 at 7:11 PM, Alexander Korotkov > wrote: On Sun, Jun 7, 2015 at 11:01 PM, Peter Geoghegan > wrote: On Sun, Jun

Re: [HACKERS] PoC: Partial sort

2016-01-23 Thread Peter Geoghegan
On Sat, Jan 23, 2016 at 4:07 AM, Tomas Vondra wrote: > The main thing I'm particularly interested in is how much is this coupled > with the Sort node, and whether it's possible to feed partially sorted > tuples into other nodes. That's cool, but I'm particularly

Re: [HACKERS] PoC: Partial sort

2015-11-03 Thread Peter Geoghegan
On Tue, Oct 20, 2015 at 4:17 AM, Alexander Korotkov wrote: > Planner regression is fixed in the attached version of patch. It appears > that get_cheapest_fractional_path_for_pathkeys() behaved wrong when no > ordering is required. I took a look at this. My remarks are not

Re: [HACKERS] PoC: Partial sort

2015-10-29 Thread Peter Geoghegan
On Tue, Oct 20, 2015 at 4:17 AM, Alexander Korotkov wrote: > Planner regression is fixed in the attached version of patch. It appears > that get_cheapest_fractional_path_for_pathkeys() behaved wrong when no > ordering is required. I don't see an entry in the CF app for

Re: [HACKERS] PoC: Partial sort

2015-10-20 Thread Alexander Korotkov
On Fri, Oct 16, 2015 at 7:11 PM, Alexander Korotkov wrote: > On Sun, Jun 7, 2015 at 11:01 PM, Peter Geoghegan wrote: > >> On Sun, Jun 7, 2015 at 8:10 AM, Andreas Karlsson >> wrote: >> > Are you planning to work on this patch for 9.6? >>

Re: [HACKERS] PoC: Partial sort

2015-10-16 Thread Alexander Korotkov
On Sun, Jun 7, 2015 at 11:01 PM, Peter Geoghegan wrote: > On Sun, Jun 7, 2015 at 8:10 AM, Andreas Karlsson > wrote: > > Are you planning to work on this patch for 9.6? > > FWIW I hope so. It's a nice patch. > I'm trying to to whisk dust. Rebased version of

Re: [HACKERS] PoC: Partial sort

2015-06-07 Thread Peter Geoghegan
On Sun, Jun 7, 2015 at 8:10 AM, Andreas Karlsson andr...@proxel.se wrote: Are you planning to work on this patch for 9.6? FWIW I hope so. It's a nice patch. -- Peter Geoghegan -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription:

Re: [HACKERS] PoC: Partial sort

2015-06-07 Thread Andreas Karlsson
On 09/15/2014 01:58 PM, Alexander Korotkov wrote: On Sun, Sep 14, 2014 at 9:32 AM, Peter Geoghegan p...@heroku.com mailto:p...@heroku.com wrote: I think we might be better off if a tuplesort function was called shortly after tuplesort_begin_heap() is called. How top-n heap sorts

Re: [HACKERS] PoC: Partial sort

2014-09-15 Thread Alexander Korotkov
On Sun, Sep 14, 2014 at 7:39 AM, Peter Geoghegan p...@heroku.com wrote: On Fri, Sep 12, 2014 at 2:19 PM, Alexander Korotkov aekorot...@gmail.com wrote: Actually, higher cardinality skip columns is better. Sorting of smaller groups is faster than sorting larger groups of same size. Also,

Re: [HACKERS] PoC: Partial sort

2014-09-15 Thread Alexander Korotkov
On Sun, Sep 14, 2014 at 9:32 AM, Peter Geoghegan p...@heroku.com wrote: I think we might be better off if a tuplesort function was called shortly after tuplesort_begin_heap() is called. How top-n heap sorts work is something that largely lives in tuplesort's head. Today, we call

Re: [HACKERS] PoC: Partial sort

2014-09-13 Thread Alexander Korotkov
On Tue, Aug 19, 2014 at 2:02 PM, David Rowley dgrowle...@gmail.com wrote: Here's a few notes from reading over the code: * pathkeys.c EquivalenceMember *member = (EquivalenceMember *) lfirst(list_head(key-pk_eclass-ec_members)); You can use linitial() instead of lfirst(list_head()).

Re: [HACKERS] PoC: Partial sort

2014-09-13 Thread Peter Geoghegan
On Fri, Sep 12, 2014 at 2:19 PM, Alexander Korotkov aekorot...@gmail.com wrote: Actually, higher cardinality skip columns is better. Sorting of smaller groups is faster than sorting larger groups of same size. Also, with smaller groups you achieve limit more accurate (in average), i.e. sort

Re: [HACKERS] PoC: Partial sort

2014-09-13 Thread Peter Geoghegan
Some quick comments on partial-sort-basic-2.patch: *** a/src/include/utils/tuplesort.h --- b/src/include/utils/tuplesort.h *** *** 24,29 --- 24,30 #include executor/tuptable.h #include fmgr.h #include utils/relcache.h + #include utils/sortsupport.h Why

Re: [HACKERS] PoC: Partial sort

2014-09-12 Thread Alexander Korotkov
On Sun, Jul 13, 2014 at 6:45 AM, Peter Geoghegan p...@heroku.com wrote: On Mon, Feb 10, 2014 at 10:59 AM, Alexander Korotkov aekorot...@gmail.com wrote: Done. Patch is splitted. I took a quick look at this. Have you thought about making your new cmpSortSkipCols() function not use real

Re: [HACKERS] PoC: Partial sort

2014-08-19 Thread David Rowley
On Tue, Feb 11, 2014 at 7:59 AM, Alexander Korotkov aekorot...@gmail.com wrote: Done. Patch is splitted. I've started to look at this, and for now I'm still finding my way around the patch, so I'm not quite there yet with understanding everything. Never-the-less it seems best to post my

Re: [HACKERS] PoC: Partial sort

2014-07-12 Thread Peter Geoghegan
On Mon, Feb 10, 2014 at 10:59 AM, Alexander Korotkov aekorot...@gmail.com wrote: Done. Patch is splitted. I took a quick look at this. Have you thought about making your new cmpSortSkipCols() function not use real comparisons? Since in the circumstances in which this optimization is expected to

Re: [HACKERS] PoC: Partial sort

2014-02-24 Thread Robert Haas
On Wed, Feb 19, 2014 at 1:39 PM, Marti Raudsepp ma...@juffo.org wrote: On Wed, Feb 12, 2014 at 11:54 PM, Marti Raudsepp ma...@juffo.org wrote: With partial-sort-basic-1 and this fix on the same test suite, the planner overhead is now a more manageable 0.5% to 1.3%; one test is faster by 0.5%.

Re: [HACKERS] PoC: Partial sort

2014-02-19 Thread Marti Raudsepp
On Wed, Feb 12, 2014 at 11:54 PM, Marti Raudsepp ma...@juffo.org wrote: With partial-sort-basic-1 and this fix on the same test suite, the planner overhead is now a more manageable 0.5% to 1.3%; one test is faster by 0.5%. Ping, Robert or anyone, does this overhead seem bearable or is that

Re: [HACKERS] PoC: Partial sort

2014-02-19 Thread Alexander Korotkov
On Thu, Feb 13, 2014 at 1:54 AM, Marti Raudsepp ma...@juffo.org wrote: I think the 1st patch now has a bug in initial_cost_mergejoin; you still pass the presorted_keys argument to cost_sort, making it calculate a partial sort cost, but generated plans never use partial sort. I think 0 should

Re: [HACKERS] PoC: Partial sort

2014-02-12 Thread Marti Raudsepp
On Mon, Feb 10, 2014 at 8:59 PM, Alexander Korotkov aekorot...@gmail.com wrote: Done. Patch is splitted. Thanks! I think the 1st patch now has a bug in initial_cost_mergejoin; you still pass the presorted_keys argument to cost_sort, making it calculate a partial sort cost, but generated plans

Re: [HACKERS] PoC: Partial sort

2014-02-10 Thread Marti Raudsepp
On Sun, Feb 9, 2014 at 7:37 PM, Alexander Korotkov aekorot...@gmail.com wrote: This is not only place that worry me about planning overhead. See get_cheapest_fractional_path_for_pathkeys. I had to estimate number of groups for each sorting column in order to get right fractional path. AFAICT

Re: [HACKERS] PoC: Partial sort

2014-02-10 Thread Alexander Korotkov
On Mon, Feb 10, 2014 at 2:33 PM, Marti Raudsepp ma...@juffo.org wrote: On Sun, Feb 9, 2014 at 7:37 PM, Alexander Korotkov aekorot...@gmail.com wrote: This is not only place that worry me about planning overhead. See get_cheapest_fractional_path_for_pathkeys. I had to estimate number of

Re: [HACKERS] PoC: Partial sort

2014-02-09 Thread Alexander Korotkov
On Thu, Feb 6, 2014 at 12:39 PM, Marti Raudsepp ma...@juffo.org wrote: On Thu, Feb 6, 2014 at 5:31 AM, Robert Haas robertmh...@gmail.com wrote: Hmm, sounds a little steep. Why is it so expensive? I'm probably missing something here, because I would have thought that planner support for

Re: [HACKERS] PoC: Partial sort

2014-02-06 Thread Marti Raudsepp
On Thu, Feb 6, 2014 at 5:31 AM, Robert Haas robertmh...@gmail.com wrote: Hmm, sounds a little steep. Why is it so expensive? I'm probably missing something here, because I would have thought that planner support for partial sorts would consist mostly of considering the same sorts we consider

Re: [HACKERS] PoC: Partial sort

2014-02-06 Thread Robert Haas
On Thu, Feb 6, 2014 at 3:39 AM, Marti Raudsepp ma...@juffo.org wrote: On Thu, Feb 6, 2014 at 5:31 AM, Robert Haas robertmh...@gmail.com wrote: Hmm, sounds a little steep. Why is it so expensive? I'm probably missing something here, because I would have thought that planner support for

Re: [HACKERS] PoC: Partial sort

2014-02-06 Thread Marti Raudsepp
On Thu, Feb 6, 2014 at 9:15 PM, Robert Haas robertmh...@gmail.com wrote: It may be that having the capability to do a partial sort makes it seem worth spending more CPU looking for merge joins, but I'd vote for making any such change a separate patch. Agreed. Alexander, should I work on

Re: [HACKERS] PoC: Partial sort

2014-02-06 Thread Tom Lane
Robert Haas robertmh...@gmail.com writes: On Thu, Feb 6, 2014 at 3:39 AM, Marti Raudsepp ma...@juffo.org wrote: I guess it's because the patch undoes some optimizations in the mergejoin planner wrt caching merge clauses and adds a whole lot of code to find_mergeclauses_for_pathkeys. In other

Re: [HACKERS] PoC: Partial sort

2014-02-05 Thread Marti Raudsepp
On Tue, Jan 28, 2014 at 7:51 AM, Alexander Korotkov aekorot...@gmail.comwrote: On Tue, Jan 28, 2014 at 7:41 AM, Marti Raudsepp ma...@juffo.org wrote: But some benchmarks of planning performance are certainly warranted. I didn't test it, but I worry that overhead might be high. If it's true

Re: [HACKERS] PoC: Partial sort

2014-02-05 Thread Robert Haas
On Wed, Feb 5, 2014 at 6:58 PM, Marti Raudsepp ma...@juffo.org wrote: I ran some synthetic benchmarks with single-column inner joins between 5 tables, with indexes on both joined columns, using only EXPLAIN (so measuring planning time, not execution) in 9 scenarios to excercise different code

Re: [HACKERS] PoC: Partial sort

2014-01-28 Thread Marti Raudsepp
On Tue, Jan 28, 2014 at 7:51 AM, Alexander Korotkov aekorot...@gmail.com wrote: I didn't test it, but I worry that overhead might be high. If it's true then it could be like constraint_exclusion option which id off by default because of planning overhead. I see, that makes sense. I will try

Re: [HACKERS] PoC: Partial sort

2014-01-27 Thread Alexander Korotkov
Hi! On Tue, Jan 21, 2014 at 3:24 AM, Marti Raudsepp ma...@juffo.org wrote: On Tue, Jan 14, 2014 at 5:49 PM, Alexander Korotkov aekorot...@gmail.com wrote: On Tue, Jan 14, 2014 at 12:54 AM, Marti Raudsepp ma...@juffo.org wrote: I've been trying it out in a few situations. I implemented a new

Re: [HACKERS] PoC: Partial sort

2014-01-27 Thread Marti Raudsepp
On Mon, Jan 27, 2014 at 9:26 PM, Alexander Korotkov aekorot...@gmail.com wrote: For now, I have attempt to fix extra columns in mergejoin problem. It would be nice if you test it. Yes, it solves the test cases I was trying with, thanks. 1) With enable_partialsort = off all mergejoin logic

Re: [HACKERS] PoC: Partial sort

2014-01-27 Thread Alexander Korotkov
On Tue, Jan 28, 2014 at 7:41 AM, Marti Raudsepp ma...@juffo.org wrote: On Mon, Jan 27, 2014 at 9:26 PM, Alexander Korotkov aekorot...@gmail.com wrote: For now, I have attempt to fix extra columns in mergejoin problem. It would be nice if you test it. Yes, it solves the test cases I was

Re: [HACKERS] PoC: Partial sort

2014-01-26 Thread Marti Raudsepp
On Mon, Jan 20, 2014 at 2:43 PM, Alexander Korotkov aekorot...@gmail.com wrote: Another changes in this version of patch: 1) Applied patch to don't compare skipCols in tuplesort by Marti Raudsepp 2) Adjusting sort bound after processing buckets. Hi, Here's a patch with some whitespace and

Re: [HACKERS] PoC: Partial sort

2014-01-20 Thread Alexander Korotkov
On Sun, Jan 19, 2014 at 5:57 AM, Andreas Karlsson andr...@proxel.se wrote: On 01/18/2014 08:13 PM, Jeremy Harris wrote: On 31/12/13 01:41, Andreas Karlsson wrote: On 12/29/2013 08:24 AM, David Rowley wrote: If it was possible to devise some way to reuse any previous tuplesortstate perhaps

Re: [HACKERS] PoC: Partial sort

2014-01-20 Thread Marti Raudsepp
Hi, On Tue, Jan 14, 2014 at 5:49 PM, Alexander Korotkov aekorot...@gmail.com wrote: On Tue, Jan 14, 2014 at 12:54 AM, Marti Raudsepp ma...@juffo.org wrote: I've been trying it out in a few situations. I implemented a new enable_partialsort GUC to make it easier to turn on/off, this way it's a

Re: [HACKERS] PoC: Partial sort

2014-01-18 Thread Jeremy Harris
On 13/01/14 18:01, Alexander Korotkov wrote: Thanks. It's included into attached version of patch. As wall as estimation improvements, more comments and regression tests fix. Would it be possible to totally separate the two sets of sort-keys, only giving the non-index set to the tuplesort? At

Re: [HACKERS] PoC: Partial sort

2014-01-18 Thread Marti Raudsepp
Hi, There's another small regression with this patch when used with expensive comparison functions, such as long text fields. If we go through all this trouble in cmpSortSkipCols to prove that the first N sortkeys are equal, it would be nice if Tuplesort could skip their comparisons entirely;

Re: [HACKERS] PoC: Partial sort

2014-01-18 Thread Marti Raudsepp
Funny, I just wrote a patch to do that some minutes ago (didn't see your email yet). http://www.postgresql.org/message-id/CABRT9RCK=wmFUYZdqU_+MOFW5PDevLxJmZ5B=etjjnubvya...@mail.gmail.com Regards, Marti On Sat, Jan 18, 2014 at 7:10 PM, Jeremy Harris j...@wizmail.org wrote: On 13/01/14

Re: [HACKERS] PoC: Partial sort

2014-01-18 Thread Jeremy Harris
On 31/12/13 01:41, Andreas Karlsson wrote: On 12/29/2013 08:24 AM, David Rowley wrote: If it was possible to devise some way to reuse any previous tuplesortstate perhaps just inventing a reset method which clears out tuples, then we could see performance exceed the standard seqscan - sort. The

Re: [HACKERS] PoC: Partial sort

2014-01-18 Thread Marti Raudsepp
On Sat, Jan 18, 2014 at 7:22 PM, Marti Raudsepp ma...@juffo.org wrote: Total runtime: 5.418 ms Oops, shouldn't have rushed this. Clearly the timings should have tipped me off that it's broken. I didn't notice that cmpSortSkipCols was re-using tuplesort's sortkeys. Here's a patch that actually

Re: [HACKERS] PoC: Partial sort

2014-01-18 Thread Andreas Karlsson
On 01/18/2014 08:13 PM, Jeremy Harris wrote: On 31/12/13 01:41, Andreas Karlsson wrote: On 12/29/2013 08:24 AM, David Rowley wrote: If it was possible to devise some way to reuse any previous tuplesortstate perhaps just inventing a reset method which clears out tuples, then we could see

Re: [HACKERS] PoC: Partial sort

2014-01-16 Thread Jeremy Harris
On 22/12/13 20:26, Alexander Korotkov wrote: On Sat, Dec 14, 2013 at 6:30 PM, Jeremy Harris j...@wizmail.org wrote: On 14/12/13 12:54, Andres Freund wrote: Is that actually all that beneficial when sorting with a bog standard qsort() since that doesn't generally benefit from data being

Re: [HACKERS] PoC: Partial sort

2014-01-14 Thread Alexander Korotkov
Hi! On Tue, Jan 14, 2014 at 12:54 AM, Marti Raudsepp ma...@juffo.org wrote: First, thanks a lot for working on this feature. This PostgreSQL shortcoming crops up in all the time in web applications that implement paging by multiple sorted columns. Thanks! I've been trying it out in a few

Re: [HACKERS] PoC: Partial sort

2014-01-14 Thread Marti Raudsepp
On Tue, Jan 14, 2014 at 5:49 PM, Alexander Korotkov aekorot...@gmail.com wrote: I implemented a new enable_partialsort GUC to make it easier to turn on/off I though about such option. Generally not because of testing convenience, but because of overhead of planning. This way you implement it

Re: [HACKERS] PoC: Partial sort

2014-01-14 Thread Alexander Korotkov
On Tue, Jan 14, 2014 at 11:16 PM, Marti Raudsepp ma...@juffo.org wrote: On Tue, Jan 14, 2014 at 5:49 PM, Alexander Korotkov aekorot...@gmail.com wrote: I implemented a new enable_partialsort GUC to make it easier to turn on/off I though about such option. Generally not because of testing

Re: [HACKERS] PoC: Partial sort

2014-01-14 Thread Marti Raudsepp
On Tue, Jan 14, 2014 at 9:28 PM, Alexander Korotkov aekorot...@gmail.comwrote: On Tue, Jan 14, 2014 at 11:16 PM, Marti Raudsepp ma...@juffo.org wrote: Oh, this actually highlights a performance regression with the partial sort patch. Interesting. Could you share the dataset? It occurs

Re: [HACKERS] PoC: Partial sort

2014-01-13 Thread Alexander Korotkov
On Tue, Dec 31, 2013 at 5:41 AM, Andreas Karlsson andr...@proxel.se wrote: On 12/29/2013 08:24 AM, David Rowley wrote: If it was possible to devise some way to reuse any previous tuplesortstate perhaps just inventing a reset method which clears out tuples, then we could see performance

Re: [HACKERS] PoC: Partial sort

2014-01-13 Thread Marti Raudsepp
Hi Alexander, First, thanks a lot for working on this feature. This PostgreSQL shortcoming crops up in all the time in web applications that implement paging by multiple sorted columns. I've been trying it out in a few situations. I implemented a new enable_partialsort GUC to make it easier to

Re: [HACKERS] PoC: Partial sort

2013-12-31 Thread David Rowley
On Tue, Dec 31, 2013 at 2:41 PM, Andreas Karlsson andr...@proxel.se wrote: On 12/29/2013 08:24 AM, David Rowley wrote: If it was possible to devise some way to reuse any previous tuplesortstate perhaps just inventing a reset method which clears out tuples, then we could see performance

Re: [HACKERS] PoC: Partial sort

2013-12-31 Thread Alvaro Herrera
David Rowley escribió: I was about to test it tonight, but I'm having trouble getting the patch to compile... I'm really wondering which compiler you are using as it seems you're declaring your variables in some strange places.. See nodeSort.c line 101. These variables are declared after

Re: [HACKERS] PoC: Partial sort

2013-12-31 Thread Andreas Karlsson
On 12/28/2013 04:51 PM, Alexander Korotkov wrote: I've compiled it with clang. Yeah, there was mixed declarations. I've rechecked it with gcc, now it gives no warnings. I didn't try it with visual studio, but I hope it will be OK. I looked at this version of the patch and noticed a possibility

Re: [HACKERS] PoC: Partial sort

2013-12-28 Thread David Rowley
On Sat, Dec 28, 2013 at 9:28 PM, Alexander Korotkov aekorot...@gmail.comwrote: On Tue, Dec 24, 2013 at 6:02 AM, Andreas Karlsson andr...@proxel.sewrote: Attached revision of patch implements partial sort usage in merge joins. I'm looking forward to doing a bit of testing on this patch. I

Re: [HACKERS] PoC: Partial sort

2013-12-28 Thread David Rowley
On Sun, Dec 29, 2013 at 4:51 AM, Alexander Korotkov aekorot...@gmail.comwrote: I've compiled it with clang. Yeah, there was mixed declarations. I've rechecked it with gcc, now it gives no warnings. I didn't try it with visual studio, but I hope it will be OK. Thanks for the patch. It now

Re: [HACKERS] PoC: Partial sort

2013-12-23 Thread Andreas Karlsson
On 12/22/2013 04:38 PM, Alexander Korotkov wrote: postgres=# explain analyze select * from test order by v1, id limit 10; QUERY PLAN

Re: [HACKERS] PoC: Partial sort

2013-12-22 Thread Martijn van Oosterhout
On Sun, Dec 22, 2013 at 07:38:05PM +0400, Alexander Korotkov wrote: Hi! Next revision. It expected to do better work with optimizer. It introduces presorted_keys argument of cost_sort function which represent number of keys already sorted in Path. Then this function uses estimate_num_groups

Re: [HACKERS] PoC: Partial sort

2013-12-22 Thread Alexander Korotkov
On Sun, Dec 22, 2013 at 8:12 PM, Martijn van Oosterhout klep...@svana.orgwrote: On Sun, Dec 22, 2013 at 07:38:05PM +0400, Alexander Korotkov wrote: Hi! Next revision. It expected to do better work with optimizer. It introduces presorted_keys argument of cost_sort function which

Re: [HACKERS] PoC: Partial sort

2013-12-22 Thread Alexander Korotkov
On Sat, Dec 14, 2013 at 6:30 PM, Jeremy Harris j...@wizmail.org wrote: On 14/12/13 12:54, Andres Freund wrote: On 2013-12-14 13:59:02 +0400, Alexander Korotkov wrote: Currently when we need to get ordered result from table we have to choose one of two approaches: get results from index in

Re: [HACKERS] PoC: Partial sort

2013-12-18 Thread Alexander Korotkov
On Sat, Dec 14, 2013 at 6:39 PM, Martijn van Oosterhout klep...@svana.orgwrote: On Sat, Dec 14, 2013 at 06:21:18PM +0400, Alexander Korotkov wrote: Is that actually all that beneficial when sorting with a bog standard qsort() since that doesn't generally benefit from data being

Re: [HACKERS] PoC: Partial sort

2013-12-18 Thread Alexander Korotkov
On Sat, Dec 14, 2013 at 7:04 PM, Andres Freund and...@2ndquadrant.comwrote: Hi, Limit (cost=69214.06..69214.08 rows=10 width=16) (actual time=0.097..0.099 rows=10 loops=1) - Sort (cost=69214.06..71714.06 rows=100 width=16) (actual time=0.096..0.097 rows=10 loops=1)

Re: [HACKERS] PoC: Partial sort

2013-12-18 Thread Alexander Korotkov
On Sat, Dec 14, 2013 at 11:47 PM, Andreas Karlsson andr...@proxel.sewrote: On 12/14/2013 10:59 AM, Alexander Korotkov wrote: This patch allows to use index for order-by if order-by clause and index has non-empty common prefix. So, index gives right ordering for first n order-by columns. In

Re: [HACKERS] PoC: Partial sort

2013-12-18 Thread Andreas Karlsson
On 12/18/2013 01:02 PM, Alexander Korotkov wrote: My idea for a solution was to modify tuplesort to allow storing the already sorted keys in either memtuples or the sort result file, but setting a field so it does not sort thee already sorted tuples again. This would allow the

Re: [HACKERS] PoC: Partial sort

2013-12-14 Thread Andres Freund
Hi, Cool stuff. On 2013-12-14 13:59:02 +0400, Alexander Korotkov wrote: Currently when we need to get ordered result from table we have to choose one of two approaches: get results from index in exact order we need or do sort of tuples. However, it could be useful to mix both methods: get

Re: [HACKERS] PoC: Partial sort

2013-12-14 Thread Alexander Korotkov
Hi! Thanks for feedback! On Sat, Dec 14, 2013 at 4:54 PM, Andres Freund and...@2ndquadrant.comwrote: Hi, Cool stuff. On 2013-12-14 13:59:02 +0400, Alexander Korotkov wrote: Currently when we need to get ordered result from table we have to choose one of two approaches: get results from

Re: [HACKERS] PoC: Partial sort

2013-12-14 Thread Jeremy Harris
On 14/12/13 12:54, Andres Freund wrote: On 2013-12-14 13:59:02 +0400, Alexander Korotkov wrote: Currently when we need to get ordered result from table we have to choose one of two approaches: get results from index in exact order we need or do sort of tuples. However, it could be useful to mix

Re: [HACKERS] PoC: Partial sort

2013-12-14 Thread Martijn van Oosterhout
On Sat, Dec 14, 2013 at 06:21:18PM +0400, Alexander Korotkov wrote: Is that actually all that beneficial when sorting with a bog standard qsort() since that doesn't generally benefit from data being pre-sorted? I think we might need to switch to a different algorithm to really benefit from

Re: [HACKERS] PoC: Partial sort

2013-12-14 Thread Andres Freund
Hi, Limit (cost=69214.06..69214.08 rows=10 width=16) (actual time=0.097..0.099 rows=10 loops=1) - Sort (cost=69214.06..71714.06 rows=100 width=16) (actual time=0.096..0.097 rows=10 loops=1) Sort Key: v1, v2 Sort Method: top-N heapsort Memory: 25kB

Re: [HACKERS] PoC: Partial sort

2013-12-14 Thread Andreas Karlsson
On 12/14/2013 10:59 AM, Alexander Korotkov wrote: This patch allows to use index for order-by if order-by clause and index has non-empty common prefix. So, index gives right ordering for first n order-by columns. In order to provide right order for rest m columns, sort node is inserted. This