64 #include "access/genam.h"
65 #include "access/gin.h"
66 #include "access/gist.h"
67 #include "access/gist_private.h"
68 #include "access/gistscan.h"
69 #if PG_VERSION_NUM < 130000
70 #include "access/tuptoaster.h"
72 #include "access/detoast.h"
74 #include "utils/datum.h"
75 #include "access/heapam.h"
76 #include "catalog/index.h"
77 #include "catalog/pg_am.h"
78 #include "miscadmin.h"
79 #include "storage/lmgr.h"
80 #include "catalog/namespace.h"
81 #include "catalog/indexing.h"
82 #if PG_VERSION_NUM >= 100000
83 #include "utils/regproc.h"
84 #include "utils/varlena.h"
86 #include "utils/builtins.h"
87 #include "utils/datum.h"
88 #include "utils/snapmgr.h"
89 #include "utils/fmgroids.h"
91 #include "access/heapam.h"
92 #include "catalog/pg_type.h"
93 #include "access/relscan.h"
95 #include "executor/spi.h"
97 #include "commands/vacuum.h"
98 #if PG_VERSION_NUM < 120000
99 #include "nodes/relation.h"
101 #include "nodes/pathnodes.h"
103 #include "parser/parsetree.h"
104 #include "utils/array.h"
105 #include "utils/lsyscache.h"
106 #include "utils/builtins.h"
107 #include "utils/syscache.h"
108 #include "utils/rel.h"
109 #include "utils/selfuncs.h"
111 #include "../postgis_config.h"
113 #include "access/htup_details.h"
117 #include "lwgeom_pg.h"
118 #include "gserialized_gist.h"
170 #define STATISTIC_KIND_ND 102
171 #define STATISTIC_KIND_2D 103
181 #define STATISTIC_SLOT_ND 3
182 #define STATISTIC_SLOT_2D 4
190 #define SDFACTOR 3.25
205 #define MIN_DIMENSION_WIDTH 0.000000001
211 #define MAX_DIMENSION_WIDTH 1.0E+20
216 #define DEFAULT_ND_SEL 0.0001
217 #define DEFAULT_ND_JOINSEL 0.001
222 #define FALLBACK_ND_SEL 0.2
223 #define FALLBACK_ND_JOINSEL 0.3
322 if (VARSIZE_ANY_EXHDR(txt) <= 0)
324 modestr = (
char*)VARDATA(txt);
325 if ( modestr[0] ==
'N' )
337 int ia = *((
const int*)a);
338 int ib = *((
const int*)b);
355 qsort(vals, nvals,
sizeof(
int),
cmp_int);
356 return vals[4*nvals/5] - vals[nvals/5];
368 for ( i = 0; i < nvals; i++ )
374 #if POSTGIS_DEBUG_LEVEL >= 3
380 total_int(
const int *vals,
int nvals)
385 for ( i = 0; i < nvals; i++ )
395 avg(
const int *vals,
int nvals)
397 int t = total_int(vals, nvals);
398 return (
double)t / (double)nvals;
405 stddev(
const int *vals,
int nvals)
409 double mean = avg(vals, nvals);
412 for ( i = 0; i < nvals; i++ )
414 double v = (double)(vals[i]);
415 sigma2 += (mean - v) * (mean - v);
417 return sqrt(sigma2 / nvals);
429 int accum = 1, vdx = 0;
434 for ( d = 0; d < (int)(stats->
ndims); d++ )
436 int size = (int)(stats->
size[d]);
437 if ( indexes[d] < 0 || indexes[d] >= size )
439 POSTGIS_DEBUGF(3,
" bad index at (%d, %d)", indexes[0], indexes[1]);
442 vdx += indexes[d] * accum;
459 for ( i = 0; i < ndims; i++ )
465 for ( i = 0; i < ndims; i++ )
485 char *json_extent, *
str;
488 int ndims = (int)roundf(nd_stats->
ndims);
495 for ( d = 0; d < ndims; d++ )
555 for ( d = 0; d <
ND_DIMS; d++ )
557 target->
min[d] = Min(target->
min[d], source->
min[d]);
558 target->
max[d] = Max(target->
max[d], source->
max[d]);
567 memset(a, 0,
sizeof(
ND_BOX));
580 for ( d = 0; d <
ND_DIMS; d++ )
583 a->
max[d] = -1 * FLT_MAX;
630 for ( d = 0; d < ndims; d++ )
632 if ( (a->
min[d] > b->
max[d]) || (a->
max[d] < b->
min[d]) )
645 for ( d = 0; d < ndims; d++ )
647 if ( ! ((a->
min[d] < b->
min[d]) && (a->
max[d] > b->
max[d])) )
662 for ( d = 0; d <
ND_DIMS; d++ )
664 size = nd_box->
max[d] - nd_box->
min[d];
668 nd_box->
min[d] -= size * expansion_factor / 2;
669 nd_box->
max[d] += size * expansion_factor / 2;
686 memset(nd_ibox, 0,
sizeof(
ND_IBOX));
689 for ( d = 0; d < nd_stats->
ndims; d++ )
693 double width = smax - smin;
701 int size = (int)roundf(nd_stats->
size[d]);
704 nd_ibox->
min[d] = floor(size * (nd_box->
min[d] - smin) / width);
705 nd_ibox->
max[d] = floor(size * (nd_box->
max[d] - smin) / width);
707 POSTGIS_DEBUGF(5,
" stats: dim %d: min %g: max %g: width %g", d, smin, smax, width);
708 POSTGIS_DEBUGF(5,
" overlap: dim %d: (%d, %d)", d, nd_ibox->
min[d], nd_ibox->
max[d]);
711 nd_ibox->
min[d] = Max(nd_ibox->
min[d], 0);
712 nd_ibox->
max[d] = Min(nd_ibox->
max[d], size - 1);
732 for ( d = 0 ; d < ndims; d++ )
734 if ( b1->
max[d] <= b2->
min[d] || b1->
min[d] >= b2->
max[d] )
737 if ( b1->
min[d] > b2->
min[d] || b1->
max[d] < b2->
max[d] )
744 for ( d = 0; d < ndims; d++ )
746 double width1 = b1->
max[d] - b1->
min[d];
747 double width2 = b2->
max[d] - b2->
min[d];
748 double imin, imax, iwidth;
753 imin = Max(b1->
min[d], b2->
min[d]);
754 imax = Min(b1->
max[d], b2->
max[d]);
755 iwidth = imax - imin;
756 iwidth = Max(0.0, iwidth);
792 #if POSTGIS_DEBUG_LEVEL >= 3
793 double average, sdev, sdev_ratio;
799 for ( d = 0; d < ndims; d++ )
802 memset(counts, 0,
sizeof(counts));
804 smin = extent->
min[d];
805 smax = extent->
max[d];
806 swidth = smax - smin;
820 for ( i = 0; i < num_boxes; i++ )
822 double minoffset, maxoffset;
826 if ( ! ndb )
continue;
829 minoffset = ndb->
min[d] - smin;
830 maxoffset = ndb->
max[d] - smin;
833 if ( minoffset < 0 || minoffset > swidth ||
834 maxoffset < 0 || maxoffset > swidth )
840 bmin = floor(
NUM_BINS * minoffset / swidth);
841 bmax = floor(
NUM_BINS * maxoffset / swidth);
847 POSTGIS_DEBUGF(4,
" dimension %d, feature %d: bin %d to bin %d", d, i, bmin, bmax);
850 for ( k = bmin; k <= bmax; k++ )
860 #if POSTGIS_DEBUG_LEVEL >= 3
863 sdev_ratio = sdev/average;
865 POSTGIS_DEBUGF(3,
" dimension %d: range = %d", d, range);
866 POSTGIS_DEBUGF(3,
" dimension %d: average = %.6g", d, average);
867 POSTGIS_DEBUGF(3,
" dimension %d: stddev = %.6g", d, sdev);
868 POSTGIS_DEBUGF(3,
" dimension %d: stddev_ratio = %.6g", d, sdev_ratio);
871 distribution[d] = range;
889 if ( counter[d] < ibox->
max[d] )
894 counter[d] = ibox->
min[d];
917 #if POSTGIS_PGSQL_VERSION < 100
922 rv = get_attstatsslot(stats_tuple, 0, 0, stats_kind, InvalidOid,
923 NULL, NULL, NULL, &floatptr, &nvalues);
926 POSTGIS_DEBUGF(2,
"no slot of kind %d in stats tuple", stats_kind);
931 nd_stats = palloc(
sizeof(
float) * nvalues);
932 memcpy(nd_stats, floatptr,
sizeof(
float) * nvalues);
935 free_attstatsslot(0, NULL, 0, floatptr, nvalues);
940 rv = get_attstatsslot(&sslot, stats_tuple, stats_kind, InvalidOid,
941 ATTSTATSSLOT_NUMBERS);
943 POSTGIS_DEBUGF(2,
"no slot of kind %d in stats tuple", stats_kind);
948 nd_stats = palloc(
sizeof(float4) * sslot.nnumbers);
949 memcpy(nd_stats, sslot.numbers,
sizeof(float4) * sslot.nnumbers);
951 free_attstatsslot(&sslot);
965 HeapTuple stats_tuple = NULL;
971 POSTGIS_DEBUGF(2,
"searching whole tree stats for \"%s\"", get_rel_name(table_oid)? get_rel_name(table_oid) :
"NULL");
972 stats_tuple = SearchSysCache3(STATRELATTINH, ObjectIdGetDatum(table_oid), Int16GetDatum(att_num), BoolGetDatum(
true));
974 POSTGIS_DEBUGF(2,
"found whole tree stats for \"%s\"", get_rel_name(table_oid)? get_rel_name(table_oid) :
"NULL");
977 if ( only_parent || ! stats_tuple )
979 POSTGIS_DEBUGF(2,
"searching parent table stats for \"%s\"", get_rel_name(table_oid)? get_rel_name(table_oid) :
"NULL");
980 stats_tuple = SearchSysCache3(STATRELATTINH, ObjectIdGetDatum(table_oid), Int16GetDatum(att_num), BoolGetDatum(
false));
982 POSTGIS_DEBUGF(2,
"found parent table stats for \"%s\"", get_rel_name(table_oid)? get_rel_name(table_oid) :
"NULL");
986 POSTGIS_DEBUGF(2,
"stats for \"%s\" do not exist", get_rel_name(table_oid)? get_rel_name(table_oid) :
"NULL");
991 ReleaseSysCache(stats_tuple);
995 "histogram for attribute %d of table \"%s\" does not exist?",
996 att_num, get_rel_name(table_oid));
1013 const char *att_name = text_to_cstring(att_text);
1020 att_num = get_attnum(table_oid, att_name);
1022 elog(ERROR,
"attribute \"%s\" does not exist", att_name);
1028 elog(ERROR,
"attribute name is null");
1051 int ncells1, ncells2;
1052 int ndims1, ndims2, ndims;
1054 double ntuples_not_null1, ntuples_not_null2;
1073 if ( ! ( s1 && s2 ) )
1075 elog(NOTICE,
" estimate_join_selectivity called with null inputs");
1084 if ( ncells1 > ncells2 )
1102 ntuples_max = ntuples_not_null1 * ntuples_not_null2;
1105 ndims1 = (int)roundf(s1->
ndims);
1106 ndims2 = (int)roundf(s2->
ndims);
1107 ndims = Max(ndims1, ndims2);
1116 POSTGIS_DEBUG(3,
"relation stats do not intersect, returning 0");
1117 PG_RETURN_FLOAT8(0.0);
1126 POSTGIS_DEBUG(3,
"could not calculate overlap of relations");
1131 for ( d = 0; d < ndims1; d++ )
1133 at1[d] = ibox1.
min[d];
1136 size1[d] = (int)roundf(s1->
size[d]);
1137 cellsize1[d] = width1[d] / size1[d];
1141 for ( d = 0; d < ndims2; d++ )
1145 size2[d] = (int)roundf(s2->
size[d]);
1146 cellsize2[d] = width2[d] / size2[d];
1156 for ( d = 0; d < ndims1; d++ )
1158 nd_cell1.
min[d] = min1[d] + (at1[d]+0) * cellsize1[d];
1159 nd_cell1.
max[d] = min1[d] + (at1[d]+1) * cellsize1[d];
1166 for ( d = 0; d < ndims2; d++ )
1168 at2[d] = ibox2.
min[d];
1171 POSTGIS_DEBUGF(3,
"at1 %d,%d %s", at1[0], at1[1],
nd_box_to_json(&nd_cell1, ndims1));
1185 for ( d = 0; d < ndims2; d++ )
1187 nd_cell2.
min[d] = min2[d] + (at2[d]+0) * cellsize2[d];
1188 nd_cell2.
max[d] = min2[d] + (at2[d]+1) * cellsize2[d];
1191 POSTGIS_DEBUGF(3,
" at2 %d,%d %s", at2[0], at2[1],
nd_box_to_json(&nd_cell2, ndims2));
1194 ratio2 =
nd_box_ratio(&nd_cell1, &nd_cell2, Max(ndims1, ndims2));
1198 POSTGIS_DEBUGF(3,
" val1 %.6g val2 %.6g ratio %.6g", val1, val2, ratio2);
1199 val += val1 * (val2 * ratio2);
1206 POSTGIS_DEBUGF(3,
"val of histogram = %g", val);
1217 POSTGIS_DEBUGF(3,
"val scaled to full table size = %g", val);
1234 selectivity = val / ntuples_max;
1237 if ( isnan(selectivity) || ! isfinite(selectivity) || selectivity < 0.0 )
1241 else if ( selectivity > 1.0 )
1256 PG_RETURN_DATUM(DirectFunctionCall5(
1258 PG_GETARG_DATUM(0), PG_GETARG_DATUM(1),
1259 PG_GETARG_DATUM(2), PG_GETARG_DATUM(3),
1271 PG_RETURN_DATUM(DirectFunctionCall5(
1273 PG_GETARG_DATUM(0), PG_GETARG_DATUM(1),
1274 PG_GETARG_DATUM(2), PG_GETARG_DATUM(3),
1285 Node *arg1 = (Node*) linitial(
args);
1286 Node *arg2 = (Node*) lsecond(
args);
1287 Var *var1 = (Var*) arg1;
1288 Var *var2 = (Var*) arg2;
1290 POSTGIS_DEBUGF(2,
"%s: entered function", __func__);
1294 if (!IsA(arg1, Var) || !IsA(arg2, Var))
1296 POSTGIS_DEBUGF(1,
"%s called with arguments that are not column references", __func__);
1301 relid1 = rt_fetch(var1->varno, root->parse->rtable)->relid;
1302 relid2 = rt_fetch(var2->varno, root->parse->rtable)->relid;
1311 POSTGIS_DEBUGF(2,
"%s: cannot find stats for \"%s\"", __func__, get_rel_name(relid2) ? get_rel_name(relid2) :
"NULL");
1316 POSTGIS_DEBUGF(2,
"%s: cannot find stats for \"%s\"", __func__, get_rel_name(relid2) ? get_rel_name(relid2) :
"NULL");
1321 POSTGIS_DEBUGF(2,
"got selectivity %g", selectivity);
1338 PlannerInfo *root = (PlannerInfo *) PG_GETARG_POINTER(0);
1340 List *
args = (List *) PG_GETARG_POINTER(2);
1341 JoinType jointype = (JoinType) PG_GETARG_INT16(3);
1342 int mode = PG_GETARG_INT32(4);
1344 POSTGIS_DEBUGF(2,
"%s: entered function", __func__);
1347 if (list_length(
args) != 2)
1349 POSTGIS_DEBUGF(2,
"%s: got nargs == %d", __func__, list_length(
args));
1354 if (jointype != JOIN_INNER)
1356 POSTGIS_DEBUGF(1,
"%s: jointype %d not supported", __func__, jointype);
1383 int sample_rows,
double total_rows,
int mode)
1385 MemoryContext old_context;
1387 int notnull_cnt = 0;
1389 int histogram_features = 0;
1392 size_t nd_stats_size;
1394 double total_width = 0;
1395 double total_sample_volume = 0;
1396 double total_cell_count = 0;
1402 const ND_BOX **sample_boxes;
1407 int histo_cells_target;
1409 int histo_cells_new = 1;
1412 int histo_ndims = 0;
1413 double sample_distribution[
ND_DIMS];
1414 double total_distribution;
1419 (void)total_sample_volume;
1434 POSTGIS_DEBUG(2,
"compute_gserialized_stats called");
1435 POSTGIS_DEBUGF(3,
" # sample_rows: %d", sample_rows);
1436 POSTGIS_DEBUGF(3,
" estimate of total_rows: %.6g", total_rows);
1442 sample_boxes = palloc(
sizeof(
ND_BOX*) * sample_rows);
1454 for ( i = 0; i < sample_rows; i++ )
1461 datum = fetchfunc(stats, i, &is_null);
1466 POSTGIS_DEBUGF(4,
" skipped null geometry %d", i);
1475 POSTGIS_DEBUGF(3,
" skipped empty geometry %d", i);
1486 POSTGIS_DEBUGF(3,
" skipped infinite/nan geometry %d", i);
1498 nd_box = palloc(
sizeof(
ND_BOX));
1502 sample_boxes[notnull_cnt] = nd_box;
1505 if ( ! notnull_cnt )
1512 total_width += toast_raw_datum_size(datum);
1515 for ( d = 0; d < ndims; d++ )
1517 sum.
min[d] += nd_box->
min[d];
1518 sum.
max[d] += nd_box->
max[d];
1525 vacuum_delay_point();
1536 histo_cells_target = (int)pow((
double)(stats->attr->attstattarget), (
double)ndims);
1537 histo_cells_target = Min(histo_cells_target, ndims * 10000);
1538 histo_cells_target = Min(histo_cells_target, (
int)(total_rows/5));
1539 POSTGIS_DEBUGF(3,
" stats->attr->attstattarget: %d", stats->attr->attstattarget);
1540 POSTGIS_DEBUGF(3,
" target # of histogram cells: %d", histo_cells_target);
1543 if ( ! notnull_cnt )
1545 Oid relation_oid = stats->attr->attrelid;
1546 char *relation_name = get_rel_name(relation_oid);
1548 "PostGIS: Unable to compute statistics for \"%s.%s\": No non-null/empty features",
1549 relation_name ? relation_name :
"(NULL)",
1550 stats->attr->attname.data);
1551 stats->stats_valid =
false;
1555 POSTGIS_DEBUGF(3,
" sample_extent: %s",
nd_box_to_json(&sample_extent, ndims));
1561 for ( d = 0; d < ndims; d++ )
1564 avg.
min[d] = sum.
min[d] / notnull_cnt;
1565 avg.
max[d] = sum.
max[d] / notnull_cnt;
1568 for ( i = 0; i < notnull_cnt; i++ )
1570 const ND_BOX *ndb = sample_boxes[i];
1571 stddev.
min[d] += (ndb->
min[d] - avg.
min[d]) * (ndb->
min[d] - avg.
min[d]);
1572 stddev.
max[d] += (ndb->
max[d] - avg.
max[d]) * (ndb->
max[d] - avg.
max[d]);
1574 stddev.
min[d] = sqrt(stddev.
min[d] / notnull_cnt);
1575 stddev.
max[d] = sqrt(stddev.
max[d] / notnull_cnt);
1588 for ( i = 0; i < notnull_cnt; i++ )
1590 const ND_BOX *ndb = sample_boxes[i];
1594 POSTGIS_DEBUGF(4,
" feature %d is a hard deviant, skipped", i);
1595 sample_boxes[i] = NULL;
1606 histo_extent = histo_extent_new;
1621 sample_distribution);
1637 for ( d = 0; d < ndims; d++ )
1639 if ( sample_distribution[d] > 0 )
1643 if ( histo_ndims == 0 )
1647 POSTGIS_DEBUG(3,
" special case: no axes have variability");
1648 histo_cells_new = 1;
1649 for ( d = 0; d < ndims; d++ )
1651 histo_size[d] = (int)pow((
double)histo_cells_target, 1/(double)ndims);
1652 if ( ! histo_size[d] )
1654 POSTGIS_DEBUGF(3,
" histo_size[d]: %d", histo_size[d]);
1655 histo_cells_new *= histo_size[d];
1657 POSTGIS_DEBUGF(3,
" histo_cells_new: %d", histo_cells_new);
1666 POSTGIS_DEBUG(3,
" allocating histogram axes based on axis variability");
1667 total_distribution =
total_double(sample_distribution, ndims);
1668 POSTGIS_DEBUGF(3,
" total_distribution: %.8g", total_distribution);
1669 histo_cells_new = 1;
1670 for ( d = 0; d < ndims; d++ )
1672 if ( sample_distribution[d] == 0 )
1679 float edge_ratio = (float)sample_distribution[d] / (
float)total_distribution;
1685 histo_size[d] = (int)pow(histo_cells_target * histo_ndims * edge_ratio, 1/(
double)histo_ndims);
1687 if ( ! histo_size[d] )
1690 histo_cells_new *= histo_size[d];
1692 POSTGIS_DEBUGF(3,
" histo_cells_new: %d", histo_cells_new);
1696 histo_cells = histo_cells_new;
1697 POSTGIS_DEBUGF(3,
" histo_cells: %d", histo_cells);
1702 old_context = MemoryContextSwitchTo(stats->anl_context);
1703 nd_stats_size =
sizeof(
ND_STATS) + ((histo_cells - 1) *
sizeof(float4));
1704 nd_stats = palloc(nd_stats_size);
1705 memset(nd_stats, 0, nd_stats_size);
1706 MemoryContextSwitchTo(old_context);
1709 nd_stats->
ndims = ndims;
1710 nd_stats->
extent = histo_extent;
1715 for ( d = 0; d < ndims; d++ )
1716 nd_stats->
size[d] = histo_size[d];
1731 for ( i = 0; i < notnull_cnt; i++ )
1737 double num_cells = 0;
1738 double tmp_volume = 1.0;
1739 double min[
ND_DIMS] = {0.0, 0.0, 0.0, 0.0};
1740 double max[
ND_DIMS] = {0.0, 0.0, 0.0, 0.0};
1741 double cellsize[
ND_DIMS] = {0.0, 0.0, 0.0, 0.0};
1743 nd_box = sample_boxes[i];
1744 if ( ! nd_box )
continue;
1747 vacuum_delay_point();
1751 memset(at, 0,
sizeof(
int)*
ND_DIMS);
1753 POSTGIS_DEBUGF(3,
" feature %d: ibox (%d, %d, %d, %d) (%d, %d, %d, %d)", i,
1754 nd_ibox.
min[0], nd_ibox.
min[1], nd_ibox.
min[2], nd_ibox.
min[3],
1755 nd_ibox.
max[0], nd_ibox.
max[1], nd_ibox.
max[2], nd_ibox.
max[3]);
1757 for ( d = 0; d < nd_stats->
ndims; d++ )
1760 at[d] = nd_ibox.
min[d];
1763 cellsize[d] = (max[d] - min[d])/(nd_stats->
size[d]);
1766 tmp_volume *= (nd_box->
max[d] - nd_box->
min[d]);
1770 total_sample_volume += tmp_volume;
1778 ND_BOX nd_cell = { {0.0, 0.0, 0.0, 0.0}, {0.0, 0.0, 0.0, 0.0} };
1781 for ( d = 0; d < nd_stats->
ndims; d++ )
1783 nd_cell.
min[d] = min[d] + (at[d]+0) * cellsize[d];
1784 nd_cell.
max[d] = min[d] + (at[d]+1) * cellsize[d];
1795 POSTGIS_DEBUGF(3,
" ratio (%.8g) num_cells (%.8g)", ratio, num_cells);
1796 POSTGIS_DEBUGF(3,
" at (%d, %d, %d, %d)", at[0], at[1], at[2], at[3]);
1801 total_cell_count += num_cells;
1803 histogram_features++;
1806 POSTGIS_DEBUGF(3,
" histogram_features: %d", histogram_features);
1807 POSTGIS_DEBUGF(3,
" sample_rows: %d", sample_rows);
1808 POSTGIS_DEBUGF(3,
" table_rows: %.6g", total_rows);
1811 if ( ! histogram_features )
1813 POSTGIS_DEBUG(3,
" no stats have been gathered");
1814 elog(NOTICE,
" no features lie in the stats histogram, invalid stats");
1815 stats->stats_valid =
false;
1836 stats->stakind[stats_slot] = stats_kind;
1837 stats->staop[stats_slot] = InvalidOid;
1838 stats->stanumbers[stats_slot] = (float4*)nd_stats;
1839 stats->numnumbers[stats_slot] = nd_stats_size/
sizeof(float4);
1840 stats->stanullfrac = (float4)null_cnt/sample_rows;
1841 stats->stawidth = total_width/notnull_cnt;
1842 stats->stadistinct = -1.0;
1843 stats->stats_valid =
true;
1845 POSTGIS_DEBUGF(3,
" out: slot 0: kind %d (STATISTIC_KIND_ND)", stats->stakind[0]);
1846 POSTGIS_DEBUGF(3,
" out: slot 0: op %d (InvalidOid)", stats->staop[0]);
1847 POSTGIS_DEBUGF(3,
" out: slot 0: numnumbers %d", stats->numnumbers[0]);
1848 POSTGIS_DEBUGF(3,
" out: null fraction: %f=%d/%d", stats->stanullfrac, null_cnt, sample_rows);
1849 POSTGIS_DEBUGF(3,
" out: average width: %d bytes", stats->stawidth);
1850 POSTGIS_DEBUG (3,
" out: distinct values: all (no check done)");
1879 int sample_rows,
double total_rows)
1885 stats->extra_data = extra_data;
1890 if (stats->stats_valid)
1927 VacAttrStats *stats = (VacAttrStats *)PG_GETARG_POINTER(0);
1932 if (!std_typanalyze(stats))
1933 PG_RETURN_BOOL(
false);
1940 stats->extra_data = extra_data;
1943 PG_RETURN_BOOL(
true);
1969 double total_count = 0.0;
1975 elog(NOTICE,
" estimate_selectivity called with null input");
1989 POSTGIS_DEBUGF(3,
" mode: %d", mode);
1992 POSTGIS_DEBUG(3,
" in 2d mode, stripping the computation down to 2d");
2008 POSTGIS_DEBUG(3,
" search box does not overlap histogram, returning 0");
2015 POSTGIS_DEBUG(3,
" search box contains histogram, returning 1");
2022 POSTGIS_DEBUG(3,
" search box overlap with stats histogram failed");
2027 for ( d = 0; d < nd_stats->
ndims; 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]);
2036 at[d] = nd_ibox.
min[d];
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} };
2046 for ( d = 0; d < nd_stats->
ndims; d++ )
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];
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);
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);
2070 if (selectivity > 1.0) selectivity = 1.0;
2071 else if (selectivity < 0.0) selectivity = 0.0;
2085 Oid table_oid = PG_GETARG_OID(0);
2086 text *att_text = PG_GETARG_TEXT_P(1);
2091 bool only_parent =
false;
2094 if ( ! PG_ARGISNULL(2) )
2100 elog(ERROR,
"stats for \"%s.%s\" do not exist", get_rel_name(table_oid), text_to_cstring(att_text));
2104 json = cstring_to_text(
str);
2107 PG_RETURN_TEXT_P(json);
2118 Oid table_oid = PG_GETARG_OID(0);
2119 text *att_text = PG_GETARG_TEXT_P(1);
2120 Datum geom_datum = PG_GETARG_DATUM(2);
2122 float8 selectivity = 0;
2127 if ( ! PG_ARGISNULL(3) )
2134 elog(ERROR,
"stats for \"%s.%s\" do not exist", get_rel_name(table_oid), text_to_cstring(att_text));
2138 elog(ERROR,
"unable to calculate bounding box from geometry");
2146 PG_RETURN_FLOAT8(selectivity);
2157 Oid table_oid1 = PG_GETARG_OID(0);
2158 text *att_text1 = PG_GETARG_TEXT_P(1);
2159 Oid table_oid2 = PG_GETARG_OID(2);
2160 text *att_text2 = PG_GETARG_TEXT_P(3);
2162 float8 selectivity = 0;
2171 elog(ERROR,
"stats for \"%s.%s\" do not exist", get_rel_name(table_oid1), text_to_cstring(att_text1));
2174 elog(ERROR,
"stats for \"%s.%s\" do not exist", get_rel_name(table_oid2), text_to_cstring(att_text2));
2177 if ( ! PG_ARGISNULL(4) )
2179 text *modetxt = PG_GETARG_TEXT_P(4);
2180 char *modestr = text_to_cstring(modetxt);
2181 if ( modestr[0] ==
'N' )
2190 PG_RETURN_FLOAT8(selectivity);
2200 PG_RETURN_DATUM(DirectFunctionCall5(
2202 PG_GETARG_DATUM(0), PG_GETARG_DATUM(1),
2203 PG_GETARG_DATUM(2), PG_GETARG_DATUM(3),
2215 PG_RETURN_DATUM(DirectFunctionCall5(
2217 PG_GETARG_DATUM(0), PG_GETARG_DATUM(1),
2218 PG_GETARG_DATUM(2), PG_GETARG_DATUM(3),
2241 VariableStatData vardata;
2247 float8 selectivity = 0;
2250 POSTGIS_DEBUGF(2,
"%s: entered function", __func__);
2252 if (!get_restriction_variable(root,
args, varRelid, &vardata, &other, &varonleft))
2254 POSTGIS_DEBUGF(2,
"%s: could not find vardata", __func__);
2258 if (!IsA(other, Const))
2260 ReleaseVariableStats(vardata);
2261 POSTGIS_DEBUGF(2,
"%s: no constant argument, returning default selectivity %g", __func__,
DEFAULT_ND_SEL);
2265 otherConst = (Const*)other;
2266 if ((!otherConst) || otherConst->constisnull)
2268 ReleaseVariableStats(vardata);
2269 POSTGIS_DEBUGF(2,
"%s: constant argument is NULL", __func__);
2275 ReleaseVariableStats(vardata);
2276 POSTGIS_DEBUGF(2,
"%s: search box is EMPTY", __func__);
2280 if (!vardata.statsTuple)
2282 POSTGIS_DEBUGF(1,
"%s: no statistics available on table. Empty? Need to ANALYZE?", __func__);
2287 ReleaseVariableStats(vardata);
2289 if (nd_stats) pfree(nd_stats);
2296 PlannerInfo *root = (PlannerInfo *) PG_GETARG_POINTER(0);
2298 List *
args = (List *) PG_GETARG_POINTER(2);
2299 int varRelid = PG_GETARG_INT32(3);
2300 int mode = PG_GETARG_INT32(4);
2302 POSTGIS_DEBUGF(2,
"%s: selectivity is %g", __func__, selectivity);
2303 PG_RETURN_FLOAT8(selectivity);
2319 char *nsp_tbl = NULL;
2320 Oid tbl_oid, idx_oid = 0;
2323 bool only_parent =
false;
2324 int key_type, att_num;
2328 postgis_initialize_cache();
2330 if ( PG_NARGS() == 4 )
2332 nsp = text_to_cstring(PG_GETARG_TEXT_P(0));
2333 tbl = text_to_cstring(PG_GETARG_TEXT_P(1));
2334 col = PG_GETARG_TEXT_P(2);
2335 only_parent = PG_GETARG_BOOL(3);
2336 sz = strlen(nsp) + strlen(tbl) + 6;
2337 nsp_tbl = palloc(sz);
2338 snprintf(nsp_tbl, sz,
"\"%s\".\"%s\"", nsp, tbl);
2339 tbl_oid = DatumGetObjectId(DirectFunctionCall1(regclassin, CStringGetDatum(nsp_tbl)));
2342 else if ( PG_NARGS() == 3 )
2344 nsp = text_to_cstring(PG_GETARG_TEXT_P(0));
2345 tbl = text_to_cstring(PG_GETARG_TEXT_P(1));
2346 col = PG_GETARG_TEXT_P(2);
2347 sz = strlen(nsp) + strlen(tbl) + 6;
2348 nsp_tbl = palloc(sz);
2349 snprintf(nsp_tbl, sz,
"\"%s\".\"%s\"", nsp, tbl);
2350 tbl_oid = DatumGetObjectId(DirectFunctionCall1(regclassin, CStringGetDatum(nsp_tbl)));
2353 else if ( PG_NARGS() == 2 )
2355 tbl = text_to_cstring(PG_GETARG_TEXT_P(0));
2356 col = PG_GETARG_TEXT_P(1);
2357 sz = strlen(tbl) + 3;
2358 nsp_tbl = palloc(sz);
2359 snprintf(nsp_tbl, sz,
"\"%s\"", tbl);
2360 tbl_oid = DatumGetObjectId(DirectFunctionCall1(regclassin, CStringGetDatum(nsp_tbl)));
2365 elog(ERROR,
"estimated_extent() called with wrong number of arguments");
2376 POSTGIS_DEBUGF(2,
"index for \"%s.%s\" exists, reading gbox from there", tbl, text_to_cstring(col));
2377 if ( ! gbox ) PG_RETURN_NULL();
2381 POSTGIS_DEBUGF(2,
"index for \"%s.%s\" does not exist", tbl, text_to_cstring(col));
2390 elog(WARNING,
"stats for \"%s.%s\" do not exist", tbl, text_to_cstring(col));
2395 gbox = palloc(
sizeof(
GBOX));
2406 PG_RETURN_POINTER(gbox);
2418 if ( PG_NARGS() == 3 )
2424 PG_GETARG_DATUM(2)));
2426 else if ( PG_NARGS() == 2 )
2431 PG_GETARG_DATUM(1)));
2434 elog(ERROR,
"geometry_estimated_extent() called with wrong number of arguments");
2447 char *colname = text_to_cstring(col);
2450 Oid b2d_oid = postgis_oid(BOX2DFOID);
2451 Oid gdx_oid = postgis_oid(BOX3DOID);
2453 if (!(b2d_oid && gdx_oid))
2456 tbl_rel = RelationIdGetRelation(tbl_oid);
2457 idx_list = RelationGetIndexList(tbl_rel);
2458 RelationClose(tbl_rel);
2461 foreach(lc, idx_list)
2463 Form_pg_class idx_form;
2466 Oid idx_oid = lfirst_oid(lc);
2468 idx_tup = SearchSysCache1(RELOID, ObjectIdGetDatum(idx_oid));
2469 if (!HeapTupleIsValid(idx_tup))
2470 elog(ERROR,
"%s: unable to lookup index %u in syscache", __func__, idx_oid);
2471 idx_form = (Form_pg_class) GETSTRUCT(idx_tup);
2472 idx_relam = idx_form->relam;
2473 ReleaseSysCache(idx_tup);
2476 if (idx_relam == GIST_AM_OID)
2478 Form_pg_attribute att;
2482 HeapTuple att_tup = SearchSysCache2(ATTNAME,
2483 ObjectIdGetDatum(idx_oid),
2484 PointerGetDatum(colname));
2485 if (!HeapTupleIsValid(att_tup))
2488 att = (Form_pg_attribute) GETSTRUCT(att_tup);
2489 atttypid = att->atttypid;
2490 attnum = att->attnum;
2491 ReleaseSysCache(att_tup);
2494 if (b2d_oid == atttypid || gdx_oid == atttypid)
2512 BOX2DF *bounds_2df = NULL;
2513 GIDX *bounds_gidx = NULL;
2518 OffsetNumber offset;
2519 unsigned long offset_max;
2524 idx_rel = index_open(idx_oid, AccessShareLock);
2525 buffer = ReadBuffer(idx_rel, GIST_ROOT_BLKNO);
2526 page = (Page) BufferGetPage(
buffer);
2527 offset = FirstOffsetNumber;
2528 offset_max = PageGetMaxOffsetNumber(page);
2529 while (offset <= offset_max)
2531 ItemId iid = PageGetItemId(page, offset);
2536 index_close(idx_rel, AccessShareLock);
2539 ituple = (IndexTuple) PageGetItem(page, iid);
2540 if (!GistTupleIsInvalid(ituple))
2543 Datum idx_attr = index_getattr(ituple, att_num, idx_rel->rd_att, &isnull);
2548 BOX2DF *b = (BOX2DF*)DatumGetPointer(idx_attr);
2556 GIDX *b = (GIDX*)DatumGetPointer(idx_attr);
2568 index_close(idx_rel, AccessShareLock);
2582 gbox_from_gidx(bounds_gidx, gbox, 0);
2603 Oid tbl_oid = PG_GETARG_DATUM(0);
2604 text *col = PG_GETARG_TEXT_P(1);
2611 postgis_initialize_cache();
2621 PG_RETURN_POINTER(gbox);
char result[OUT_DOUBLE_BUFFER_SIZE]
GBOX * gbox_new(lwflags_t flags)
Create a new gbox with the dimensionality indicated by the flags.
int gbox_is_valid(const GBOX *gbox)
Return false if any of the dimensions is NaN or infinite.
char * gbox_to_string(const GBOX *gbox)
Allocate a string representation of the GBOX, based on dimensionality of flags.
static ND_STATS * pg_get_nd_stats_by_name(const Oid table_oid, const text *att_text, int mode, bool only_parent)
Pull the stats object from the PgSQL system catalogs.
struct ND_STATS_T ND_STATS
N-dimensional statistics structure.
static char * nd_stats_to_json(const ND_STATS *nd_stats)
Convert an ND_STATS to a JSON representation for external use.
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_box_init_bounds(ND_BOX *a)
Prepare an ND_BOX for bounds calculation: set the maxes to the smallest thing possible and the mins t...
Datum gserialized_gist_joinsel_2d(PG_FUNCTION_ARGS)
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 range_quintile(int *vals, int nvals)
The difference between the fourth and first quintile values, the "inter-quintile range".
#define STATISTIC_SLOT_ND
static int gbox_ndims(const GBOX *gbox)
Given that geodetic boxes are X/Y/Z regardless of the underlying geometry dimensionality and other bo...
Datum gserialized_gist_joinsel_nd(PG_FUNCTION_ARGS)
static float8 estimate_selectivity(const GBOX *box, const ND_STATS *nd_stats, int mode)
This function returns an estimate of the selectivity of a search GBOX by looking at data in the ND_ST...
static ND_STATS * pg_get_nd_stats(const Oid table_oid, AttrNumber att_num, int mode, bool only_parent)
Pull the stats object from the PgSQL system catalogs.
#define ND_DIMS
The maximum number of dimensions our code can handle.
#define STATISTIC_KIND_2D
static int nd_box_merge(const ND_BOX *source, ND_BOX *target)
Create a printable view of the ND_STATS histogram.
#define DEFAULT_ND_JOINSEL
#define STATISTIC_KIND_ND
#define FALLBACK_ND_SEL
More modest fallback selectivity factor.
PG_FUNCTION_INFO_V1(gserialized_gist_joinsel_nd)
For (geometry &&& geometry) and (geography && geography) we call into the N-D mode.
Datum gserialized_estimated_extent(PG_FUNCTION_ARGS)
#define DEFAULT_ND_SEL
Default geometry selectivity factor.
Datum _postgis_gserialized_joinsel(PG_FUNCTION_ARGS)
static double total_double(const double *vals, int nvals)
Given double array, return sum of values.
#define FALLBACK_ND_JOINSEL
static void compute_gserialized_stats_mode(VacAttrStats *stats, AnalyzeAttrFetchFunc fetchfunc, int sample_rows, double total_rows, int mode)
The gserialized_analyze_nd sets this function as a callback on the stats object when called by the AN...
static int cmp_int(const void *a, const void *b)
Integer comparison function for qsort.
static GBOX * spatial_index_read_extent(Oid idx_oid, int key_type, int att_num)
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 compute_gserialized_stats(VacAttrStats *stats, AnalyzeAttrFetchFunc fetchfunc, int sample_rows, double total_rows)
In order to do useful selectivity calculations in both 2-D and N-D modes, we actually have to generat...
static int text_p_get_mode(const text *txt)
Utility function to see if the first letter of the mode argument is 'N'.
Datum gserialized_gist_sel(PG_FUNCTION_ARGS)
float8 gserialized_joinsel_internal(PlannerInfo *root, List *args, JoinType jointype, int mode)
static ND_STATS * pg_nd_stats_from_tuple(HeapTuple stats_tuple, int mode)
static void nd_box_from_gbox(const GBOX *gbox, ND_BOX *nd_box)
Set the values of an ND_BOX from a GBOX.
float8 gserialized_sel_internal(PlannerInfo *root, List *args, int varRelid, int mode)
This function should return an estimation of the number of rows returned by a query involving an over...
Datum _postgis_gserialized_stats(PG_FUNCTION_ARGS)
static int nd_box_init(ND_BOX *a)
Zero out an ND_BOX.
Datum gserialized_gist_sel_nd(PG_FUNCTION_ARGS)
static Oid table_get_spatial_index(Oid tbl_oid, text *col, int *key_type, int *att_num)
struct ND_IBOX_T ND_IBOX
N-dimensional box index type.
#define MAX_DIMENSION_WIDTH
Maximum width of a dimension that we'll bother trying to compute statistics on.
Datum gserialized_analyze_nd(PG_FUNCTION_ARGS)
Datum _postgis_gserialized_sel(PG_FUNCTION_ARGS)
static int nd_box_expand(ND_BOX *nd_box, double expansion_factor)
Expand an ND_BOX ever so slightly.
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 float8 estimate_join_selectivity(const ND_STATS *s1, const ND_STATS *s2)
Given two statistics histograms, what is the selectivity of a join driven by the && or &&& operator?
Datum gserialized_gist_sel_2d(PG_FUNCTION_ARGS)
Datum _postgis_gserialized_index_extent(PG_FUNCTION_ARGS)
Datum gserialized_gist_joinsel(PG_FUNCTION_ARGS)
static char * nd_box_to_json(const ND_BOX *nd_box, int ndims)
Convert an ND_BOX to a JSON string for printing.
#define MIN_DIMENSION_WIDTH
Minimum width of a dimension that we'll bother trying to compute statistics on.
Datum geometry_estimated_extent(PG_FUNCTION_ARGS)
#define STATISTIC_SLOT_2D
struct ND_BOX_T ND_BOX
N-dimensional box type for calculations, to avoid doing explicit axis conversions from GBOX in all ca...
static int nd_box_array_distribution(const ND_BOX **nd_boxes, int num_boxes, const ND_BOX *extent, int ndims, double *distribution)
Calculate how much a set of boxes is homogenously distributed or contentrated within one dimension,...
void box2df_merge(BOX2DF *b_union, BOX2DF *b_new)
bool box2df_is_empty(const BOX2DF *a)
int box2df_to_gbox_p(BOX2DF *a, GBOX *box)
int gserialized_datum_get_gbox_p(Datum gsdatum, GBOX *gbox)
Given a GSERIALIZED datum, as quickly as possible (peaking into the top of the memory) return the gbo...
BOX2DF * box2df_copy(BOX2DF *b)
bool gidx_is_unknown(const GIDX *a)
GIDX * gidx_copy(GIDX *b)
void gidx_merge(GIDX **b_union, GIDX *b_new)
#define FLAGS_GET_Z(flags)
#define FLAGS_GET_M(flags)
#define FLAGS_SET_GEODETIC(flags, value)
#define FLAGS_SET_M(flags, value)
#define FLAGS_SET_Z(flags, value)
#define FLAGS_GET_GEODETIC(flags)
This library is the generic geometry handling section of PostGIS.
Datum buffer(PG_FUNCTION_ARGS)
int stringbuffer_aprintf(stringbuffer_t *s, const char *fmt,...)
Appends a formatted string to the current string buffer, using the format and argument list provided.
stringbuffer_t * stringbuffer_create(void)
Allocate a new stringbuffer_t.
void stringbuffer_destroy(stringbuffer_t *s)
Free the stringbuffer_t and all memory managed within it.
char * stringbuffer_getstringcopy(stringbuffer_t *s)
Returns a newly allocated string large enough to contain the current state of the string.
static void stringbuffer_append(stringbuffer_t *s, const char *a)
Append the specified string to the stringbuffer_t.
N-dimensional box type for calculations, to avoid doing explicit axis conversions from GBOX in all ca...
N-dimensional box index type.
float4 histogram_features
N-dimensional statistics structure.