Re: [HACKERS] Abbreviated keys for text cost model fix

2015-04-03 Thread Robert Haas
On Mon, Feb 23, 2015 at 7:44 PM, Peter Geoghegan p...@heroku.com wrote: On Mon, Feb 23, 2015 at 4:41 PM, Tomas Vondra tomas.von...@2ndquadrant.com wrote: Are you going to add this into the CF? Would be nice to get this into 9.5. Strictly speaking it should go to 2015-06 I guess, but I'd

Re: [HACKERS] Abbreviated keys for text cost model fix

2015-04-03 Thread Peter Geoghegan
On Fri, Apr 3, 2015 at 1:49 PM, Robert Haas robertmh...@gmail.com wrote: Committed. For future reference, I'd prefer to have things like this added to the next CF rather than no CF at all. I'll bear that in mind. Thanks. -- Peter Geoghegan -- Sent via pgsql-hackers mailing list

Re: [HACKERS] Abbreviated keys for text cost model fix

2015-03-03 Thread Peter Geoghegan
On Tue, Mar 3, 2015 at 2:00 AM, Jeremy Harris j...@wizmail.org wrote: Yes; there seemed no advantage to any additional complexity. The merge consistently performs fewer comparisons than the quicksort, on random input - and many fewer if there are any sorted (or reverse-sorted) sections.

Re: [HACKERS] Abbreviated keys for text cost model fix

2015-03-03 Thread Jeremy Harris
On 03/03/15 03:08, Arthur Silva wrote: Does it always perform mergesort instead of quicksort when enabled? Yes; there seemed no advantage to any additional complexity. The merge consistently performs fewer comparisons than the quicksort, on random input - and many fewer if there are any sorted

Re: [HACKERS] Abbreviated keys for text cost model fix

2015-02-25 Thread Jeremy Harris
On 25/02/15 00:42, Peter Geoghegan wrote: On Tue, Feb 24, 2015 at 4:32 PM, Jeremy Harris j...@wizmail.org wrote: On 23/02/15 16:40, Tomas Vondra wrote: On 22.2.2015 22:30, Peter Geoghegan wrote: You should try it with the data fully sorted like this, but with one tiny difference: The very

Re: [HACKERS] Abbreviated keys for text cost model fix

2015-02-24 Thread Jeremy Harris
On 23/02/15 16:40, Tomas Vondra wrote: On 22.2.2015 22:30, Peter Geoghegan wrote: You should try it with the data fully sorted like this, but with one tiny difference: The very last tuple is out of order. How does that look? If this case is actually important, a merge-sort can take

Re: [HACKERS] Abbreviated keys for text cost model fix

2015-02-24 Thread Peter Geoghegan
On Tue, Feb 24, 2015 at 4:32 PM, Jeremy Harris j...@wizmail.org wrote: On 23/02/15 16:40, Tomas Vondra wrote: On 22.2.2015 22:30, Peter Geoghegan wrote: You should try it with the data fully sorted like this, but with one tiny difference: The very last tuple is out of order. How does that

Re: [HACKERS] Abbreviated keys for text cost model fix

2015-02-23 Thread Tomas Vondra
Hi, On 22.2.2015 22:30, Peter Geoghegan wrote: On Sun, Feb 22, 2015 at 1:19 PM, Tomas Vondra tomas.von...@2ndquadrant.com wrote: In short, this fixes all the cases except for the ASC sorted data. I haven't done any code review, but I think we want this. I'll use data from the i5-2500k, but

Re: [HACKERS] Abbreviated keys for text cost model fix

2015-02-23 Thread Peter Geoghegan
On Mon, Feb 23, 2015 at 11:17 AM, Tomas Vondra tomas.von...@2ndquadrant.com wrote: So while it's true that for the 3rd query we get much worse results compared to the other queries (i.e. we don't get 400% speedup but ~3% slowdown compared to master), it's true that master performs

Re: [HACKERS] Abbreviated keys for text cost model fix

2015-02-23 Thread Peter Geoghegan
On Mon, Feb 23, 2015 at 8:40 AM, Tomas Vondra tomas.von...@2ndquadrant.com wrote: The durations are much higher than without the single unsorted row added at the end. Queries often take 20x longer to finish (on the same code), depending on the scale. I knew this would happen. :-) What's

Re: [HACKERS] Abbreviated keys for text cost model fix

2015-02-23 Thread Tomas Vondra
On 23.2.2015 19:22, Peter Geoghegan wrote: On Mon, Feb 23, 2015 at 8:40 AM, Tomas Vondra tomas.von...@2ndquadrant.com wrote: The durations are much higher than without the single unsorted row added at the end. Queries often take 20x longer to finish (on the same code), depending on the scale.

Re: [HACKERS] Abbreviated keys for text cost model fix

2015-02-23 Thread Tomas Vondra
On 24.2.2015 01:44, Peter Geoghegan wrote: On Mon, Feb 23, 2015 at 4:41 PM, Tomas Vondra tomas.von...@2ndquadrant.com wrote: Are you going to add this into the CF? Would be nice to get this into 9.5. Strictly speaking it should go to 2015-06 I guess, but I'd consider it part of one of the

Re: [HACKERS] Abbreviated keys for text cost model fix

2015-02-23 Thread Peter Geoghegan
On Mon, Feb 23, 2015 at 4:41 PM, Tomas Vondra tomas.von...@2ndquadrant.com wrote: Are you going to add this into the CF? Would be nice to get this into 9.5. Strictly speaking it should go to 2015-06 I guess, but I'd consider it part of one of the existing sortsupport patches. It's a bugfix,

Re: [HACKERS] Abbreviated keys for text cost model fix

2015-02-23 Thread Tomas Vondra
On 23.2.2015 21:52, Peter Geoghegan wrote: On Mon, Feb 23, 2015 at 11:17 AM, Tomas Vondra tomas.von...@2ndquadrant.com wrote: So while it's true that for the 3rd query we get much worse results compared to the other queries (i.e. we don't get 400% speedup but ~3% slowdown compared to master),

Re: [HACKERS] Abbreviated keys for text cost model fix

2015-02-22 Thread Peter Geoghegan
On Sun, Feb 22, 2015 at 1:30 PM, Peter Geoghegan p...@heroku.com wrote: You should try it with the data fully sorted like this, but with one tiny difference: The very last tuple is out of order. How does that look? Another thing that may be of particular interest to you as a Czech person is

Re: [HACKERS] Abbreviated keys for text cost model fix

2015-02-22 Thread Peter Geoghegan
On Sun, Feb 22, 2015 at 1:19 PM, Tomas Vondra tomas.von...@2ndquadrant.com wrote: In short, this fixes all the cases except for the ASC sorted data. I haven't done any code review, but I think we want this. I'll use data from the i5-2500k, but it applies to the Xeon too, except that the Xeon

Re: [HACKERS] Abbreviated keys for text cost model fix

2015-02-22 Thread Tomas Vondra
On 23.2.2015 00:16, Peter Geoghegan wrote: On Sun, Feb 22, 2015 at 1:30 PM, Peter Geoghegan p...@heroku.com wrote: You should try it with the data fully sorted like this, but with one tiny difference: The very last tuple is out of order. How does that look? I'm running that test now, I'll

[HACKERS] Abbreviated keys for text cost model fix

2015-02-21 Thread Peter Geoghegan
Over in the abbreviated keys for numeric thread, Tomas Vondra reports a case where the ad-hoc cost model of abbreviated keys/sortsupport for text is far too conservative, and aborts a case where abbreviated keys can still greatly help. Previously, I proposed additions to the cost model that dealt