Re: [HACKERS] Optimizer on sort aggregate

2014-10-19 Thread David Rowley
On Sat, Oct 18, 2014 at 2:25 PM, Feng Tian wrote: > Hi, David, > > Yes, switch sorting order would loose an interesting order so if user > dictates order by t, i; planner need to resort to its cost model. > Estimating cardinality of groupby is a much bigger topic than this thread. > > Well I don'

Re: [HACKERS] Optimizer on sort aggregate

2014-10-18 Thread Peter Geoghegan
On Sat, Oct 18, 2014 at 5:27 AM, Greg Stark wrote: > That's interesting but I think it's mostly a quirk of your example. > Afaics the difference is only that the en_US locale ignores > punctuation like : and / (or at least treats them as less significant > than alphabetic characters). If you had

Re: [HACKERS] Optimizer on sort aggregate

2014-10-18 Thread Greg Stark
On Sat, Oct 18, 2014 at 3:10 AM, Peter Geoghegan wrote: > So the first eight bytes of the first string is 0x131F1F1B221E, > and the second 0x131F1F1B220C. The last byte is different. That's interesting but I think it's mostly a quirk of your example. Afaics the difference is only that the

Re: [HACKERS] Optimizer on sort aggregate

2014-10-17 Thread Peter Geoghegan
On Fri, Oct 17, 2014 at 6:25 PM, Feng Tian wrote: > I feel sorting string as if it is bytea is particularly interesting. I am > aware Peter G's patch and I think it is great, but for this sort agg case, > first, I believe it is still slower than sorting bytea, and second, Peter > G's patch depend

Re: [HACKERS] Optimizer on sort aggregate

2014-10-17 Thread Feng Tian
Hi, David, Yes, switch sorting order would loose an interesting order so if user dictates order by t, i; planner need to resort to its cost model. Estimating cardinality of groupby is a much bigger topic than this thread. I feel sorting string as if it is bytea is particularly interesting. I am

Re: [HACKERS] Optimizer on sort aggregate

2014-10-17 Thread David Rowley
On Sat, Oct 18, 2014 at 12:35 PM, Tatsuo Ishii wrote: > > The query, > > select count(distinct j) from t group by t, i; > > > > runs for 35 seconds. However, if I change the query to, > > select count(distinct j) from t group by i, t; -- note switching the > > ordering > > select count(distinct

Re: [HACKERS] Optimizer on sort aggregate

2014-10-17 Thread David Rowley
On Sat, Oct 18, 2014 at 5:10 AM, Feng Tian wrote: > Hi, > > Consider the following queries. > > create table t(i int, j int, k int, t text); > insert into t select i, i % 100, i % 1000, 'AABBCCDD' || i from > generate_series(1, 100) i; > > ftian=# explain select count(distinct j) from t group

Re: [HACKERS] Optimizer on sort aggregate

2014-10-17 Thread Tatsuo Ishii
> The query, > select count(distinct j) from t group by t, i; > > runs for 35 seconds. However, if I change the query to, > select count(distinct j) from t group by i, t; -- note switching the > ordering > select count(distinct j) from t group by decode(t, 'escape'), i; -- convert > t to bytea >

[HACKERS] Optimizer on sort aggregate

2014-10-17 Thread Feng Tian
Hi, Consider the following queries. create table t(i int, j int, k int, t text); insert into t select i, i % 100, i % 1000, 'AABBCCDD' || i from generate_series(1, 100) i; ftian=# explain select count(distinct j) from t group by t, i; QUERY PLAN ---