From 263d9de66b867b7800fac82c222e004b795b724a Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Fri, 6 Apr 2012 15:02:35 -0400 Subject: Allow statistics to be collected for foreign tables. ANALYZE now accepts foreign tables and allows the table's FDW to control how the sample rows are collected. (But only manual ANALYZEs will touch foreign tables, for the moment, since among other things it's not very clear how to handle remote permissions checks in an auto-analyze.) contrib/file_fdw is extended to support this. Etsuro Fujita, reviewed by Shigeru Hanada, some further tweaking by me. --- doc/src/sgml/fdwhandler.sgml | 42 ++++++++++++++++++++++++++++--- doc/src/sgml/maintenance.sgml | 10 ++++++++ doc/src/sgml/ref/alter_foreign_table.sgml | 28 +++++++++++++++++++++ doc/src/sgml/ref/analyze.sgml | 10 +++++++- 4 files changed, 86 insertions(+), 4 deletions(-) (limited to 'doc/src') diff --git a/doc/src/sgml/fdwhandler.sgml b/doc/src/sgml/fdwhandler.sgml index f7bf3d8a395..8e7525ab596 100644 --- a/doc/src/sgml/fdwhandler.sgml +++ b/doc/src/sgml/fdwhandler.sgml @@ -10,8 +10,8 @@ All operations on a foreign table are handled through its foreign data - wrapper, which consists of a set of functions that the planner and - executor call. The foreign data wrapper is responsible for fetching + wrapper, which consists of a set of functions that the core server + calls. The foreign data wrapper is responsible for fetching data from the remote data source and returning it to the PostgreSQL executor. This chapter outlines how to write a new foreign data wrapper. @@ -47,7 +47,8 @@ The handler function simply returns a struct of function pointers to - callback functions that will be called by the planner and executor. + callback functions that will be called by the planner, executor, and + various maintenance commands. Most of the effort in writing an FDW is in implementing these callback functions. The handler function must be registered with @@ -276,6 +277,41 @@ EndForeignScan (ForeignScanState *node); to remote servers should be cleaned up. + + +AcquireSampleRowsFunc +AnalyzeForeignTable (Relation relation); + + + This function is called when is executed on + a foreign table. If the FDW supports collecting statistics for this + foreign table, it should return a pointer to a function that will collect + sample rows from the table. Otherwise, return NULL. If the + FDW does not support collecting statistics for any tables, the + AnalyzeForeignTable pointer can be set to NULL. + + + + If provided, the sample collection function must have the signature + +int +AcquireSampleRowsFunc (Relation relation, int elevel, + HeapTuple *rows, int targrows, + double *totalrows, + double *totaldeadrows, + BlockNumber *totalpages); + + + A random sample of up to targrows rows should be collected + from the table and stored into the caller-provided rows + array. The actual number of rows collected must be returned. In + addition, store estimates of the total numbers of live rows, dead rows, + and pages in the table into the output parameters + totalrows, totaldeadrows, and + totalpages. These numbers will be recorded in the table's + pg_class entry for future use. + + The FdwRoutine struct type is declared in src/include/foreign/fdwapi.h, which see for additional diff --git a/doc/src/sgml/maintenance.sgml b/doc/src/sgml/maintenance.sgml index 93c3ff5f2bb..cc33e5f76b0 100644 --- a/doc/src/sgml/maintenance.sgml +++ b/doc/src/sgml/maintenance.sgml @@ -332,6 +332,16 @@ plans that use the expression index. + + + + The autovacuum daemon does not issue ANALYZE commands for + foreign tables, since it has no means of determining how often that + might be useful. If your queries require statistics on foreign tables + for proper planning, it's a good idea to run manually-managed + ANALYZE commands on those tables on a suitable schedule. + + diff --git a/doc/src/sgml/ref/alter_foreign_table.sgml b/doc/src/sgml/ref/alter_foreign_table.sgml index c4cdaa8289d..c3f33a50aec 100644 --- a/doc/src/sgml/ref/alter_foreign_table.sgml +++ b/doc/src/sgml/ref/alter_foreign_table.sgml @@ -36,6 +36,9 @@ ALTER FOREIGN TABLE [ IF EXISTS ] namecolumn [ RESTRICT | CASCADE ] ALTER [ COLUMN ] column [ SET DATA ] TYPE type ALTER [ COLUMN ] column { SET | DROP } NOT NULL + ALTER [ COLUMN ] column SET STATISTICS integer + ALTER [ COLUMN ] column SET ( attribute_option = value [, ... ] ) + ALTER [ COLUMN ] column RESET ( attribute_option [, ... ] ) ALTER [ COLUMN ] column OPTIONS ( [ ADD | SET | DROP ] option ['value'] [, ... ]) OWNER TO new_owner OPTIONS ( [ ADD | SET | DROP ] option ['value'] [, ... ]) @@ -103,6 +106,31 @@ ALTER FOREIGN TABLE [ IF EXISTS ] name + + SET STATISTICS + + + This form + sets the per-column statistics-gathering target for subsequent + operations. + See the similar form of + for more details. + + + + + + SET ( attribute_option = value [, ... ] ) + RESET ( attribute_option [, ... ] ) + + + This form sets or resets per-attribute options. + See the similar form of + for more details. + + + + OWNER diff --git a/doc/src/sgml/ref/analyze.sgml b/doc/src/sgml/ref/analyze.sgml index 8c9057beb91..52b2114889d 100644 --- a/doc/src/sgml/ref/analyze.sgml +++ b/doc/src/sgml/ref/analyze.sgml @@ -63,7 +63,8 @@ ANALYZE [ VERBOSE ] [ table [ ( The name (possibly schema-qualified) of a specific table to - analyze. Defaults to all tables in the current database. + analyze. If omitted, all regular tables (but not foreign tables) + in the current database are analyzed. @@ -92,6 +93,13 @@ ANALYZE [ VERBOSE ] [ table [ ( Notes + + Foreign tables are analyzed only when explicitly selected. Not all + foreign data wrappers support ANALYZE. If the table's + wrapper does not support ANALYZE, the command prints a + warning and does nothing. + + In the default PostgreSQL configuration, the autovacuum daemon (see ) -- cgit v1.2.3