Re: [HACKERS] Cardinality estimation for group by
On Tue, Oct 4, 2016 at 8:05 AM, Chenxi Liwrote: > How is cardinality estimation for "group by" is done and where is the code > doing that? I would suggest that you start by looking at estimate_num_groups() in src/backend/utils/adt/selfuncs.c. You might also want to look at cost_agg() in src/backend/optimizer/path/costsize.c, and also the places where it gets called. They don't all estimate the number of groups in the same way. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Cardinality estimation for group by
Friends, How is cardinality estimation for "group by" is done and where is the code doing that? Best Regards, Chenxi Li
Re: [HACKERS] cardinality()
On 1 Mar 2009, at 00:52, Andrew Dunstan wrote: We seem to have acquired a cardinality() function with almost no discussion, and it has semantics that are a bit surprising to me. I should have thought cardinality(array) would be the total number of elements in the array. Instead, it seems it is a synonym for array_length(array,1). Is that *really* what the standard says? any difference between array_upper(array,1), and cardinality ? Standart just says something like: cardinality (a collection): - The number of elements in that collection. - Those elements need not necessarily have distinct values. - The objects to which this concept applies includes tables and the values of collection types. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] cardinality()
Grzegorz Jaskiewicz wrote: On 1 Mar 2009, at 00:52, Andrew Dunstan wrote: We seem to have acquired a cardinality() function with almost no discussion, and it has semantics that are a bit surprising to me. I should have thought cardinality(array) would be the total number of elements in the array. Instead, it seems it is a synonym for array_length(array,1). Is that *really* what the standard says? any difference between array_upper(array,1), and cardinality ? Standart just says something like: cardinality (a collection): - The number of elements in that collection. - Those elements need not necessarily have distinct values. - The objects to which this concept applies includes tables and the values of collection types. Well, I think that's a definition of the term as used in the standard, rather than of a function. But in any case, I think it goes in the right direction, and the semantics of our new function (as well as the docs) are misleading. I'm also a bit concerned that I could not find any real discussion of this new function at all on this list, so our processes seem to have slipped a bit. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] cardinality()
Andrew Dunstan and...@dunslane.net writes: Grzegorz Jaskiewicz wrote: On 1 Mar 2009, at 00:52, Andrew Dunstan wrote: We seem to have acquired a cardinality() function with almost no discussion, and it has semantics that are a bit surprising to me. I should have thought cardinality(array) would be the total number of . elements in the array. Instead, it seems it is a synonym for array_length(array,1). Is that *really* what the standard says? Standart just says something like: cardinality (a collection): - The number of elements in that collection. The standard doesn't have multi-dimensional arrays, so it's entirely possible that somewhere in it there is wording that makes cardinality() equivalent to the length of the first dimension. But I concur with Andrew that this is flat wrong when extended to m-d arrays. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] cardinality()
I wrote: The standard doesn't have multi-dimensional arrays, so it's entirely possible that somewhere in it there is wording that makes cardinality() equivalent to the length of the first dimension. But I concur with Andrew that this is flat wrong when extended to m-d arrays. I poked around in the SQL:2008 draft a bit. AFAICT the most precise statement about cardinality() is in 6.27 numeric value function: cardinality expression ::= CARDINALITYleft paren collection value expression right paren 7) The result of cardinality expression is the number of elements of the result of the collection value expression. Now the standard is only considering 1-D arrays, but I fail to see any way that it could be argued that the appropriate reading of number of elements for a multi-D array is the length of the first dimension. So I think Andrew is right and we need to fix our implementation of cardinality() while we still can. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] cardinality()
2009/3/1 Tom Lane t...@sss.pgh.pa.us: I wrote: The standard doesn't have multi-dimensional arrays, so it's entirely possible that somewhere in it there is wording that makes cardinality() equivalent to the length of the first dimension. But I concur with Andrew that this is flat wrong when extended to m-d arrays. I poked around in the SQL:2008 draft a bit. AFAICT the most precise statement about cardinality() is in 6.27 numeric value function: cardinality expression ::= CARDINALITYleft paren collection value expression right paren 7) The result of cardinality expression is the number of elements of the result of the collection value expression. Now the standard is only considering 1-D arrays, but I fail to see any way that it could be argued that the appropriate reading of number of elements for a multi-D array is the length of the first dimension. So I think Andrew is right and we need to fix our implementation of cardinality() while we still can. ₊1 regards Pavel Stehule regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] cardinality()
On Sun, 1 Mar 2009, Tom Lane wrote: I wrote: The standard doesn't have multi-dimensional arrays, so it's entirely possible that somewhere in it there is wording that makes cardinality() equivalent to the length of the first dimension. But I concur with Andrew that this is flat wrong when extended to m-d arrays. I poked around in the SQL:2008 draft a bit. AFAICT the most precise statement about cardinality() is in 6.27 numeric value function: cardinality expression ::= CARDINALITYleft paren collection value expression right paren 7) The result of cardinality expression is the number of elements of the result of the collection value expression. Now the standard is only considering 1-D arrays, but I fail to see any way that it could be argued that the appropriate reading of number of elements for a multi-D array is the length of the first dimension. Does the standard allow you to make arrays of arrays, for example with something like ARRAY[ARRAY[1,2], ARRAY[3,4]]? If so, it might be possible that cardinality(that expression) would be returning the number of arrays in the outer array. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] cardinality()
On Sunday 01 March 2009 19:40:16 Tom Lane wrote: I wrote: The standard doesn't have multi-dimensional arrays, so it's entirely possible that somewhere in it there is wording that makes cardinality() equivalent to the length of the first dimension. But I concur with Andrew that this is flat wrong when extended to m-d arrays. I poked around in the SQL:2008 draft a bit. AFAICT the most precise statement about cardinality() is in 6.27 numeric value function: cardinality expression ::= CARDINALITYleft paren collection value expression right paren 7) The result of cardinality expression is the number of elements of the result of the collection value expression. Now the standard is only considering 1-D arrays, The standard represents multidimensional arrays as arrays of arrays (like in C). But the cardinality is only that of the first level array. The real question here is how we want to consider mapping what the standard has to what PostgreSQL has, and might have in the future. For example, will we ever have arrays of arrays as distinct from multidimensional arrays? Will we support things like array of multiset of array? What would the results be there? I think PostgreSQL multidimensional array support and SQL standard multidimensional array support are pretty well in line leaving aside minor syntax issues and the major syntax issue that the subscript order is reversed. So I think there is not much of a need to do much redefining and reinterpreting, unless someone has a larger and different plan in mind. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] cardinality()
Peter Eisentraut pete...@gmx.net writes: The standard represents multidimensional arrays as arrays of arrays (like in C). Uh, C doesn't represent multidimensional arrays as arrays of arrays so you've lost me already. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's Slony Replication support! -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] cardinality()
Gregory Stark wrote: Peter Eisentraut pete...@gmx.net writes: The standard represents multidimensional arrays as arrays of arrays (like in C). Uh, C doesn't represent multidimensional arrays as arrays of arrays so you've lost me already. I think he meant to say C _can_ represent multidimensional arrays as arrays of arrays. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] cardinality()
We seem to have acquired a cardinality() function with almost no discussion, and it has semantics that are a bit surprising to me. I should have thought cardinality(array) would be the total number of elements in the array. Instead, it seems it is a synonym for array_length(array,1). Is that *really* what the standard says? cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers