Re: Abbreviated keys for text cost model fix

Поиск
Список
Период
Сортировка
От Tomas Vondra
Тема Re: Abbreviated keys for text cost model fix
Дата
Msg-id 54EBC8D7.5030100@2ndquadrant.com
обсуждение исходный текст
Ответ на Re: Abbreviated keys for text cost model fix  (Peter Geoghegan <pg@heroku.com>)
Ответы Re: Abbreviated keys for text cost model fix  (Peter Geoghegan <pg@heroku.com>)
Список pgsql-hackers
On 23.2.2015 21:52, Peter Geoghegan wrote:
> On Mon, Feb 23, 2015 at 11:17 AM, Tomas Vondra
> <tomas.vondra@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
>> exceptionally well for this query with small datasets. Once we get to 2M
>> rows, the master performance drops significantly but cost-model keeps
>> the performance characteristics and the speedup jumps back to ~700%
>> which is nice.
>>
>> These numbers are for the 'ASC + unsorted row' test, but I do get
>> exactly the same pattern for the 'random' tests done previously.
> 
> Yeah. Looks like you're comparing a case where the old cost model
> did the right thing anyway (i.e. used abbreviation). The difference
> would then be entirely explainable as noise. Right?

I don't think it's just noise - on the i5-2500 CPU, the result are very
consistent. For 1M random rows with this query (#3)
   select * from (select * from stuff_text order by randtxt                                          offset
100000000000)foo
 

I do get this (with 20 of the query):
                min       max      median    mean      stddev
------------------------------------------------------------  master       932.449   932.902  932.637   932.639   0.116
 cost-model   957.16    958.47   957.497   957.586   0.411
 

That is super-consistent, IMHO, and the ~2.6% slowdown is clearly
visible. But it might be due to the padding described by Andrew.

Similarly on the Xeon E5450 CPU, although the results there are much
more noisy. Maybe it's more

>> It would be nice if we could address the 3% regression for the
>> last query, but I guess it's not a big deal. The numbers in general
>> are absolutely impressive. Kudos.
> 
> Thanks.

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.

-- 
Tomas Vondra                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services



В списке pgsql-hackers по дате отправления:

Предыдущее
От: Michael Paquier
Дата:
Сообщение: Raspberry PI vs Raspberry PI 2: time to compile backend code
Следующее
От: Peter Geoghegan
Дата:
Сообщение: Re: Abbreviated keys for text cost model fix