(2011/11/07 20:26), Shigeru Hanada wrote:
> (2011/10/20 18:56), Etsuro Fujita wrote:
>> I revised the patch according to Hanada-san's comments. Attached is the
>> updated version of the patch.
>>
>> Changes:
>>
>> * pull up of logging "analyzing foo.bar"
>> * new vac_update_relstats always called
>> * tab-completion in psql
>> * add "foreign tables are not analyzed automatically..." to 23.1.3
>> Updating Planner Statistics
>> * some other modifications
>
> Submission review
> =================
>
> - Patch can be applied, and all regression tests passed. :)
Thank you for your testing. I updated the patch according to your
comments. Attached is the updated version of the patch.
> - file_fdw_do_analyze_rel is almost copy of do_analyze_rel. IIUC,
> difference against do_analyze_rel are:
> * don't logging analyze target
> * don't switch userid to the owner of target table
> * don't measure elapsed time for autoanalyze deamon
> * don't handle index
> * some comments are removed.
> * sample rows are acquired by file_fdw's routine
>
> I don't see any problem here, but would you confirm that all of them are
> intentional?
Yes. But in the updated version, I've refactored analyze.c a little bit
to allow FDW authors to simply call do_analyze_rel().
> - In your design, each FDW have to copy most of do_analyze_rel to their
> own source. It means that FDW authors must know much details of ANALYZE
> to implement ANALYZE handler. Actually, your patch exports some static
> functions from analyze.c. Have you considered hooking
> acquire_sample_rows()? Such handler should be more simple, and
> FDW-specific. As you say, such design requires FDWs to skip some
> records, but it would be difficult for some FDW (e.g. twitter_fdw) which
> can't pick sample data up easily. IMHO such problem *must* be solved by
> FDW itself.
The updated version enables FDW authors to just write their own
acquire_sample_rows(). On the other hand, by retaining to hook
AnalyzeForeignTable routine at analyze_rel(), higher level than
acquire_sample_rows() as before, it allows FDW authors to write
AnalyzeForeignTable routine for foreign tables on a remote server to ask
the server for its current stats instead, as pointed out earlier by Tom
Lane.
Best regards,
Etsuro Fujita
*** a/contrib/file_fdw/file_fdw.c
--- b/contrib/file_fdw/file_fdw.c
***************
*** 16,31 ****
#include <unistd.h>
#include "access/reloptions.h"
#include "catalog/pg_foreign_table.h"
#include "commands/copy.h"
#include "commands/defrem.h"
#include "commands/explain.h"
#include "foreign/fdwapi.h"
#include "foreign/foreign.h"
#include "miscadmin.h"
#include "nodes/makefuncs.h"
#include "optimizer/cost.h"
! #include "utils/rel.h"
#include "utils/syscache.h"
PG_MODULE_MAGIC;
--- 16,36 ----
#include <unistd.h>
#include "access/reloptions.h"
+ #include "access/transam.h"
#include "catalog/pg_foreign_table.h"
#include "commands/copy.h"
#include "commands/defrem.h"
#include "commands/explain.h"
+ #include "commands/vacuum.h"
#include "foreign/fdwapi.h"
#include "foreign/foreign.h"
#include "miscadmin.h"
#include "nodes/makefuncs.h"
#include "optimizer/cost.h"
! #include "parser/parse_relation.h"
! #include "pgstat.h"
! #include "utils/attoptcache.h"
! #include "utils/memutils.h"
#include "utils/syscache.h"
PG_MODULE_MAGIC;
***************
*** 101,106 **** static void fileBeginForeignScan(ForeignScanState *node, int
eflags);
--- 106,112 ----
static TupleTableSlot *fileIterateForeignScan(ForeignScanState *node);
static void fileReScanForeignScan(ForeignScanState *node);
static void fileEndForeignScan(ForeignScanState *node);
+ static void fileAnalyzeForeignTable(Relation onerel, VacuumStmt *vacstmt, int
elevel);
/*
* Helper functions
***************
*** 112,118 **** static List *get_file_fdw_attribute_options(Oid relid);
static void estimate_costs(PlannerInfo *root, RelOptInfo *baserel,
const char *filename,
Cost *startup_cost, Cost *total_cost);
!
/*
* Foreign-data wrapper handler function: return a struct with pointers
--- 118,127 ----
static void estimate_costs(PlannerInfo *root, RelOptInfo *baserel,
const char *filename,
Cost *startup_cost, Cost *total_cost);
! static int acquire_sample_rows(Relation onerel,
! HeapTuple *rows, int targrows,
! double *totalrows, double *totaldeadrows,
! BlockNumber *totalpages, int elevel);
/*
* Foreign-data wrapper handler function: return a struct with pointers
***************
*** 129,134 **** file_fdw_handler(PG_FUNCTION_ARGS)
--- 138,144 ----
fdwroutine->IterateForeignScan = fileIterateForeignScan;
fdwroutine->ReScanForeignScan = fileReScanForeignScan;
fdwroutine->EndForeignScan = fileEndForeignScan;
+ fdwroutine->AnalyzeForeignTable = fileAnalyzeForeignTable;
PG_RETURN_POINTER(fdwroutine);
}
***************
*** 575,580 **** fileReScanForeignScan(ForeignScanState *node)
--- 585,602 ----
}
/*
+ * fileAnalyzeForeignTable
+ * Analyze table
+ */
+ static void
+ fileAnalyzeForeignTable(Relation onerel, VacuumStmt *vacstmt, int elevel)
+ {
+ do_analyze_rel(onerel, vacstmt,
+ elevel, false,
+ acquire_sample_rows);
+ }
+
+ /*
* Estimate costs of scanning a foreign table.
*/
static void
***************
*** 584,590 **** estimate_costs(PlannerInfo *root, RelOptInfo *baserel,
{
struct stat stat_buf;
BlockNumber pages;
! int tuple_width;
double ntuples;
double nrows;
Cost run_cost = 0;
--- 606,613 ----
{
struct stat stat_buf;
BlockNumber pages;
! BlockNumber relpages;
! double reltuples;
double ntuples;
double nrows;
Cost run_cost = 0;
***************
*** 604,619 **** estimate_costs(PlannerInfo *root, RelOptInfo *baserel,
if (pages < 1)
pages = 1;
! /*
! * Estimate the number of tuples in the file. We back into this
estimate
! * using the planner's idea of the relation width; which is bogus if not
! * all columns are being read, not to mention that the text
representation
! * of a row probably isn't the same size as its internal representation.
! * FIXME later.
! */
! tuple_width = MAXALIGN(baserel->width) +
MAXALIGN(sizeof(HeapTupleHeaderData));
! ntuples = clamp_row_est((double) stat_buf.st_size / (double)
tuple_width);
/*
* Now estimate the number of rows returned by the scan after applying
the
--- 627,658 ----
if (pages < 1)
pages = 1;
! relpages = baserel->pages;
! reltuples = baserel->tuples;
!
! if (relpages > 0)
! {
! double density;
! density = reltuples / (double) relpages;
!
! ntuples = clamp_row_est(density * (double) pages);
! }
! else
! {
! int tuple_width;
!
! /*
! * Estimate the number of tuples in the file. We back into
this estimate
! * using the planner's idea of the relation width; which is
bogus if not
! * all columns are being read, not to mention that the text
representation
! * of a row probably isn't the same size as its internal
representation.
! * FIXME later.
! */
! tuple_width = MAXALIGN(baserel->width) +
MAXALIGN(sizeof(HeapTupleHeaderData));
!
! ntuples = clamp_row_est((double) stat_buf.st_size / (double)
tuple_width);
! }
/*
* Now estimate the number of rows returned by the scan after applying
the
***************
*** 645,647 **** estimate_costs(PlannerInfo *root, RelOptInfo *baserel,
--- 684,888 ----
run_cost += cpu_per_tuple * ntuples;
*total_cost = *startup_cost + run_cost;
}
+
+ /*
+ * acquire_sample_rows -- acquire a random sample of rows from the table
+ *
+ * Selected rows are returned in the caller-allocated array rows[], which
+ * must have at least targrows entries.
+ * The actual number of rows selected is returned as the function result.
+ * We also count the number of rows in the table, and return it into
*totalrows.
+ *
+ * The returned list of tuples is in order by physical position in the table.
+ * (We will rely on this later to derive correlation estimates.)
+ */
+ static int
+ acquire_sample_rows(Relation onerel, HeapTuple *rows, int targrows,
+ double *totalrows, double
*totaldeadrows,
+ BlockNumber *totalpages, int elevel)
+ {
+ int numrows = 0;
+ double samplerows = 0; /* total # rows collected */
+ double rowstoskip = -1; /* -1 means not set yet */
+ double rstate;
+ HeapTuple tuple;
+ TupleDesc tupDesc;
+ TupleConstr *constr;
+ int natts;
+ int attrChk;
+ Datum *values;
+ bool *nulls;
+ bool found;
+ bool sample_it = false;
+ BlockNumber blknum;
+ OffsetNumber offnum;
+ char *filename;
+ struct stat stat_buf;
+ List *options;
+ CopyState cstate;
+ ErrorContextCallback errcontext;
+
+ Assert(onerel);
+ Assert(targrows > 0);
+
+ tupDesc = RelationGetDescr(onerel);
+ constr = tupDesc->constr;
+ natts = tupDesc->natts;
+ values = (Datum *) palloc(tupDesc->natts * sizeof(Datum));
+ nulls = (bool *) palloc(tupDesc->natts * sizeof(bool));
+
+ /* Fetch options of foreign table */
+ fileGetOptions(RelationGetRelid(onerel), &filename, &options);
+
+ /*
+ * Get size of the file.
+ */
+ if (stat(filename, &stat_buf) < 0)
+ ereport(ERROR,
+ (errcode_for_file_access(),
+ errmsg("could not stat file \"%s\": %m",
+ filename)));
+
+ /*
+ * Convert size to pages for use in I/O cost estimate.
+ */
+ *totalpages = (stat_buf.st_size + (BLCKSZ - 1)) / BLCKSZ;
+ if (*totalpages < 1)
+ *totalpages = 1;
+
+ /*
+ * Create CopyState from FDW options. We always acquire all columns, so
+ * as to match the expected ScanTupleSlot signature.
+ */
+ cstate = BeginCopyFrom(onerel, filename, NIL, options);
+
+ /* Prepare for sampling rows */
+ rstate = init_selection_state(targrows);
+
+ /* Set up callback to identify error line number. */
+ errcontext.callback = CopyFromErrorCallback;
+ errcontext.arg = (void *) cstate;
+ errcontext.previous = error_context_stack;
+ error_context_stack = &errcontext;
+
+ for (;;)
+ {
+ sample_it = true;
+
+ /*
+ * Check for user-requested abort.
+ */
+ CHECK_FOR_INTERRUPTS();
+
+ found = NextCopyFrom(cstate, NULL, values, nulls, NULL);
+
+ if (!found)
+ break;
+
+ tuple = heap_form_tuple(tupDesc, values, nulls);
+
+ if (constr && constr->has_not_null)
+ {
+ for (attrChk = 1; attrChk <= natts; attrChk++)
+ {
+ if (onerel->rd_att->attrs[attrChk -
1]->attnotnull &&
+ heap_attisnull(tuple, attrChk))
+ {
+ sample_it = false;
+ break;
+ }
+ }
+ }
+
+ if (!sample_it)
+ {
+ heap_freetuple(tuple);
+ continue;
+ }
+
+ /*
+ * The first targrows sample rows are simply copied into the
+ * reservoir. Then we start replacing tuples in the sample
+ * until we reach the end of the relation. This algorithm
is
+ * from Jeff Vitter's paper (see full citation below). It
+ * works by repeatedly computing the number of tuples to skip
+ * before selecting a tuple, which replaces a randomly chosen
+ * element of the reservoir (current set of tuples). At all
+ * times the reservoir is a true random sample of the tuples
+ * we've passed over so far, so when we fall off the end of
+ * the relation we're done.
+ */
+ if (numrows < targrows)
+ {
+ blknum = (BlockNumber) samplerows / MaxOffsetNumber;
+ offnum = (OffsetNumber) samplerows % MaxOffsetNumber +
1;
+ ItemPointerSet(&tuple->t_self, blknum, offnum);
+ rows[numrows++] = heap_copytuple(tuple);
+ }
+ else
+ {
+ /*
+ * t in Vitter's paper is the number of records already
+ * processed. If we need to compute a new S value, we
+ * must use the not-yet-incremented value of samplerows
as
+ * t.
+ */
+ if (rowstoskip < 0)
+ rowstoskip = get_next_S(samplerows, targrows,
&rstate);
+
+ if (rowstoskip <= 0)
+ {
+ /*
+ * Found a suitable tuple, so save it,
replacing one
+ * old tuple at random
+ */
+ int k = (int) (targrows * random_fract());
+
+ Assert(k >= 0 && k < targrows);
+ heap_freetuple(rows[k]);
+
+ blknum = (BlockNumber) samplerows /
MaxOffsetNumber;
+ offnum = (OffsetNumber) samplerows %
MaxOffsetNumber + 1;
+ ItemPointerSet(&tuple->t_self, blknum, offnum);
+ rows[k] = heap_copytuple(tuple);
+ }
+
+ rowstoskip -= 1;
+ }
+
+ samplerows += 1;
+ heap_freetuple(tuple);
+ }
+
+ /* Remove error callback. */
+ error_context_stack = errcontext.previous;
+
+ /*
+ * If we didn't find as many tuples as we wanted then we're done. No
sort
+ * is needed, since they're already in order.
+ *
+ * Otherwise we need to sort the collected tuples by position
+ * (itempointer). It's not worth worrying about corner cases where the
+ * tuples are already sorted.
+ */
+ if (numrows == targrows)
+ qsort((void *) rows, numrows, sizeof(HeapTuple), compare_rows);
+
+ *totalrows = samplerows;
+ *totaldeadrows = 0;
+
+ EndCopyFrom(cstate);
+
+ pfree(values);
+ pfree(nulls);
+
+ /*
+ * Emit some interesting relation info
+ */
+ ereport(elevel,
+ (errmsg("\"%s\": scanned, "
+ "%d rows in sample, %d total rows",
+ RelationGetRelationName(onerel),
numrows, (int) *totalrows)));
+
+ return numrows;
+ }
*** a/contrib/file_fdw/input/file_fdw.source
--- b/contrib/file_fdw/input/file_fdw.source
***************
*** 111,116 **** EXECUTE st(100);
--- 111,121 ----
EXECUTE st(100);
DEALLOCATE st;
+ -- statistics collection tests
+ ANALYZE agg_csv;
+ SELECT relpages, reltuples FROM pg_class WHERE relname = 'agg_csv';
+ SELECT * FROM pg_stats WHERE tablename = 'agg_csv';
+
-- tableoid
SELECT tableoid::regclass, b FROM agg_csv;
*** a/contrib/file_fdw/output/file_fdw.source
--- b/contrib/file_fdw/output/file_fdw.source
***************
*** 174,179 **** EXECUTE st(100);
--- 174,194 ----
(1 row)
DEALLOCATE st;
+ -- statistics collection tests
+ ANALYZE agg_csv;
+ SELECT relpages, reltuples FROM pg_class WHERE relname = 'agg_csv';
+ relpages | reltuples
+ ----------+-----------
+ 1 | 3
+ (1 row)
+
+ SELECT * FROM pg_stats WHERE tablename = 'agg_csv';
+ schemaname | tablename | attname | inherited | null_frac | avg_width |
n_distinct | most_common_vals | most_common_freqs | histogram_bounds |
correlation
+
------------+-----------+---------+-----------+-----------+-----------+------------+------------------+-------------------+-------------------------+-------------
+ public | agg_csv | a | f | 0 | 2 |
-1 | | | {0,42,100} |
-0.5
+ public | agg_csv | b | f | 0 | 4 |
-1 | | | {0.09561,99.097,324.78} |
0.5
+ (2 rows)
+
-- tableoid
SELECT tableoid::regclass, b FROM agg_csv;
tableoid | b
*** a/doc/src/sgml/fdwhandler.sgml
--- b/doc/src/sgml/fdwhandler.sgml
***************
*** 228,233 **** EndForeignScan (ForeignScanState *node);
--- 228,246 ----
</para>
<para>
+ <programlisting>
+ void
+ AnalyzeForeignTable (Relation onerel,
+ VacuumStmt *vacstmt,
+ int elevel);
+ </programlisting>
+
+ Collect statistics on a foreign table and store the results in the
+ pg_class and pg_statistics system catalogs.
+ This is called when <command>ANALYZE</> command is run.
+ </para>
+
+ <para>
The <structname>FdwRoutine</> and <structname>FdwPlan</> struct types
are declared in <filename>src/include/foreign/fdwapi.h</>, which see
for additional details.
*** a/doc/src/sgml/maintenance.sgml
--- b/doc/src/sgml/maintenance.sgml
***************
*** 279,284 ****
--- 279,288 ----
<command>ANALYZE</> strictly as a function of the number of rows
inserted or updated; it has no knowledge of whether that will lead
to meaningful statistical changes.
+ Note that the autovacuum daemon does not issue <command>ANALYZE</>
+ commands on foreign tables. It is recommended to run manually-managed
+ <command>ANALYZE</> commands as needed, which typically are executed
+ according to a schedule by cron or Task Scheduler scripts.
</para>
<para>
*** a/doc/src/sgml/ref/alter_foreign_table.sgml
--- b/doc/src/sgml/ref/alter_foreign_table.sgml
***************
*** 36,41 **** ALTER FOREIGN TABLE <replaceable
class="PARAMETER">name</replaceable>
--- 36,44 ----
DROP [ COLUMN ] [ IF EXISTS ] <replaceable
class="PARAMETER">column</replaceable> [ RESTRICT | CASCADE ]
ALTER [ COLUMN ] <replaceable class="PARAMETER">column</replaceable> [
SET DATA ] TYPE <replaceable class="PARAMETER">type</replaceable>
ALTER [ COLUMN ] <replaceable class="PARAMETER">column</replaceable> {
SET | DROP } NOT NULL
+ ALTER [ COLUMN ] <replaceable class="PARAMETER">column</replaceable> SET
STATISTICS <replaceable class="PARAMETER">integer</replaceable>
+ ALTER [ COLUMN ] <replaceable class="PARAMETER">column</replaceable> SET
( <replaceable class="PARAMETER">attribute_option</replaceable> = <replaceable
class="PARAMETER">value</replaceable> [, ... ] )
+ ALTER [ COLUMN ] <replaceable class="PARAMETER">column</replaceable>
RESET ( <replaceable class="PARAMETER">attribute_option</replaceable> [, ... ] )
ALTER [ COLUMN ] <replaceable class="PARAMETER">column</replaceable>
OPTIONS ( [ ADD | SET | DROP ] <replaceable
class="PARAMETER">option</replaceable> ['<replaceable
class="PARAMETER">value</replaceable>'] [, ... ])
OWNER TO <replaceable class="PARAMETER">new_owner</replaceable>
OPTIONS ( [ ADD | SET | DROP ] <replaceable
class="PARAMETER">option</replaceable> ['<replaceable
class="PARAMETER">value</replaceable>'] [, ... ])
***************
*** 94,99 **** ALTER FOREIGN TABLE <replaceable
class="PARAMETER">name</replaceable>
--- 97,146 ----
</varlistentry>
<varlistentry>
+ <term><literal>SET STATISTICS</literal></term>
+ <listitem>
+ <para>
+ This form
+ sets the per-column statistics-gathering target for subsequent
+ <xref linkend="sql-analyze"> operations.
+ The target can be set in the range 0 to 10000; alternatively, set it
+ to -1 to revert to using the system default statistics
+ target (<xref linkend="guc-default-statistics-target">).
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>SET ( <replaceable
class="PARAMETER">attribute_option</replaceable> = <replaceable
class="PARAMETER">value</replaceable> [, ... ] )</literal></term>
+ <term><literal>RESET ( <replaceable
class="PARAMETER">attribute_option</replaceable> [, ... ] )</literal></term>
+ <listitem>
+ <para>
+ This form
+ sets or resets a per-attribute option. Currently, the only defined
+ per-attribute option is <literal>n_distinct</>, which overrides
+ the number-of-distinct-values estimates made by subsequent
+ <xref linkend="sql-analyze"> operations.
+ When set to a positive value, <command>ANALYZE</> will assume that
+ the column contains exactly the specified number of distinct nonnull
+ values.
+ When set to a negative value, which must be greater than or equal
+ to -1, <command>ANALYZE</> will assume that the number of distinct
+ nonnull values in the column is linear in the size of the foreign
+ table; the exact count is to be computed by multiplying the estimated
+ foreign table size by the absolute value of the given number.
+ For example,
+ a value of -1 implies that all values in the column are distinct,
+ while a value of -0.5 implies that each value appears twice on the
+ average.
+ This can be useful when the size of the foreign table changes over
+ time, since the multiplication by the number of rows in the foreign
+ table is not performed until query planning time. Specify a value
+ of 0 to revert to estimating the number of distinct values normally.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
<term><literal>OWNER</literal></term>
<listitem>
<para>
*** a/doc/src/sgml/ref/analyze.sgml
--- b/doc/src/sgml/ref/analyze.sgml
***************
*** 39,47 **** ANALYZE [ VERBOSE ] [ <replaceable
class="PARAMETER">table</replaceable> [ ( <re
<para>
With no parameter, <command>ANALYZE</command> examines every table in the
! current database. With a parameter, <command>ANALYZE</command> examines
! only that table. It is further possible to give a list of column names,
! in which case only the statistics for those columns are collected.
</para>
</refsect1>
--- 39,49 ----
<para>
With no parameter, <command>ANALYZE</command> examines every table in the
! current database except for foreign tables. With a parameter, <command>
! ANALYZE</command> examines only that table. For a foreign table, it is
! necessary to spcify the name of that table. It is further possible to
! give a list of column names, in which case only the statistics for those
! columns are collected.
</para>
</refsect1>
***************
*** 63,69 **** ANALYZE [ VERBOSE ] [ <replaceable
class="PARAMETER">table</replaceable> [ ( <re
<listitem>
<para>
The name (possibly schema-qualified) of a specific table to
! analyze. Defaults to all tables in the current database.
</para>
</listitem>
</varlistentry>
--- 65,72 ----
<listitem>
<para>
The name (possibly schema-qualified) of a specific table to
! analyze. Defaults to all tables in the current database except
! for foreign tables.
</para>
</listitem>
</varlistentry>
***************
*** 137,143 **** ANALYZE [ VERBOSE ] [ <replaceable
class="PARAMETER">table</replaceable> [ ( <re
In rare situations, this non-determinism will cause the planner's
choices of query plans to change after <command>ANALYZE</command> is run.
To avoid this, raise the amount of statistics collected by
! <command>ANALYZE</command>, as described below.
</para>
<para>
--- 140,148 ----
In rare situations, this non-determinism will cause the planner's
choices of query plans to change after <command>ANALYZE</command> is run.
To avoid this, raise the amount of statistics collected by
! <command>ANALYZE</command>, as described below. Note that the time
! needed to analyze on foreign tables depends on the implementation of
! the foreign data wrapper via which such tables are attached.
</para>
<para>
*** a/src/backend/commands/analyze.c
--- b/src/backend/commands/analyze.c
***************
*** 23,28 ****
--- 23,29 ----
#include "access/xact.h"
#include "catalog/index.h"
#include "catalog/indexing.h"
+ #include "catalog/pg_class.h"
#include "catalog/pg_collation.h"
#include "catalog/pg_inherits_fn.h"
#include "catalog/pg_namespace.h"
***************
*** 30,35 ****
--- 31,38 ----
#include "commands/tablecmds.h"
#include "commands/vacuum.h"
#include "executor/executor.h"
+ #include "foreign/foreign.h"
+ #include "foreign/fdwapi.h"
#include "miscadmin.h"
#include "nodes/nodeFuncs.h"
#include "parser/parse_oper.h"
***************
*** 78,91 **** typedef struct AnlIndexData
int default_statistics_target = 100;
/* A few variables that don't seem worth passing around as parameters */
- static int elevel = -1;
-
static MemoryContext anl_context = NULL;
static BufferAccessStrategy vac_strategy;
- static void do_analyze_rel(Relation onerel, VacuumStmt *vacstmt, bool inh);
static void BlockSampler_Init(BlockSampler bs, BlockNumber nblocks,
int samplesize);
static bool BlockSampler_HasMore(BlockSampler bs);
--- 81,91 ----
***************
*** 96,110 **** static void compute_index_stats(Relation onerel, double
totalrows,
MemoryContext col_context);
static VacAttrStats *examine_attribute(Relation onerel, int attnum,
Node *index_expr);
! static int acquire_sample_rows(Relation onerel, HeapTuple *rows,
! int targrows, double *totalrows, double
*totaldeadrows);
! static double random_fract(void);
! static double init_selection_state(int n);
! static double get_next_S(double t, int n, double *stateptr);
! static int compare_rows(const void *a, const void *b);
static int acquire_inherited_sample_rows(Relation onerel,
HeapTuple *rows, int
targrows,
! double *totalrows,
double *totaldeadrows);
static void update_attstats(Oid relid, bool inh,
int natts, VacAttrStats **vacattrstats);
static Datum std_fetch_func(VacAttrStatsP stats, int rownum, bool *isNull);
--- 96,109 ----
MemoryContext col_context);
static VacAttrStats *examine_attribute(Relation onerel, int attnum,
Node *index_expr);
! static int acquire_sample_rows(Relation onerel,
! HeapTuple *rows, int
targrows,
! double *totalrows,
double *totaldeadrows,
! BlockNumber
*totalpages, int elevel);
static int acquire_inherited_sample_rows(Relation onerel,
HeapTuple *rows, int
targrows,
! double *totalrows,
double *totaldeadrows,
! BlockNumber
*totalpages, int elevel);
static void update_attstats(Oid relid, bool inh,
int natts, VacAttrStats **vacattrstats);
static Datum std_fetch_func(VacAttrStatsP stats, int rownum, bool *isNull);
***************
*** 119,125 **** static bool std_typanalyze(VacAttrStats *stats);
--- 118,126 ----
void
analyze_rel(Oid relid, VacuumStmt *vacstmt, BufferAccessStrategy bstrategy)
{
+ int elevel;
Relation onerel;
+ FdwRoutine *fdwroutine;
/* Set up static variables */
if (vacstmt->options & VACOPT_VERBOSE)
***************
*** 184,193 **** analyze_rel(Oid relid, VacuumStmt *vacstmt,
BufferAccessStrategy bstrategy)
}
/*
! * Check that it's a plain table; we used to do this in get_rel_oids()
but
* seems safer to check after we've locked the relation.
*/
! if (onerel->rd_rel->relkind != RELKIND_RELATION)
{
/* No need for a WARNING if we already complained during VACUUM
*/
if (!(vacstmt->options & VACOPT_VACUUM))
--- 185,195 ----
}
/*
! * Check that it's a plain table or foreign table; we used to do this
in get_rel_oids() but
* seems safer to check after we've locked the relation.
*/
! if (onerel->rd_rel->relkind != RELKIND_RELATION &&
! onerel->rd_rel->relkind != RELKIND_FOREIGN_TABLE)
{
/* No need for a WARNING if we already complained during VACUUM
*/
if (!(vacstmt->options & VACOPT_VACUUM))
***************
*** 212,217 **** analyze_rel(Oid relid, VacuumStmt *vacstmt,
BufferAccessStrategy bstrategy)
--- 214,221 ----
/*
* We can ANALYZE any table except pg_statistic. See update_attstats
+ * We can ANALYZE foreign tables if the underlying foreign-data wrappers
+ * have their AnalyzeForeignTable callback routines.
*/
if (RelationGetRelid(onerel) == StatisticRelationId)
{
***************
*** 219,224 **** analyze_rel(Oid relid, VacuumStmt *vacstmt,
BufferAccessStrategy bstrategy)
--- 223,242 ----
return;
}
+ if (onerel->rd_rel->relkind == RELKIND_FOREIGN_TABLE)
+ {
+ fdwroutine = GetFdwRoutineByRelId(RelationGetRelid(onerel));
+
+ if (fdwroutine->AnalyzeForeignTable == NULL)
+ {
+ ereport(WARNING,
+ (errmsg("skipping \"%s\" --- underlying
foreign-data wrapper cannot analyze it",
+
RelationGetRelationName(onerel))));
+ relation_close(onerel, ShareUpdateExclusiveLock);
+ return;
+ }
+ }
+
/*
* OK, let's do it. First let other backends know I'm in ANALYZE.
*/
***************
*** 226,241 **** analyze_rel(Oid relid, VacuumStmt *vacstmt,
BufferAccessStrategy bstrategy)
MyProc->vacuumFlags |= PROC_IN_ANALYZE;
LWLockRelease(ProcArrayLock);
! /*
! * Do the normal non-recursive ANALYZE.
! */
! do_analyze_rel(onerel, vacstmt, false);
! /*
! * If there are child tables, do recursive ANALYZE.
! */
! if (onerel->rd_rel->relhassubclass)
! do_analyze_rel(onerel, vacstmt, true);
/*
* Close source relation now, but keep lock so that no one deletes it
--- 244,280 ----
MyProc->vacuumFlags |= PROC_IN_ANALYZE;
LWLockRelease(ProcArrayLock);
! if (onerel->rd_rel->relkind == RELKIND_FOREIGN_TABLE)
! {
! ereport(elevel,
! (errmsg("analyzing \"%s.%s\"",
!
get_namespace_name(RelationGetNamespace(onerel)),
!
RelationGetRelationName(onerel))));
! fdwroutine->AnalyzeForeignTable(onerel, vacstmt, elevel);
! }
! else
! {
! /*
! * Do the normal non-recursive ANALYZE.
! */
! ereport(elevel,
! (errmsg("analyzing \"%s.%s\"",
!
get_namespace_name(RelationGetNamespace(onerel)),
!
RelationGetRelationName(onerel))));
! do_analyze_rel(onerel, vacstmt, elevel, false,
acquire_sample_rows);
! /*
! * If there are child tables, do recursive ANALYZE.
! */
! if (onerel->rd_rel->relhassubclass)
! {
! ereport(elevel,
! (errmsg("analyzing \"%s.%s\"
inheritance tree",
!
get_namespace_name(RelationGetNamespace(onerel)),
!
RelationGetRelationName(onerel))));
! do_analyze_rel(onerel, vacstmt, elevel, true,
acquire_inherited_sample_rows);
! }
! }
/*
* Close source relation now, but keep lock so that no one deletes it
***************
*** 257,264 **** analyze_rel(Oid relid, VacuumStmt *vacstmt,
BufferAccessStrategy bstrategy)
/*
* do_analyze_rel() -- analyze one relation, recursively or not
*/
! static void
! do_analyze_rel(Relation onerel, VacuumStmt *vacstmt, bool inh)
{
int attr_cnt,
tcnt,
--- 296,304 ----
/*
* do_analyze_rel() -- analyze one relation, recursively or not
*/
! void
! do_analyze_rel(Relation onerel, VacuumStmt *vacstmt, int elevel,
! bool inh, int (*sample_row_acquirer) ())
{
int attr_cnt,
tcnt,
***************
*** 273,278 **** do_analyze_rel(Relation onerel, VacuumStmt *vacstmt, bool inh)
--- 313,319 ----
numrows;
double totalrows,
totaldeadrows;
+ BlockNumber totalpages;
HeapTuple *rows;
PGRUsage ru0;
TimestampTz starttime = 0;
***************
*** 281,297 **** do_analyze_rel(Relation onerel, VacuumStmt *vacstmt, bool inh)
int save_sec_context;
int save_nestlevel;
- if (inh)
- ereport(elevel,
- (errmsg("analyzing \"%s.%s\" inheritance tree",
-
get_namespace_name(RelationGetNamespace(onerel)),
-
RelationGetRelationName(onerel))));
- else
- ereport(elevel,
- (errmsg("analyzing \"%s.%s\"",
-
get_namespace_name(RelationGetNamespace(onerel)),
-
RelationGetRelationName(onerel))));
-
/*
* Set up a working context so that we can easily free whatever junk
gets
* created.
--- 322,327 ----
***************
*** 449,459 **** do_analyze_rel(Relation onerel, VacuumStmt *vacstmt, bool inh)
*/
rows = (HeapTuple *) palloc(targrows * sizeof(HeapTuple));
if (inh)
! numrows = acquire_inherited_sample_rows(onerel, rows, targrows,
!
&totalrows, &totaldeadrows);
else
! numrows = acquire_sample_rows(onerel, rows, targrows,
!
&totalrows, &totaldeadrows);
/*
* Compute the statistics. Temporary results during the
calculations for
--- 479,491 ----
*/
rows = (HeapTuple *) palloc(targrows * sizeof(HeapTuple));
if (inh)
! numrows = sample_row_acquirer(onerel, rows, targrows,
!
&totalrows, &totaldeadrows,
! NULL,
elevel);
else
! numrows = sample_row_acquirer(onerel, rows, targrows,
!
&totalrows, &totaldeadrows,
!
&totalpages, elevel);
/*
* Compute the statistics. Temporary results during the
calculations for
***************
*** 534,540 **** do_analyze_rel(Relation onerel, VacuumStmt *vacstmt, bool inh)
*/
if (!inh)
vac_update_relstats(onerel,
!
RelationGetNumberOfBlocks(onerel),
totalrows,
visibilitymap_count(onerel),
hasindex,
--- 566,572 ----
*/
if (!inh)
vac_update_relstats(onerel,
! totalpages,
totalrows,
visibilitymap_count(onerel),
hasindex,
***************
*** 1017,1023 **** BlockSampler_Next(BlockSampler bs)
*/
static int
acquire_sample_rows(Relation onerel, HeapTuple *rows, int targrows,
! double *totalrows, double
*totaldeadrows)
{
int numrows = 0; /* # rows now in reservoir */
double samplerows = 0; /* total # rows collected */
--- 1049,1056 ----
*/
static int
acquire_sample_rows(Relation onerel, HeapTuple *rows, int targrows,
! double *totalrows, double
*totaldeadrows,
! BlockNumber *totalpages, int elevel)
{
int numrows = 0; /* # rows now in reservoir */
double samplerows = 0; /* total # rows collected */
***************
*** 1032,1037 **** acquire_sample_rows(Relation onerel, HeapTuple *rows, int
targrows,
--- 1065,1072 ----
Assert(targrows > 0);
totalblocks = RelationGetNumberOfBlocks(onerel);
+ if (totalpages)
+ *totalpages = totalblocks;
/* Need a cutoff xmin for HeapTupleSatisfiesVacuum */
OldestXmin = GetOldestXmin(onerel->rd_rel->relisshared, true);
***************
*** 1254,1260 **** acquire_sample_rows(Relation onerel, HeapTuple *rows, int
targrows,
}
/* Select a random value R uniformly distributed in (0 - 1) */
! static double
random_fract(void)
{
return ((double) random() + 1) / ((double) MAX_RANDOM_VALUE + 2);
--- 1289,1295 ----
}
/* Select a random value R uniformly distributed in (0 - 1) */
! double
random_fract(void)
{
return ((double) random() + 1) / ((double) MAX_RANDOM_VALUE + 2);
***************
*** 1274,1287 **** random_fract(void)
* determines the number of records to skip before the next record is
* processed.
*/
! static double
init_selection_state(int n)
{
/* Initial value of W (for use when Algorithm Z is first applied) */
return exp(-log(random_fract()) / n);
}
! static double
get_next_S(double t, int n, double *stateptr)
{
double S;
--- 1309,1322 ----
* determines the number of records to skip before the next record is
* processed.
*/
! double
init_selection_state(int n)
{
/* Initial value of W (for use when Algorithm Z is first applied) */
return exp(-log(random_fract()) / n);
}
! double
get_next_S(double t, int n, double *stateptr)
{
double S;
***************
*** 1366,1372 **** get_next_S(double t, int n, double *stateptr)
/*
* qsort comparator for sorting rows[] array
*/
! static int
compare_rows(const void *a, const void *b)
{
HeapTuple ha = *(const HeapTuple *) a;
--- 1401,1407 ----
/*
* qsort comparator for sorting rows[] array
*/
! int
compare_rows(const void *a, const void *b)
{
HeapTuple ha = *(const HeapTuple *) a;
***************
*** 1397,1403 **** compare_rows(const void *a, const void *b)
*/
static int
acquire_inherited_sample_rows(Relation onerel, HeapTuple *rows, int targrows,
! double *totalrows,
double *totaldeadrows)
{
List *tableOIDs;
Relation *rels;
--- 1432,1439 ----
*/
static int
acquire_inherited_sample_rows(Relation onerel, HeapTuple *rows, int targrows,
! double *totalrows,
double *totaldeadrows,
! BlockNumber
*totalpages, int elevel)
{
List *tableOIDs;
Relation *rels;
***************
*** 1460,1465 **** acquire_inherited_sample_rows(Relation onerel, HeapTuple
*rows, int targrows,
--- 1496,1503 ----
totalblocks += relblocks[nrels];
nrels++;
}
+ if (totalpages)
+ *totalpages = totalblocks;
/*
* Now sample rows from each relation, proportionally to its fraction of
***************
*** 1493,1499 **** acquire_inherited_sample_rows(Relation onerel, HeapTuple
*rows, int targrows,
rows + numrows,
childtargrows,
&trows,
!
&tdrows);
/* We may need to convert from child's rowtype
to parent's */
if (childrows > 0 &&
--- 1531,1539 ----
rows + numrows,
childtargrows,
&trows,
!
&tdrows,
!
NULL,
!
elevel);
/* We may need to convert from child's rowtype
to parent's */
if (childrows > 0 &&
*** a/src/backend/commands/tablecmds.c
--- b/src/backend/commands/tablecmds.c
***************
*** 311,316 **** static void ATPrepSetStatistics(Relation rel, const char
*colName,
--- 311,318 ----
Node *newValue, LOCKMODE lockmode);
static void ATExecSetStatistics(Relation rel, const char *colName,
Node *newValue, LOCKMODE lockmode);
+ static void ATPrepSetOptions(Relation rel, const char *colName,
+ Node *options, LOCKMODE lockmode);
static void ATExecSetOptions(Relation rel, const char *colName,
Node *options, bool isReset, LOCKMODE
lockmode);
static void ATExecSetStorage(Relation rel, const char *colName,
***************
*** 2887,2892 **** ATPrepCmd(List **wqueue, Relation rel, AlterTableCmd *cmd,
--- 2889,2895 ----
case AT_SetOptions: /* ALTER COLUMN SET ( options )
*/
case AT_ResetOptions: /* ALTER COLUMN RESET ( options ) */
ATSimplePermissions(rel, ATT_TABLE | ATT_INDEX);
+ ATPrepSetOptions(rel, cmd->name, cmd->def, lockmode);
/* This command never recurses */
pass = AT_PASS_MISC;
break;
***************
*** 4822,4831 **** ATPrepSetStatistics(Relation rel, const char *colName, Node
*newValue, LOCKMODE
* allowSystemTableMods to be turned on.
*/
if (rel->rd_rel->relkind != RELKIND_RELATION &&
! rel->rd_rel->relkind != RELKIND_INDEX)
ereport(ERROR,
(errcode(ERRCODE_WRONG_OBJECT_TYPE),
! errmsg("\"%s\" is not a table or index",
RelationGetRelationName(rel))));
/* Permissions checks */
--- 4825,4835 ----
* allowSystemTableMods to be turned on.
*/
if (rel->rd_rel->relkind != RELKIND_RELATION &&
! rel->rd_rel->relkind != RELKIND_INDEX &&
! rel->rd_rel->relkind != RELKIND_FOREIGN_TABLE)
ereport(ERROR,
(errcode(ERRCODE_WRONG_OBJECT_TYPE),
! errmsg("\"%s\" is not a table, index or
foreign table",
RelationGetRelationName(rel))));
/* Permissions checks */
***************
*** 4894,4899 **** ATExecSetStatistics(Relation rel, const char *colName, Node
*newValue, LOCKMODE
--- 4898,4923 ----
}
static void
+ ATPrepSetOptions(Relation rel, const char *colName, Node *options,
+ LOCKMODE lockmode)
+ {
+ if (rel->rd_rel->relkind == RELKIND_FOREIGN_TABLE)
+ {
+ ListCell *cell;
+
+ foreach(cell, (List *) options)
+ {
+ DefElem *def = (DefElem *) lfirst(cell);
+
+ if (pg_strncasecmp(def->defname,
"n_distinct_inherited", strlen("n_distinct_inherited")) == 0)
+ ereport(ERROR,
+
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("cannot support option
\"n_distinct_inherited\" for foreign tables")));
+ }
+ }
+ }
+
+ static void
ATExecSetOptions(Relation rel, const char *colName, Node *options,
bool isReset, LOCKMODE lockmode)
{
*** a/src/bin/psql/tab-complete.c
--- b/src/bin/psql/tab-complete.c
***************
*** 399,404 **** static const SchemaQuery Query_for_list_of_tsvf = {
--- 399,419 ----
NULL
};
+ static const SchemaQuery Query_for_list_of_tf = {
+ /* catname */
+ "pg_catalog.pg_class c",
+ /* selcondition */
+ "c.relkind IN ('r', 'f')",
+ /* viscondition */
+ "pg_catalog.pg_table_is_visible(c.oid)",
+ /* namespace */
+ "c.relnamespace",
+ /* result */
+ "pg_catalog.quote_ident(c.relname)",
+ /* qualresult */
+ NULL
+ };
+
static const SchemaQuery Query_for_list_of_views = {
/* catname */
"pg_catalog.pg_class c",
***************
*** 2769,2775 **** psql_completion(char *text, int start, int end)
/* ANALYZE */
/* If the previous word is ANALYZE, produce list of tables */
else if (pg_strcasecmp(prev_wd, "ANALYZE") == 0)
! COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_tables, NULL);
/* WHERE */
/* Simple case of the word before the where being the table name */
--- 2784,2790 ----
/* ANALYZE */
/* If the previous word is ANALYZE, produce list of tables */
else if (pg_strcasecmp(prev_wd, "ANALYZE") == 0)
! COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_tf, NULL);
/* WHERE */
/* Simple case of the word before the where being the table name */
*** a/src/include/commands/vacuum.h
--- b/src/include/commands/vacuum.h
***************
*** 167,171 **** extern void lazy_vacuum_rel(Relation onerel, VacuumStmt
*vacstmt,
--- 167,178 ----
/* in commands/analyze.c */
extern void analyze_rel(Oid relid, VacuumStmt *vacstmt,
BufferAccessStrategy bstrategy);
+ extern void do_analyze_rel(Relation onerel, VacuumStmt *vacstmt, int elevel,
+ bool inh, int
(*sample_row_acquirer) ());
+ extern double random_fract(void);
+ extern double init_selection_state(int n);
+ extern double get_next_S(double t, int n, double *stateptr);
+ extern int compare_rows(const void *a, const void *b);
+
#endif /* VACUUM_H */
*** a/src/include/foreign/fdwapi.h
--- b/src/include/foreign/fdwapi.h
***************
*** 12,19 ****
--- 12,21 ----
#ifndef FDWAPI_H
#define FDWAPI_H
+ #include "foreign/foreign.h"
#include "nodes/execnodes.h"
#include "nodes/relation.h"
+ #include "utils/rel.h"
/* To avoid including explain.h here, reference ExplainState thus: */
struct ExplainState;
***************
*** 68,73 **** typedef void (*ReScanForeignScan_function) (ForeignScanState
*node);
--- 70,78 ----
typedef void (*EndForeignScan_function) (ForeignScanState *node);
+ typedef void (*AnalyzeForeignTable_function) (Relation relation,
+
VacuumStmt *vacstmt,
+
int elevel);
/*
* FdwRoutine is the struct returned by a foreign-data wrapper's handler
***************
*** 88,93 **** typedef struct FdwRoutine
--- 93,99 ----
IterateForeignScan_function IterateForeignScan;
ReScanForeignScan_function ReScanForeignScan;
EndForeignScan_function EndForeignScan;
+ AnalyzeForeignTable_function AnalyzeForeignTable;
} FdwRoutine;
--
Sent via pgsql-hackers mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
