PostGIS  3.1.6dev-r@@SVN_REVISION@@

◆ estimate_selectivity()

static float8 estimate_selectivity ( const GBOX box,
const ND_STATS nd_stats,
int  mode 
)
static

This function returns an estimate of the selectivity of a search GBOX by looking at data in the ND_STATS structure.

The selectivity is a float from 0-1, that estimates the proportion of the rows in the table that will be returned as a result of the search box.

To get our estimate, we need "only" sum up the values * the proportion of each cell in the histogram that falls within the search box, then divide by the number of features that generated the histogram.

Definition at line 1959 of file gserialized_estimate.c.

1960 {
1961  int d; /* counter */
1962  float8 selectivity;
1963  ND_BOX nd_box;
1964  ND_IBOX nd_ibox;
1965  int at[ND_DIMS];
1966  double cell_size[ND_DIMS];
1967  double min[ND_DIMS];
1968  double max[ND_DIMS];
1969  double total_count = 0.0;
1970  int ndims_max;
1971 
1972  /* Calculate the overlap of the box on the histogram */
1973  if ( ! nd_stats )
1974  {
1975  elog(NOTICE, " estimate_selectivity called with null input");
1976  return FALLBACK_ND_SEL;
1977  }
1978 
1979  ndims_max = Max(nd_stats->ndims, gbox_ndims(box));
1980 
1981  /* Initialize nd_box. */
1982  nd_box_from_gbox(box, &nd_box);
1983 
1984  /*
1985  * To return 2D stats on an ND sample, we need to make the
1986  * 2D box cover the full range of the other dimensions in the
1987  * histogram.
1988  */
1989  POSTGIS_DEBUGF(3, " mode: %d", mode);
1990  if ( mode == 2 )
1991  {
1992  POSTGIS_DEBUG(3, " in 2d mode, stripping the computation down to 2d");
1993  ndims_max = 2;
1994  }
1995 
1996  POSTGIS_DEBUGF(3, " nd_stats->extent: %s", nd_box_to_json(&(nd_stats->extent), nd_stats->ndims));
1997  POSTGIS_DEBUGF(3, " nd_box: %s", nd_box_to_json(&(nd_box), gbox_ndims(box)));
1998 
1999  /*
2000  * Search box completely misses histogram extent?
2001  * We have to intersect in all N dimensions or else we have
2002  * zero interaction under the &&& operator. It's important
2003  * to short circuit in this case, as some of the tests below
2004  * will return junk results when run on non-intersecting inputs.
2005  */
2006  if ( ! nd_box_intersects(&nd_box, &(nd_stats->extent), ndims_max) )
2007  {
2008  POSTGIS_DEBUG(3, " search box does not overlap histogram, returning 0");
2009  return 0.0;
2010  }
2011 
2012  /* Search box completely contains histogram extent! */
2013  if ( nd_box_contains(&nd_box, &(nd_stats->extent), ndims_max) )
2014  {
2015  POSTGIS_DEBUG(3, " search box contains histogram, returning 1");
2016  return 1.0;
2017  }
2018 
2019  /* Calculate the overlap of the box on the histogram */
2020  if ( ! nd_box_overlap(nd_stats, &nd_box, &nd_ibox) )
2021  {
2022  POSTGIS_DEBUG(3, " search box overlap with stats histogram failed");
2023  return FALLBACK_ND_SEL;
2024  }
2025 
2026  /* Work out some measurements of the histogram */
2027  for ( d = 0; d < nd_stats->ndims; d++ )
2028  {
2029  /* Cell size in each dim */
2030  min[d] = nd_stats->extent.min[d];
2031  max[d] = nd_stats->extent.max[d];
2032  cell_size[d] = (max[d] - min[d]) / nd_stats->size[d];
2033  POSTGIS_DEBUGF(3, " cell_size[%d] : %.9g", d, cell_size[d]);
2034 
2035  /* Initialize the counter */
2036  at[d] = nd_ibox.min[d];
2037  }
2038 
2039  /* Move through all the overlap values and sum them */
2040  do
2041  {
2042  float cell_count, ratio;
2043  ND_BOX nd_cell = { {0.0, 0.0, 0.0, 0.0}, {0.0, 0.0, 0.0, 0.0} };
2044 
2045  /* We have to pro-rate partially overlapped cells. */
2046  for ( d = 0; d < nd_stats->ndims; d++ )
2047  {
2048  nd_cell.min[d] = min[d] + (at[d]+0) * cell_size[d];
2049  nd_cell.max[d] = min[d] + (at[d]+1) * cell_size[d];
2050  }
2051 
2052  ratio = nd_box_ratio(&nd_box, &nd_cell, nd_stats->ndims);
2053  cell_count = nd_stats->value[nd_stats_value_index(nd_stats, at)];
2054 
2055  /* Add the pro-rated count for this cell to the overall total */
2056  total_count += cell_count * ratio;
2057  POSTGIS_DEBUGF(4, " cell (%d,%d), cell value %.6f, ratio %.6f", at[0], at[1], cell_count, ratio);
2058  }
2059  while ( nd_increment(&nd_ibox, nd_stats->ndims, at) );
2060 
2061  /* Scale by the number of features in our histogram to get the proportion */
2062  selectivity = total_count / nd_stats->histogram_features;
2063 
2064  POSTGIS_DEBUGF(3, " nd_stats->histogram_features = %f", nd_stats->histogram_features);
2065  POSTGIS_DEBUGF(3, " nd_stats->histogram_cells = %f", nd_stats->histogram_cells);
2066  POSTGIS_DEBUGF(3, " sum(overlapped histogram cells) = %f", total_count);
2067  POSTGIS_DEBUGF(3, " selectivity = %f", selectivity);
2068 
2069  /* Prevent rounding overflows */
2070  if (selectivity > 1.0) selectivity = 1.0;
2071  else if (selectivity < 0.0) selectivity = 0.0;
2072 
2073  return selectivity;
2074 }
static int nd_box_intersects(const ND_BOX *a, const ND_BOX *b, int ndims)
Return true if ND_BOX a overlaps b, false otherwise.
static int nd_increment(ND_IBOX *ibox, int ndims, int *counter)
Given an n-d index array (counter), and a domain to increment it in (ibox) increment it by one,...
static int gbox_ndims(const GBOX *gbox)
Given that geodetic boxes are X/Y/Z regardless of the underlying geometry dimensionality and other bo...
#define ND_DIMS
The maximum number of dimensions our code can handle.
#define FALLBACK_ND_SEL
More modest fallback selectivity factor.
static int nd_box_contains(const ND_BOX *a, const ND_BOX *b, int ndims)
Return true if ND_BOX a contains b, false otherwise.
static void nd_box_from_gbox(const GBOX *gbox, ND_BOX *nd_box)
Set the values of an ND_BOX from a GBOX.
static int nd_box_overlap(const ND_STATS *nd_stats, const ND_BOX *nd_box, ND_IBOX *nd_ibox)
What stats cells overlap with this ND_BOX? Put the lowest cell addresses in ND_IBOX->min and the high...
static double nd_box_ratio(const ND_BOX *b1, const ND_BOX *b2, int ndims)
Returns the proportion of b2 that is covered by b1.
static int nd_stats_value_index(const ND_STATS *stats, int *indexes)
Given a position in the n-d histogram (i,j,k) return the position in the 1-d values array.
static char * nd_box_to_json(const ND_BOX *nd_box, int ndims)
Convert an ND_BOX to a JSON string for printing.
float4 max[ND_DIMS]
float4 min[ND_DIMS]
N-dimensional box type for calculations, to avoid doing explicit axis conversions from GBOX in all ca...
int min[ND_DIMS]
N-dimensional box index type.
float4 size[ND_DIMS]

References ND_STATS_T::extent, FALLBACK_ND_SEL, gbox_ndims(), ND_STATS_T::histogram_cells, ND_STATS_T::histogram_features, ND_BOX_T::max, ND_BOX_T::min, ND_IBOX_T::min, nd_box_contains(), nd_box_from_gbox(), nd_box_intersects(), nd_box_overlap(), nd_box_ratio(), nd_box_to_json(), ND_DIMS, nd_increment(), nd_stats_value_index(), ND_STATS_T::ndims, ND_STATS_T::size, and ND_STATS_T::value.

Referenced by _postgis_gserialized_sel(), and gserialized_sel_internal().

Here is the call graph for this function:
Here is the caller graph for this function: