Re: [HACKERS] Cardinality estimation for group by

[Robert Haas]

On Tue, Oct 4, 2016 at 8:05 AM, Chenxi Li  wrote:
> 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

Re: [HACKERS] cardinality()

[Grzegorz Jaskiewicz]

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()

[Andrew Dunstan]

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()

[Tom Lane]

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()

[Tom Lane]

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()

[Pavel Stehule]

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()

[Stephan Szabo]

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()

[Peter Eisentraut]

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()

[Gregory Stark]

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()

[Bruce Momjian]

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