Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1kBtZz-0003fv-SK for pgsql-hackers@arkaria.postgresql.org; Sat, 29 Aug 2020 05:39:08 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1kBtZx-0001HA-1l for pgsql-hackers@arkaria.postgresql.org; Sat, 29 Aug 2020 05:39:05 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1kBtZw-0001H1-Kx for pgsql-hackers@lists.postgresql.org; Sat, 29 Aug 2020 05:39:04 +0000 Received: from mail.postgrespro.ru ([93.174.131.139]) by magus.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1kBtZs-00037l-RC for pgsql-hackers@lists.postgresql.org; Sat, 29 Aug 2020 05:39:04 +0000 Received: from [192.168.0.106] (pool-77-222-105-198.is74.ru [77.222.105.198]) (using TLSv1.2 with cipher ECDHE-RSA-AES128-GCM-SHA256 (128/128 bits)) (Client did not present a certificate) by mail.postgrespro.ru (Postfix) with ESMTPSA id 1DA5421C1FD6; Sat, 29 Aug 2020 08:38:59 +0300 (MSK) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/simple; d=postgrespro.ru; s=mail; t=1598679539; bh=AWN7Jo6apxJdTrWFDmtp6voERFQTYaontXu28buuoJs=; h=Subject:To:Cc:References:From:Date:In-Reply-To; b=SGjDCik+VOsTwwIPo4asqYUwOSOMd5LLeG4RjaSK1kCpIcbR3AdNFA45dxhZ7S+A/ 1PdOrRhJCKf3G4RCmC2ivG6crbCSQwjUuTpE1loqheCVYvHzCoDVh0OEG0hqQgvgN+ LS8A+CrQWJBF0QFAhcJJdS7HNzak8sU9VfyBGZ8g= Subject: Re: Ideas about a better API for postgres_fdw remote estimates To: Bruce Momjian , Stephen Frost Cc: Tom Lane , pgsql-hackers@lists.postgresql.org, Etsuro Fujita , Jeff Janes References: <1155731.1593832096@sss.pgh.pa.us> <20200705170601.GD3125@tamriel.snowman.net> <1301903.1593970603@sss.pgh.pa.us> <20200705174817.GE3125@tamriel.snowman.net> <1309399.1593980722@sss.pgh.pa.us> <20200706141114.GF3125@tamriel.snowman.net> <1374896.1594047946@sss.pgh.pa.us> <20200706152828.GG3125@tamriel.snowman.net> <20200714013219.GD32422@momjian.us> From: Andrey Lepikhov Organization: Postgres Professional Message-ID: <7a40707d-1758-85a2-7bb1-6e5775518e64@postgrespro.ru> Date: Sat, 29 Aug 2020 10:38:56 +0500 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:68.0) Gecko/20100101 Thunderbird/68.10.0 MIME-Version: 1.0 In-Reply-To: <20200714013219.GD32422@momjian.us> Content-Type: multipart/mixed; boundary="------------E1F506154831107939E331AC" Content-Language: en-US List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk This is a multi-part message in MIME format. --------------E1F506154831107939E331AC Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 7bit On 7/14/20 6:32 AM, Bruce Momjian wrote: > On Mon, Jul 6, 2020 at 11:28:28AM -0400, Stephen Frost wrote: >>> Yeah, thinking about it as a function that inspects partial planner >>> results, it might be useful for other purposes besides postgres_fdw. >>> As I said before, I don't think this necessarily has to be bundled as >>> part of postgres_fdw. That still doesn't make it part of EXPLAIN. >> >> Providing it as a function rather than through EXPLAIN does make a bit >> more sense if we're going to skip things like the lookups you mention >> above. I'm still inclined to have it be a part of core rather than >> having it as postgres_fdw though. I'm not completely against it being >> part of postgres_fdw... but I would think that would really be >> appropriate if it's actually using something in postgres_fdw, but if >> everything that it's doing is part of core and nothing related >> specifically to the postgres FDW, then having it as part of core makes >> more sense to me. Also, having it as part of core would make it more >> appropriate for other tools to look at and adding that kind of >> inspection capability for partial planner results could be very >> interesting for tools like pgAdmin and such. > > I agree the statistics extraction should probably be part of core. > There is the goal if FDWs returning data, and returning the data > quickly. I think we can require all-new FDW servers to get improved > performance. I am not even clear if we have a full understanding of the > performance characteristics of FDWs yet. I know Tomas did some research > on its DML behavior, but other than that, I haven't seen much. > > On a related note, I have wished to be able to see all the costs > associated with plans not chosen, and I think others would like that as > well. Getting multiple costs for a query goes in that direction. > During the implementation of sharding related improvements i noticed that if we use a lot of foreign partitions, we have bad plans because of vacuum don't update statistics of foreign tables.This is done by the ANALYZE command, but it is very expensive operation for foreign table. Problem with statistics demonstrates with TAP-test from the first patch in attachment. I implemented some FDW + pg core machinery to reduce weight of the problem. The ANALYZE command on foreign table executes query on foreign server that extracts statistics tuple, serializes it into json-formatted string and returns to the caller. The caller deserializes this string, generates statistics for this foreign table and update it. The second patch is a proof-of-concept. This patch speedup analyze command and provides statistics relevance on a foreign table after autovacuum operation. Its effectiveness depends on relevance of statistics on the remote server, but still. -- regards, Andrey Lepikhov Postgres Professional --------------E1F506154831107939E331AC Content-Type: text/x-patch; charset=UTF-8; name="0001-TAP-Test-on-bad-statistics.patch" Content-Transfer-Encoding: 7bit Content-Disposition: attachment; filename="0001-TAP-Test-on-bad-statistics.patch" From 329954981959ee3fc97e52266c89a436d02ddf5e Mon Sep 17 00:00:00 2001 From: "Andrey V. Lepikhov" Date: Tue, 4 Aug 2020 09:29:37 +0500 Subject: [PATCH 1/2] TAP-Test on bad statistics. Add dummy postgres_fdw_stat() routine. It will return stat tuples for each input attribute. --- contrib/postgres_fdw/Makefile | 4 ++-- contrib/postgres_fdw/expected/foreign_stat.out | 18 ++++++++++++++++++ .../postgres_fdw/postgres_fdw--1.0--1.1.sql | 11 +++++++++++ contrib/postgres_fdw/postgres_fdw.c | 17 +++++++++++++++++ contrib/postgres_fdw/postgres_fdw.control | 2 +- contrib/postgres_fdw/sql/foreign_stat.sql | 10 ++++++++++ 6 files changed, 59 insertions(+), 3 deletions(-) create mode 100644 contrib/postgres_fdw/expected/foreign_stat.out create mode 100644 contrib/postgres_fdw/postgres_fdw--1.0--1.1.sql create mode 100644 contrib/postgres_fdw/sql/foreign_stat.sql diff --git a/contrib/postgres_fdw/Makefile b/contrib/postgres_fdw/Makefile index ee8a80a392..15a6f6c353 100644 --- a/contrib/postgres_fdw/Makefile +++ b/contrib/postgres_fdw/Makefile @@ -14,9 +14,9 @@ PG_CPPFLAGS = -I$(libpq_srcdir) SHLIB_LINK_INTERNAL = $(libpq) EXTENSION = postgres_fdw -DATA = postgres_fdw--1.0.sql +DATA = postgres_fdw--1.0.sql postgres_fdw--1.0--1.1.sql -REGRESS = postgres_fdw +REGRESS = postgres_fdw foreign_stat ifdef USE_PGXS PG_CONFIG = pg_config diff --git a/contrib/postgres_fdw/expected/foreign_stat.out b/contrib/postgres_fdw/expected/foreign_stat.out new file mode 100644 index 0000000000..28a470bccc --- /dev/null +++ b/contrib/postgres_fdw/expected/foreign_stat.out @@ -0,0 +1,18 @@ +CREATE TABLE ltable (a int, b real); +CREATE FOREIGN TABLE ftable (a int) server loopback options (table_name 'ltable'); +INSERT INTO ltable (a, b) (SELECT *, 1.01 FROM generate_series(1, 1E4)); +VACUUM; +-- Check statistic interface routine +SELECT * FROM postgres_fdw_stat('public', 'test', 'a'); + postgres_fdw_stat +------------------- + +(1 row) + +EXPLAIN (TIMING OFF, SUMMARY OFF, COSTS ON, ANALYZE) +SELECT * FROM ftable; + QUERY PLAN +--------------------------------------------------------------------------------------------- + Foreign Scan on ftable (cost=100.00..445.00 rows=10000 width=4) (actual rows=10000 loops=1) +(1 row) + diff --git a/contrib/postgres_fdw/postgres_fdw--1.0--1.1.sql b/contrib/postgres_fdw/postgres_fdw--1.0--1.1.sql new file mode 100644 index 0000000000..40d36c95df --- /dev/null +++ b/contrib/postgres_fdw/postgres_fdw--1.0--1.1.sql @@ -0,0 +1,11 @@ +/* contrib/postgres_fdw/postgres_fdw--1.0--1.1.sql */ + +-- complain if script is sourced in psql, rather than via CREATE EXTENSION +\echo Use "CREATE EXTENSION postgres_fdw" to load this file. \quit + +-- Returns serialized stat tuples for the relation attributes +CREATE FUNCTION postgres_fdw_stat(TEXT, TEXT, VARIADIC text[]) +RETURNS json +AS 'MODULE_PATHNAME' +LANGUAGE C STRICT; + diff --git a/contrib/postgres_fdw/postgres_fdw.c b/contrib/postgres_fdw/postgres_fdw.c index 9fc53cad68..e4c7bcec49 100644 --- a/contrib/postgres_fdw/postgres_fdw.c +++ b/contrib/postgres_fdw/postgres_fdw.c @@ -305,6 +305,7 @@ typedef struct * SQL functions */ PG_FUNCTION_INFO_V1(postgres_fdw_handler); +PG_FUNCTION_INFO_V1(postgres_fdw_stat); /* * FDW callback routines @@ -561,6 +562,22 @@ postgres_fdw_handler(PG_FUNCTION_ARGS) PG_RETURN_POINTER(routine); } +/* + * Get stat tuples for the attributes of relation + * See row_to_json() + */ +Datum +postgres_fdw_stat(PG_FUNCTION_ARGS) +{ + const char *nspname = text_to_cstring(PG_GETARG_TEXT_PP(0)); + const char *relname = text_to_cstring(PG_GETARG_TEXT_PP(1)); + StringInfo result; + + result = makeStringInfo(); + + PG_RETURN_TEXT_P(cstring_to_text_with_len(result->data, result->len)); +} + /* * postgresGetForeignRelSize * Estimate # of rows and width of the result of the scan diff --git a/contrib/postgres_fdw/postgres_fdw.control b/contrib/postgres_fdw/postgres_fdw.control index f9ed490752..d489382064 100644 --- a/contrib/postgres_fdw/postgres_fdw.control +++ b/contrib/postgres_fdw/postgres_fdw.control @@ -1,5 +1,5 @@ # postgres_fdw extension comment = 'foreign-data wrapper for remote PostgreSQL servers' -default_version = '1.0' +default_version = '1.1' module_pathname = '$libdir/postgres_fdw' relocatable = true diff --git a/contrib/postgres_fdw/sql/foreign_stat.sql b/contrib/postgres_fdw/sql/foreign_stat.sql new file mode 100644 index 0000000000..18f649b477 --- /dev/null +++ b/contrib/postgres_fdw/sql/foreign_stat.sql @@ -0,0 +1,10 @@ +CREATE TABLE ltable (a int, b real); +CREATE FOREIGN TABLE ftable (a int) server loopback options (table_name 'ltable'); +INSERT INTO ltable (a, b) (SELECT *, 1.01 FROM generate_series(1, 1E4)); +VACUUM; + +-- Check statistic interface routine +SELECT * FROM postgres_fdw_stat('public', 'test', 'a'); + +EXPLAIN (TIMING OFF, SUMMARY OFF, COSTS ON, ANALYZE) +SELECT * FROM ftable; -- 2.17.1 --------------E1F506154831107939E331AC Content-Type: text/x-patch; charset=UTF-8; name="0002-Pull-statistic-for-a-foreign-table-from-remote-serve.patch" Content-Transfer-Encoding: quoted-printable Content-Disposition: attachment; filename*0="0002-Pull-statistic-for-a-foreign-table-from-remote-serve.pa"; filename*1="tch" =46rom 637b83b5deaa2a336d54601dff9ed3fc09f45061 Mon Sep 17 00:00:00 2001 From: "Andrey V. Lepikhov" Date: Mon, 24 Aug 2020 13:45:50 +0500 Subject: [PATCH 2/2] Pull statistic for a foreign table from remote serve= r. Add the postgres_fdw_stat() routine that convert statistics on the relat= ion into json format. New fdw API routine GetForeignRelStat() implements acc= ess to this machinery and returns JSON string to the caller. This function i= s called by ANALYZE command as an attempt to reduce the cost of updating statistics. Add foreign relation analyze into the VACUUM ANALYZE and autovacuum. --- contrib/postgres_fdw/deparse.c | 8 + .../postgres_fdw/expected/foreign_stat.out | 115 +++- .../postgres_fdw/postgres_fdw--1.0--1.1.sql | 2 +- contrib/postgres_fdw/postgres_fdw.c | 53 +- contrib/postgres_fdw/postgres_fdw.h | 1 + contrib/postgres_fdw/sql/foreign_stat.sql | 41 +- src/backend/commands/analyze.c | 580 ++++++++++++++++++ src/backend/commands/vacuum.c | 13 +- src/backend/utils/adt/json.c | 6 + src/include/commands/vacuum.h | 1 + src/include/foreign/fdwapi.h | 2 + src/include/utils/json.h | 1 + 12 files changed, 798 insertions(+), 25 deletions(-) diff --git a/contrib/postgres_fdw/deparse.c b/contrib/postgres_fdw/depars= e.c index ad37a74221..9514fe996a 100644 --- a/contrib/postgres_fdw/deparse.c +++ b/contrib/postgres_fdw/deparse.c @@ -2053,6 +2053,14 @@ deparseAnalyzeSizeSql(StringInfo buf, Relation rel= ) appendStringInfo(buf, "::pg_catalog.regclass) / %d", BLCKSZ); } =20 +void +deparseGetStatSql(StringInfo buf, Relation rel) +{ + appendStringInfo(buf, "SELECT * FROM public.postgres_fdw_stat('"); + deparseRelation(buf, rel); + appendStringInfoString(buf, "');"); +} + /* * Construct SELECT statement to acquire sample rows of given relation. * diff --git a/contrib/postgres_fdw/expected/foreign_stat.out b/contrib/pos= tgres_fdw/expected/foreign_stat.out index 28a470bccc..a5d5ec8564 100644 --- a/contrib/postgres_fdw/expected/foreign_stat.out +++ b/contrib/postgres_fdw/expected/foreign_stat.out @@ -1,18 +1,107 @@ CREATE TABLE ltable (a int, b real); CREATE FOREIGN TABLE ftable (a int) server loopback options (table_name = 'ltable'); +VACUUM ANALYZE; +-- Check statistic interface routine on an empty table. +SELECT * FROM postgres_fdw_stat('ltable'); + postgres_fdw_stat = =20 +------------------------------------------------------------------------= ---------------------------------------------------- + {"namespace" : "public", "relname" : "ltable", "sta_num_slots" : 5, "re= lpages" : 0, "reltuples" : 0.000000, "attrs" : [ ]} +(1 row) + +SELECT * FROM postgres_fdw_stat('ftable'); +ERROR: Can be used for ordinary relation only. Reltype: f. +-- Check statistic interface routine on non-empty tables. INSERT INTO ltable (a, b) (SELECT *, 1.01 FROM generate_series(1, 1E4));= -VACUUM; --- Check statistic interface routine -SELECT * FROM postgres_fdw_stat('public', 'test', 'a'); - postgres_fdw_stat=20 -------------------- -=20 -(1 row) - -EXPLAIN (TIMING OFF, SUMMARY OFF, COSTS ON, ANALYZE) -SELECT * FROM ftable; - QUERY PLAN = =20 -------------------------------------------------------------------------= --------------------- - Foreign Scan on ftable (cost=3D100.00..445.00 rows=3D10000 width=3D4) = (actual rows=3D10000 loops=3D1) +ANALYZE ltable, ftable; +SELECT * FROM postgres_fdw_stat('ltable'); + = = = = = = = = = postgres_fdw_stat = = = = = = = = = =20 +------------------------------------------------------------------------= -------------------------------------------------------------------------= -------------------------------------------------------------------------= -------------------------------------------------------------------------= -------------------------------------------------------------------------= -------------------------------------------------------------------------= -------------------------------------------------------------------------= -------------------------------------------------------------------------= -------------------------------------------------------------------------= -------------------------------------------------------------------------= -------------------------------------------------------------------------= -------------------------------------------------------------------------= -------------------------------------------------------------------------= -------------------------------------------------------------------------= -------------------------------------------------------------------------= -------------------------------------------------------------------------= -------------------------------------------------------------------------= -------------------------------------------------------------------------= ----------------------------------------------------- + {"namespace" : "public", "relname" : "ltable", "sta_num_slots" : 5, "re= lpages" : 45, "reltuples" : 10000.000000, "attrs" : [{"attname" : "a", "i= nh" : "false", "nullfrac" : 0.000000, "width" : 4, "distinct" : -1.000000= , "stakind" : [2,3,0,0,0], "staop" : [97,97,0,0,0], "stacoll" : [0,0,0,0,= 0], "nn" : 101, "values1" : [1,100,200,300,400,500,600,700,800,900,1000,1= 100,1200,1300,1400,1500,1600,1700,1800,1900,2000,2100,2200,2300,2400,2500= ,2600,2700,2800,2900,3000,3100,3200,3300,3400,3500,3600,3700,3800,3900,40= 00,4100,4200,4300,4400,4500,4600,4700,4800,4900,5000,5100,5200,5300,5400,= 5500,5600,5700,5800,5900,6000,6100,6200,6300,6400,6500,6600,6700,6800,690= 0,7000,7100,7200,7300,7400,7500,7600,7700,7800,7900,8000,8100,8200,8300,8= 400,8500,8600,8700,8800,8900,9000,9100,9200,9300,9400,9500,9600,9700,9800= ,9900,10000], "values2" : [ ], "values3" : [ ], "values4" : [ ], "values5= " : [ ], "numbers1" : [ ], "nn" : 1, "numbers2" : [1], "numbers3" : [ ], = "numbers4" : [ ], "numbers5" : [ ]}, {"attname" : "b", "inh" : "false", "= nullfrac" : 0.000000, "width" : 4, "distinct" : 1.000000, "stakind" : [1,= 3,0,0,0], "staop" : [620,622,0,0,0], "stacoll" : [0,0,0,0,0], "nn" : 1, "= values1" : [1.01], "values2" : [ ], "values3" : [ ], "values4" : [ ], "va= lues5" : [ ], "nn" : 1, "numbers1" : [1], "nn" : 1, "numbers2" : [1], "nu= mbers3" : [ ], "numbers4" : [ ], "numbers5" : [ ]}]} +(1 row) + +-- Check ANALYZE on foreign table +INSERT INTO ltable (a, b) (SELECT *, 2.01 FROM generate_series(1E4, 2E4)= ); +ANALYZE ltable; +EXPLAIN (TIMING OFF, SUMMARY OFF, COSTS ON, ANALYZE) SELECT * FROM ltabl= e; + QUERY PLAN = =20 +------------------------------------------------------------------------= ---------------- + Seq Scan on ltable (cost=3D0.00..289.01 rows=3D20001 width=3D8) (actua= l rows=3D20001 loops=3D1) +(1 row) + +EXPLAIN (TIMING OFF, SUMMARY OFF, COSTS ON, ANALYZE) SELECT * FROM ftabl= e; + QUERY PLAN = =20 +------------------------------------------------------------------------= ---------------------- + Foreign Scan on ftable (cost=3D100.00..445.00 rows=3D10000 width=3D4) = (actual rows=3D20001 loops=3D1) +(1 row) + +ANALYZE ftable; +EXPLAIN (TIMING OFF, SUMMARY OFF, COSTS ON, ANALYZE) SELECT * FROM ftabl= e; + QUERY PLAN = =20 +------------------------------------------------------------------------= ---------------------- + Foreign Scan on ftable (cost=3D100.00..789.03 rows=3D20001 width=3D4) = (actual rows=3D20001 loops=3D1) +(1 row) + +-- Check selectivity +EXPLAIN (TIMING OFF, SUMMARY OFF, COSTS ON, ANALYZE) SELECT * FROM ltabl= e WHERE a > 10; + QUERY PLAN = =20 +------------------------------------------------------------------------= ---------------- + Seq Scan on ltable (cost=3D0.00..339.01 rows=3D19991 width=3D8) (actua= l rows=3D19991 loops=3D1) + Filter: (a > 10) + Rows Removed by Filter: 10 +(3 rows) + +EXPLAIN (TIMING OFF, SUMMARY OFF, COSTS ON, ANALYZE) SELECT * FROM ftabl= e WHERE a > 10; + QUERY PLAN = =20 +------------------------------------------------------------------------= ---------------------- + Foreign Scan on ftable (cost=3D100.00..838.83 rows=3D19991 width=3D4) = (actual rows=3D19991 loops=3D1) +(1 row) + +-- Check new attribute +ALTER TABLE ltable ADD COLUMN c int DEFAULT 42; +ALTER TABLE ftable ADD COLUMN c int; +EXPLAIN (TIMING OFF, SUMMARY OFF, COSTS ON, ANALYZE) SELECT * FROM ltabl= e WHERE a > 10 AND c < 15; + QUERY PLAN = =20 +------------------------------------------------------------------------= ------------ + Seq Scan on ltable (cost=3D0.00..389.01 rows=3D6664 width=3D12) (actua= l rows=3D0 loops=3D1) + Filter: ((a > 10) AND (c < 15)) + Rows Removed by Filter: 20001 +(3 rows) + +EXPLAIN (TIMING OFF, SUMMARY OFF, COSTS ON, ANALYZE) SELECT * FROM ftabl= e WHERE a > 10 AND c < 15; + QUERY PLAN = =20 +------------------------------------------------------------------------= ----------------- + Foreign Scan on ftable (cost=3D100.00..622.29 rows=3D6664 width=3D8) (= actual rows=3D0 loops=3D1) +(1 row) + +ANALYZE ltable, ftable; +EXPLAIN (TIMING OFF, SUMMARY OFF, COSTS ON, ANALYZE) SELECT * FROM ltabl= e WHERE a > 10 AND c < 15; + QUERY PLAN = =20 +------------------------------------------------------------------------= --------- + Seq Scan on ltable (cost=3D0.00..389.01 rows=3D1 width=3D12) (actual r= ows=3D0 loops=3D1) + Filter: ((a > 10) AND (c < 15)) + Rows Removed by Filter: 20001 +(3 rows) + +EXPLAIN (TIMING OFF, SUMMARY OFF, COSTS ON, ANALYZE) SELECT * FROM ftabl= e WHERE a > 10 AND c < 15; + QUERY PLAN = =20 +------------------------------------------------------------------------= -------------- + Foreign Scan on ftable (cost=3D100.00..489.03 rows=3D1 width=3D8) (act= ual rows=3D0 loops=3D1) +(1 row) + +-- Test default vacuum analyzes foreign relation +INSERT INTO ltable (a, b) (SELECT *, 2.01 FROM generate_series(2E4, 3E4)= ); +VACUUM ANALYZE ltable, ftable; -- Don't depend on default order of analy= sis here. +EXPLAIN (TIMING OFF, SUMMARY OFF, COSTS ON, ANALYZE) SELECT * FROM ltabl= e WHERE a > 100 AND c < 43; + QUERY PLAN = =20 +------------------------------------------------------------------------= ----------------- + Seq Scan on ltable (cost=3D0.00..593.03 rows=3D29902 width=3D12) (actu= al rows=3D29902 loops=3D1) + Filter: ((a > 100) AND (c < 43)) + Rows Removed by Filter: 100 +(3 rows) + +EXPLAIN (TIMING OFF, SUMMARY OFF, COSTS ON, ANALYZE) SELECT * FROM ftabl= e WHERE a > 100 AND c < 43; + QUERY PLAN = =20 +------------------------------------------------------------------------= ----------------------- + Foreign Scan on ftable (cost=3D100.00..1291.07 rows=3D29902 width=3D8)= (actual rows=3D29902 loops=3D1) (1 row) =20 diff --git a/contrib/postgres_fdw/postgres_fdw--1.0--1.1.sql b/contrib/po= stgres_fdw/postgres_fdw--1.0--1.1.sql index 40d36c95df..ff0dd6f83e 100644 --- a/contrib/postgres_fdw/postgres_fdw--1.0--1.1.sql +++ b/contrib/postgres_fdw/postgres_fdw--1.0--1.1.sql @@ -4,7 +4,7 @@ \echo Use "CREATE EXTENSION postgres_fdw" to load this file. \quit =20 -- Returns serialized stat tuples for the relation attributes -CREATE FUNCTION postgres_fdw_stat(TEXT, TEXT, VARIADIC text[]) +CREATE FUNCTION postgres_fdw_stat(TEXT) RETURNS json AS 'MODULE_PATHNAME' LANGUAGE C STRICT; diff --git a/contrib/postgres_fdw/postgres_fdw.c b/contrib/postgres_fdw/p= ostgres_fdw.c index e4c7bcec49..f81be84c02 100644 --- a/contrib/postgres_fdw/postgres_fdw.c +++ b/contrib/postgres_fdw/postgres_fdw.c @@ -392,6 +392,7 @@ static void postgresGetForeignUpperPaths(PlannerInfo = *root, RelOptInfo *input_rel, RelOptInfo *output_rel, void *extra); +static char *postgresGetForeignRelStat(Relation rel); =20 /* * Helper functions @@ -559,6 +560,7 @@ postgres_fdw_handler(PG_FUNCTION_ARGS) /* Support functions for upper relation push-down */ routine->GetForeignUpperPaths =3D postgresGetForeignUpperPaths; =20 + routine->GetForeignRelStat =3D postgresGetForeignRelStat; PG_RETURN_POINTER(routine); } =20 @@ -569,13 +571,12 @@ postgres_fdw_handler(PG_FUNCTION_ARGS) Datum postgres_fdw_stat(PG_FUNCTION_ARGS) { - const char *nspname =3D text_to_cstring(PG_GETARG_TEXT_PP(0)); - const char *relname =3D text_to_cstring(PG_GETARG_TEXT_PP(1)); - StringInfo result; + const char *relname =3D text_to_cstring(PG_GETARG_TEXT_PP(0)); + const char *json_stat; =20 - result =3D makeStringInfo(); + json_stat =3D relstat_to_json(relname); =20 - PG_RETURN_TEXT_P(cstring_to_text_with_len(result->data, result->len)); + PG_RETURN_TEXT_P(cstring_to_text(json_stat)); } =20 /* @@ -6601,3 +6602,45 @@ find_em_expr_for_input_target(PlannerInfo *root, elog(ERROR, "could not find pathkey item to sort"); return NULL; /* keep compiler quiet */ } + +static char * +postgresGetForeignRelStat(Relation rel) +{ + ForeignTable *table; + UserMapping *user; + PGconn *conn; + unsigned int cursor_number; + StringInfoData sql; + PGresult *volatile res =3D NULL; + char fetch_sql[64]; + char *json; + + table =3D GetForeignTable(RelationGetRelid(rel)); + user =3D GetUserMapping(rel->rd_rel->relowner, table->serverid); + conn =3D GetConnection(user, false); + + cursor_number =3D GetCursorNumber(conn); + initStringInfo(&sql); + appendStringInfo(&sql, "DECLARE c%u CURSOR FOR ", cursor_number); + deparseGetStatSql(&sql, rel); + + res =3D pgfdw_exec_query(conn, sql.data); + if (PQresultStatus(res) !=3D PGRES_COMMAND_OK) + pgfdw_report_error(ERROR, res, conn, false, sql.data); + PQclear(res); + + snprintf(fetch_sql, sizeof(fetch_sql), "FETCH FROM c%u", cursor_number)= ; + res =3D pgfdw_exec_query(conn, fetch_sql); + if (PQresultStatus(res) !=3D PGRES_TUPLES_OK) + pgfdw_report_error(ERROR, res, conn, false, sql.data); + + Assert(PQntuples(res) =3D=3D 1); + Assert(PQnfields(res) =3D=3D 1); + + json =3D pstrdup(PQgetvalue(res, 0, 0)); + PQclear(res); + close_cursor(conn, cursor_number); + ReleaseConnection(conn); + + return json; +} diff --git a/contrib/postgres_fdw/postgres_fdw.h b/contrib/postgres_fdw/p= ostgres_fdw.h index eef410db39..bb590bfc21 100644 --- a/contrib/postgres_fdw/postgres_fdw.h +++ b/contrib/postgres_fdw/postgres_fdw.h @@ -190,6 +190,7 @@ extern void deparseDirectDeleteSql(StringInfo buf, Pl= annerInfo *root, extern void deparseAnalyzeSizeSql(StringInfo buf, Relation rel); extern void deparseAnalyzeSql(StringInfo buf, Relation rel, List **retrieved_attrs); +extern void deparseGetStatSql(StringInfo buf, Relation rel); extern void deparseStringLiteral(StringInfo buf, const char *val); extern Expr *find_em_expr_for_rel(EquivalenceClass *ec, RelOptInfo *rel)= ; extern Expr *find_em_expr_for_input_target(PlannerInfo *root, diff --git a/contrib/postgres_fdw/sql/foreign_stat.sql b/contrib/postgres= _fdw/sql/foreign_stat.sql index 18f649b477..0829ebc522 100644 --- a/contrib/postgres_fdw/sql/foreign_stat.sql +++ b/contrib/postgres_fdw/sql/foreign_stat.sql @@ -1,10 +1,41 @@ CREATE TABLE ltable (a int, b real); CREATE FOREIGN TABLE ftable (a int) server loopback options (table_name = 'ltable'); +VACUUM ANALYZE; + +-- Check statistic interface routine on an empty table. +SELECT * FROM postgres_fdw_stat('ltable'); +SELECT * FROM postgres_fdw_stat('ftable'); + +-- Check statistic interface routine on non-empty tables. INSERT INTO ltable (a, b) (SELECT *, 1.01 FROM generate_series(1, 1E4));= -VACUUM; +ANALYZE ltable, ftable; +SELECT * FROM postgres_fdw_stat('ltable'); + +-- Check ANALYZE on foreign table +INSERT INTO ltable (a, b) (SELECT *, 2.01 FROM generate_series(1E4, 2E4)= ); +ANALYZE ltable; +EXPLAIN (TIMING OFF, SUMMARY OFF, COSTS ON, ANALYZE) SELECT * FROM ltabl= e; +EXPLAIN (TIMING OFF, SUMMARY OFF, COSTS ON, ANALYZE) SELECT * FROM ftabl= e; +ANALYZE ftable; +EXPLAIN (TIMING OFF, SUMMARY OFF, COSTS ON, ANALYZE) SELECT * FROM ftabl= e; + +-- Check selectivity +EXPLAIN (TIMING OFF, SUMMARY OFF, COSTS ON, ANALYZE) SELECT * FROM ltabl= e WHERE a > 10; +EXPLAIN (TIMING OFF, SUMMARY OFF, COSTS ON, ANALYZE) SELECT * FROM ftabl= e WHERE a > 10; + +-- Check new attribute +ALTER TABLE ltable ADD COLUMN c int DEFAULT 42; +ALTER TABLE ftable ADD COLUMN c int; +EXPLAIN (TIMING OFF, SUMMARY OFF, COSTS ON, ANALYZE) SELECT * FROM ltabl= e WHERE a > 10 AND c < 15; +EXPLAIN (TIMING OFF, SUMMARY OFF, COSTS ON, ANALYZE) SELECT * FROM ftabl= e WHERE a > 10 AND c < 15; + +ANALYZE ltable, ftable; +EXPLAIN (TIMING OFF, SUMMARY OFF, COSTS ON, ANALYZE) SELECT * FROM ltabl= e WHERE a > 10 AND c < 15; +EXPLAIN (TIMING OFF, SUMMARY OFF, COSTS ON, ANALYZE) SELECT * FROM ftabl= e WHERE a > 10 AND c < 15; =20 --- Check statistic interface routine -SELECT * FROM postgres_fdw_stat('public', 'test', 'a'); +-- Test default vacuum analyzes foreign relation +INSERT INTO ltable (a, b) (SELECT *, 2.01 FROM generate_series(2E4, 3E4)= ); +VACUUM ANALYZE ltable, ftable; -- Don't depend on default order of analy= sis here. +EXPLAIN (TIMING OFF, SUMMARY OFF, COSTS ON, ANALYZE) SELECT * FROM ltabl= e WHERE a > 100 AND c < 43; +EXPLAIN (TIMING OFF, SUMMARY OFF, COSTS ON, ANALYZE) SELECT * FROM ftabl= e WHERE a > 100 AND c < 43; =20 -EXPLAIN (TIMING OFF, SUMMARY OFF, COSTS ON, ANALYZE) -SELECT * FROM ftable; diff --git a/src/backend/commands/analyze.c b/src/backend/commands/analyz= e.c index 8af12b5c6b..ca2642360e 100644 --- a/src/backend/commands/analyze.c +++ b/src/backend/commands/analyze.c @@ -30,6 +30,7 @@ #include "catalog/catalog.h" #include "catalog/index.h" #include "catalog/indexing.h" +#include "catalog/namespace.h" #include "catalog/pg_collation.h" #include "catalog/pg_inherits.h" #include "catalog/pg_namespace.h" @@ -38,8 +39,10 @@ #include "commands/progress.h" #include "commands/tablecmds.h" #include "commands/vacuum.h" +#include "common/jsonapi.h" #include "executor/executor.h" #include "foreign/fdwapi.h" +#include "mb/pg_wchar.h" #include "miscadmin.h" #include "nodes/nodeFuncs.h" #include "parser/parse_oper.h" @@ -58,9 +61,11 @@ #include "utils/datum.h" #include "utils/fmgroids.h" #include "utils/guc.h" +#include "utils/json.h" #include "utils/lsyscache.h" #include "utils/memutils.h" #include "utils/pg_rusage.h" +#include "utils/regproc.h" #include "utils/sampling.h" #include "utils/sortsupport.h" #include "utils/syscache.h" @@ -107,6 +112,10 @@ static void update_attstats(Oid relid, bool inh, static Datum std_fetch_func(VacAttrStatsP stats, int rownum, bool *isNul= l); static Datum ind_fetch_func(VacAttrStatsP stats, int rownum, bool *isNul= l); =20 +static void parse_statjson(Relation rel, char *buffer, size_t size, + VacAttrStats **vacattrstats, int *natts, + BlockNumber *relpages, double *reltuples); +static bool update_foreign_relation_stat(Relation rel); =20 /* * analyze_rel() -- analyze one relation @@ -193,6 +202,17 @@ analyze_rel(Oid relid, RangeVar *relation, return; } =20 + if (onerel->rd_rel->relkind =3D=3D RELKIND_FOREIGN_TABLE && + update_foreign_relation_stat(onerel)) + { + /* + * The foreign relation statistics was updated by existed stat tuple + * from remote server. + */ + relation_close(onerel, ShareUpdateExclusiveLock); + return; + } + /* * Check that it's of an analyzable relkind, and set up appropriately. */ @@ -1421,6 +1441,566 @@ acquire_inherited_sample_rows(Relation onerel, in= t elevel, return numrows; } =20 +typedef enum +{ + JS_EXPECT_NSPNAME_VALUE, + JS_EXPECT_RELNAME_VALUE, + JS_EXPECT_SLOTSNUM_VALUE, + JS_EXPECT_RELPAGES_VALUE, + JS_EXPECT_RELTUPLES_VALUE, + JS_EXPECT_ATTNAME_FIELD, + JS_EXPECT_ATTNAME_VALUE, + JS_EXPECT_INH_VALUE, + JS_EXPECT_NULLFRAC_VALUE, + JS_EXPECT_WIDTH_VALUE, + JS_EXPECT_DISTINCT_VALUE, + JS_EXPECT_KIND_VALUE, + JS_EXPECT_STAOP_VALUE, + JS_EXPECT_STACOLL_VALUE, + JS_EXPECT_ARRSIZE_VALUE, + JS_EXPECT_NUMBERS_VALUE, + JS_EXPECT_VALUES_VALUE, + JS_EXPECT_PARAM_FIELD +} JsonStatSemanticState; + +typedef struct JsonStatParseState +{ + Relation rel; + int natts; + int state; + int arraysize; /* size of the array */ + int arrayval; /* current position in the array */ + int numbersN; + int valuesN; + + char *nspname; + char *relname; + + BlockNumber relpages; + double reltuples; + VacAttrStats **vas; +} JsonStatParseState; + +static bool skip_attr =3D false; + +static void +json_stat_object_field_start(void *state, char *fname, bool isnull) +{ + JsonStatParseState *parse =3D (JsonStatParseState *) state; + int attnum =3D parse->natts - 1; + + if (skip_attr && strcmp(fname, "attname") !=3D 0) + return; + + Assert(parse->state =3D=3D JS_EXPECT_PARAM_FIELD || + parse->state =3D=3D JS_EXPECT_ATTNAME_FIELD); + Assert(parse->arrayval < 0); + + if (strcmp(fname, "namespace") =3D=3D 0) + parse->state =3D JS_EXPECT_NSPNAME_VALUE; + else if (strcmp(fname, "relname") =3D=3D 0) + parse->state =3D JS_EXPECT_RELNAME_VALUE; + else if (strcmp(fname, "sta_num_slots") =3D=3D 0) + parse->state =3D JS_EXPECT_SLOTSNUM_VALUE; + else if (strcmp(fname, "relpages") =3D=3D 0) + parse->state =3D JS_EXPECT_RELPAGES_VALUE; + else if (strcmp(fname, "reltuples") =3D=3D 0) + parse->state =3D JS_EXPECT_RELTUPLES_VALUE; + else if(strcmp(fname, "attrs") =3D=3D 0) + parse->state =3D JS_EXPECT_ATTNAME_FIELD; + else if (strcmp(fname, "attname") =3D=3D 0) + { + parse->state =3D JS_EXPECT_ATTNAME_VALUE; + skip_attr =3D false; + } + else if (strcmp(fname, "nullfrac") =3D=3D 0) + parse->state =3D JS_EXPECT_NULLFRAC_VALUE; + else if (strcmp(fname, "inh") =3D=3D 0) + parse->state =3D JS_EXPECT_INH_VALUE; + else if (strcmp(fname, "width") =3D=3D 0) + parse->state =3D JS_EXPECT_WIDTH_VALUE; + else if (strcmp(fname, "distinct") =3D=3D 0) + parse->state =3D JS_EXPECT_DISTINCT_VALUE; + else if (strcmp(fname, "nn") =3D=3D 0) + { + Assert(parse->arraysize =3D=3D 0); + parse->state =3D JS_EXPECT_ARRSIZE_VALUE; + } + else + { + parse->arrayval =3D 0; + if (strcmp(fname, "stakind") =3D=3D 0) + parse->state =3D JS_EXPECT_KIND_VALUE; + else if (strcmp(fname, "staop") =3D=3D 0) + parse->state =3D JS_EXPECT_STAOP_VALUE; + else if (strcmp(fname, "stacoll") =3D=3D 0) + parse->state =3D JS_EXPECT_STACOLL_VALUE; + else if (strstr(fname, "numbers") !=3D NULL) + { + parse->numbersN =3D atoi(&fname[7]); + Assert(parse->numbersN > 0 && parse->numbersN <=3D 5); + if (parse->arraysize > 0) + parse->vas[attnum]->stanumbers[parse->numbersN-1] =3D + (float4 *) palloc(parse->arraysize * sizeof(float4)); + else + parse->vas[attnum]->stanumbers[parse->numbersN-1] =3D NULL; + + parse->vas[attnum]->numnumbers[parse->numbersN-1] =3D parse->arraysiz= e; + parse->state =3D JS_EXPECT_NUMBERS_VALUE; + } + else if (strstr(fname, "values") !=3D NULL) + { + parse->valuesN =3D atoi(&fname[6]); + Assert(parse->valuesN > 0 && parse->valuesN <=3D 5); + + if (parse->arraysize > 0) + parse->vas[parse->natts - 1]->stavalues[parse->valuesN-1] =3D + (Datum *) palloc(parse->arraysize * sizeof(Datum)); + else + parse->vas[parse->natts - 1]->stavalues[parse->valuesN-1] =3D NULL; + + parse->vas[attnum]->numvalues[parse->valuesN-1] =3D parse->arraysize;= + parse->state =3D JS_EXPECT_VALUES_VALUE; + } + else + elog(ERROR, "Unknown stat parameter in JSON string: %s", fname); + } +} + +static void +json_stat_array_end(void *state) +{ + JsonStatParseState *parse =3D (JsonStatParseState *) state; + parse->arrayval =3D -1; + parse->arraysize =3D 0; + parse->numbersN =3D 0; + parse->valuesN =3D 0; +} + +static void +json_stat_array_element_end(void *state, bool isnull) +{ + JsonStatParseState *parse =3D (JsonStatParseState *) state; + + Assert(!isnull); + + if (parse->arrayval < 0) // Debug + return; + + parse->arrayval++; +} + +static void +json_stat_field_end(void *state, char *fname, bool isnull) +{ + JsonStatParseState *parse =3D (JsonStatParseState *) state; + + Assert(!isnull && parse->arrayval < 0); + parse->state =3D JS_EXPECT_PARAM_FIELD; +} + +static void +json_stat_scalar(void *state, char *token, JsonTokenType tokentype) +{ + JsonStatParseState *parse =3D (JsonStatParseState *) state; + Datum value; + int attnum =3D parse->natts - 1; + + if (skip_attr) + return; + + switch (parse->state) + { + case JS_EXPECT_NSPNAME_VALUE: + Assert(tokentype =3D=3D JSON_TOKEN_STRING); + parse->nspname =3D token; + break; + + case JS_EXPECT_RELNAME_VALUE: + Assert(tokentype =3D=3D JSON_TOKEN_STRING); + parse->relname =3D token; + break; + + case JS_EXPECT_SLOTSNUM_VALUE: + Assert(tokentype =3D=3D JSON_TOKEN_NUMBER); + if (strcmp(token, "5") !=3D 0) + elog(ERROR, "Incompatible PostgreSQL version"); + break; + + case JS_EXPECT_RELPAGES_VALUE: + Assert(tokentype =3D=3D JSON_TOKEN_NUMBER); + value =3D DirectFunctionCall1(int4in, CStringGetDatum(token)); + parse->relpages =3D DatumGetInt32(value); + break; + + case JS_EXPECT_RELTUPLES_VALUE: + Assert(tokentype =3D=3D JSON_TOKEN_NUMBER); + value =3D DirectFunctionCall1(float4in, CStringGetDatum(token)); + parse->reltuples =3D DatumGetFloat4(value); + break; + + case JS_EXPECT_ATTNAME_FIELD: + Assert(0); + break; + + case JS_EXPECT_ATTNAME_VALUE: + { + int i; + + Assert(tokentype =3D=3D JSON_TOKEN_STRING); + + for (i =3D 0; i < parse->rel->rd_att->natts; i++) + { + if (strcmp(NameStr(parse->rel->rd_att->attrs[i].attname), token) =3D=3D= 0) + break; + } + + if (i =3D=3D parse->rel->rd_att->natts) + { + /* + * It is a valid case when a foreign table doesn't have all the + * attributes from the base relation. + */ + skip_attr =3D true; + parse->state =3D JS_EXPECT_ATTNAME_FIELD; + } + else + { + /* Initialize new storage for the attribute. */ + parse->natts++; + attnum++; + parse->vas[attnum] =3D examine_attribute(parse->rel, i+1, NULL); + parse->vas[attnum]->stats_valid =3D true; + Assert(parse->vas[attnum] !=3D NULL); + } + } + break; + + case JS_EXPECT_INH_VALUE: + Assert(tokentype =3D=3D JSON_TOKEN_STRING); + /* XXX */ + break; + + case JS_EXPECT_WIDTH_VALUE: + Assert(tokentype =3D=3D JSON_TOKEN_NUMBER); + value =3D DirectFunctionCall1(int4in, CStringGetDatum(token)); + parse->vas[attnum]->stawidth =3D DatumGetInt32(value); + break; + + case JS_EXPECT_DISTINCT_VALUE: + Assert(tokentype =3D=3D JSON_TOKEN_NUMBER); + value =3D DirectFunctionCall1(float4in, CStringGetDatum(token)); + parse->vas[attnum]->stadistinct =3D DatumGetFloat4(value); + break; + + case JS_EXPECT_NULLFRAC_VALUE: + Assert(tokentype =3D=3D JSON_TOKEN_NUMBER); + value =3D DirectFunctionCall1(float4in, CStringGetDatum(token)); + parse->vas[attnum]->stanullfrac =3D DatumGetFloat4(value); + break; + + case JS_EXPECT_KIND_VALUE: + Assert(tokentype =3D=3D JSON_TOKEN_NUMBER && parse->arrayval >=3D 0); + value =3D DirectFunctionCall1(int2in, CStringGetDatum(token)); + parse->vas[attnum]->stakind[parse->arrayval] =3D DatumGetInt16(value);= + break; + + case JS_EXPECT_STAOP_VALUE: + Assert(tokentype =3D=3D JSON_TOKEN_NUMBER && parse->arrayval >=3D 0); + value =3D DirectFunctionCall1(oidin, CStringGetDatum(token)); + parse->vas[attnum]->staop[parse->arrayval] =3D DatumGetObjectId(value)= ; + break; + + case JS_EXPECT_STACOLL_VALUE: + Assert(tokentype =3D=3D JSON_TOKEN_NUMBER && parse->arrayval >=3D 0); + value =3D DirectFunctionCall1(oidin, CStringGetDatum(token)); + parse->vas[attnum]->stacoll[parse->arrayval] =3D DatumGetObjectId(valu= e); + break; + + case JS_EXPECT_ARRSIZE_VALUE: + Assert(tokentype =3D=3D JSON_TOKEN_NUMBER && parse->arrayval < 0); + value =3D DirectFunctionCall1(int4in, CStringGetDatum(token)); + parse->arraysize =3D DatumGetInt32(value); + Assert(parse->arraysize > 0); + break; + + case JS_EXPECT_NUMBERS_VALUE: + { + int n =3D parse->numbersN; + int m =3D parse->arrayval; + + Assert(parse->valuesN =3D=3D 0); + Assert(tokentype =3D=3D JSON_TOKEN_NUMBER && n > 0 && n <=3D 5); + Assert(m >=3D 0 && m < parse->arraysize); + + value =3D DirectFunctionCall1(float4in, CStringGetDatum(token)); + parse->vas[attnum]->stanumbers[n-1][m] =3D DatumGetFloat4(value); + } + break; + + case JS_EXPECT_VALUES_VALUE: + { + int n =3D parse->valuesN; + int m =3D parse->arrayval; + Form_pg_attribute att =3D parse->vas[attnum]->attr; + Oid typinput; + Oid typioparam; + + Assert(parse->numbersN =3D=3D 0); + Assert(tokentype =3D=3D JSON_TOKEN_NUMBER && n > 0 && n <=3D 5); + Assert(m >=3D 0 && m < parse->arraysize); + + getTypeInputInfo(att->atttypid, &typinput, &typioparam); + parse->vas[attnum]->stavalues[n-1][m] =3D + OidInputFunctionCall(typinput, token, + typioparam, att->atttypmod); + } + break; + + default: + elog(ERROR, "Unexpected token type: %d. Token: %s. State: %d.", + tokentype, token, parse->state); + } +} + +static void +parse_statjson(Relation rel, char *buffer, size_t size, + VacAttrStats **vacattrstats, int *natts, + BlockNumber *relpages, double *reltuples) +{ + JsonLexContext *lex; + JsonSemAction sem; + JsonStatParseState parse; + + /* Set up our private parsing context. */ + parse.state =3D JS_EXPECT_PARAM_FIELD; + parse.arraysize =3D -1; + parse.arrayval =3D -1; + parse.numbersN =3D -1; + parse.vas =3D vacattrstats; + parse.natts =3D 0; + parse.rel =3D rel; + skip_attr =3D false; + + /* Create a JSON lexing context. */ + lex =3D makeJsonLexContextCstringLen(buffer, size, PG_UTF8, true); + + /* Set up semantic actions. */ + sem.semstate =3D &parse; + sem.object_start =3D NULL; + sem.object_end =3D NULL; + sem.array_start =3D NULL; + sem.array_end =3D json_stat_array_end; + sem.object_field_start =3D json_stat_object_field_start; + sem.object_field_end =3D json_stat_field_end; + sem.array_element_start =3D NULL; + sem.array_element_end =3D json_stat_array_element_end; + sem.scalar =3D json_stat_scalar; + + /* Run the actual JSON parser. */ + pg_parse_json(lex, &sem); + *natts =3D parse.natts; + *relpages =3D parse.relpages; + *reltuples =3D parse.reltuples; +} + +static bool +update_foreign_relation_stat(Relation rel) +{ + VacAttrStats **vacattrstats =3D (VacAttrStats **) + palloc0(rel->rd_att->natts * sizeof(VacAttrStats *)); + char *statstr; + int natts =3D 0; + FdwRoutine *fdwroutine; + BlockNumber relallvisible; + BlockNumber numpages; + double numtuples; + + fdwroutine =3D GetFdwRoutineForRelation(rel, false); + Assert(fdwroutine !=3D NULL); + + if (fdwroutine->GetForeignRelStat =3D=3D NULL || strcmp(NameStr(rel->rd= _rel->relname), "ftable") !=3D 0) + return false; + + statstr =3D fdwroutine->GetForeignRelStat(rel); + + if (!statstr) + return false; + /* + * Extract statistics from the JSON string. + */ + parse_statjson(rel, statstr, strlen(statstr), vacattrstats, &natts, &nu= mpages, &numtuples); + + update_attstats(RelationGetRelid(rel), false, natts, vacattrstats); + + visibilitymap_count(rel, &relallvisible, NULL); + vac_update_relstats(rel, numpages, numtuples, relallvisible, false, + InvalidTransactionId, InvalidMultiXactId, false); + + pfree(vacattrstats); + return true; +} + +/* + * relstat_to_json + * + */ +const char * +relstat_to_json(const char *relname) +{ + RangeVar *relvar; + Relation rel; + Relation sd; + List *relname_list; + int attno; + StringInfo str =3D makeStringInfo(); + int attnum =3D 0; + + relname_list =3D stringToQualifiedNameList(relname); + relvar =3D makeRangeVarFromNameList(relname_list); + rel =3D relation_openrv(relvar, AccessShareLock); + + if (rel->rd_rel->relkind !=3D RELKIND_RELATION) + { + relation_close(rel, AccessShareLock); + elog(ERROR, + "Can be used for ordinary relation only. Reltype: %c.", + rel->rd_rel->relkind); + } + + /* JSON header of overall relation statistics. */ + appendStringInfoString(str, "{"); + appendStringInfo(str, "\"namespace\" : \"%s\", \"relname\" : \"%s\", \"= sta_num_slots\" : %d, ", + get_namespace_name(rel->rd_rel->relnamespace), + NameStr(rel->rd_rel->relname), STATISTIC_NUM_SLOTS); + + appendStringInfo(str, "\"relpages\" : %u, \"reltuples\" : %f, ", + rel->rd_rel->relpages, rel->rd_rel->reltuples); + + appendStringInfo(str, "\"attrs\" : ["); + sd =3D table_open(StatisticRelationId, RowExclusiveLock); + + for (attno =3D 0; attno < rel->rd_att->natts; attno++) + { + HeapTuple stup; + Datum values[Natts_pg_statistic]; + bool nulls[Natts_pg_statistic]; + int i, k; + + stup =3D SearchSysCache3(STATRELATTINH, + ObjectIdGetDatum(RelationGetRelid(rel)), + Int16GetDatum(rel->rd_att->attrs[attno].attnum), + BoolGetDatum(false)); + + if (!HeapTupleIsValid(stup)) + /* Go to the next attribute, if we haven't statistics for. */ + continue; + + if (attnum++ > 0) + appendStringInfoString(str, ", "); + + heap_deform_tuple(stup, RelationGetDescr(sd), values, nulls); + + /* JSON header of attrribute statistics. */ + appendStringInfo(str, "{\"attname\" : \"%s\", \"inh\" : \"%s\", \"null= frac\" : %f, \"width\" : %d, \"distinct\" : %f, ", + NameStr(*attnumAttName(rel, rel->rd_att->attrs[attno].attnum)), + DatumGetBool(values[Anum_pg_statistic_stainherit - 1]) ? "true" : "fa= lse", + DatumGetFloat4(values[Anum_pg_statistic_stanullfrac - 1]), + DatumGetInt32(values[Anum_pg_statistic_stawidth - 1]), + DatumGetFloat4(values[Anum_pg_statistic_stadistinct - 1])); + + appendStringInfoString(str, "\"stakind\" : ["); + i =3D Anum_pg_statistic_stakind1 - 1; + for (k =3D 0; k < STATISTIC_NUM_SLOTS; k++) + { + appendStringInfo(str, "%d", DatumGetInt16(values[i++])); + if (k < STATISTIC_NUM_SLOTS - 1) + appendStringInfo(str, ","); + } + appendStringInfoString(str, "], \"staop\" : ["); + + i =3D Anum_pg_statistic_staop1 - 1; + for (k =3D 0; k < STATISTIC_NUM_SLOTS; k++) + { + appendStringInfo(str, "%u", DatumGetObjectId(values[i++])); + if (k < STATISTIC_NUM_SLOTS - 1) + appendStringInfoChar(str, ','); + } + appendStringInfoString(str, "], \"stacoll\" : ["); + + i =3D Anum_pg_statistic_stacoll1 - 1; + for (k =3D 0; k < STATISTIC_NUM_SLOTS; k++) + { + appendStringInfo(str, "%u", DatumGetObjectId(values[i++])); + if (k < STATISTIC_NUM_SLOTS - 1) + appendStringInfoChar(str, ','); + } + + appendStringInfoString(str, "], "); + + for (k =3D 0; k < STATISTIC_NUM_SLOTS; k++) + { + bool isnull; + Datum val; + + val =3D SysCacheGetAttr(STATRELATTINH, stup, + Anum_pg_statistic_stavalues1 + k, + &isnull); + + if (isnull) + appendStringInfo(str, "\"values%d\" : [ ]", k+1); + else + { + ArrayType *v =3D DatumGetArrayTypeP(val); + + appendStringInfo(str, "\"nn\" : %d, ", + ArrayGetNItems(ARR_NDIM(v), ARR_DIMS(v))); + appendStringInfo(str, "\"values%d\" : ", k+1); + arr_to_json(val, str); + } + appendStringInfoString(str, ", "); + } + + /* --- Extract numbers --- */ + for (k =3D 0; k < STATISTIC_NUM_SLOTS; k++) + { + bool isnull; + Datum val; + + if (k > 0) + appendStringInfoString(str, ", "); + + val =3D SysCacheGetAttr(STATRELATTINH, stup, + Anum_pg_statistic_stanumbers1 + k, + &isnull); + if (isnull) + appendStringInfo(str, "\"numbers%d\" : [ ]", k+1); + else + { + ArrayType *v =3D DatumGetArrayTypeP(val); + + appendStringInfo(str, "\"nn\" : %d, ", + ArrayGetNItems(ARR_NDIM(v), ARR_DIMS(v))); + appendStringInfo(str, "\"numbers%d\" : ", k+1); + arr_to_json(val, str); + } + } + + appendStringInfoString(str, "}"); + ReleaseSysCache(stup); + } + + if (attnum =3D=3D 0) + appendStringInfoString(str, " ]"); + else + appendStringInfoChar(str, ']'); + appendStringInfoString(str, "}"); + + table_close(sd, RowExclusiveLock); + relation_close(rel, AccessShareLock); + + return str->data; +} =20 /* * update_attstats() -- update attribute statistics for one relation diff --git a/src/backend/commands/vacuum.c b/src/backend/commands/vacuum.= c index 23eb605d4c..963ee51523 100644 --- a/src/backend/commands/vacuum.c +++ b/src/backend/commands/vacuum.c @@ -882,6 +882,7 @@ get_all_vacuum_rels(int options) * them. */ if (classForm->relkind !=3D RELKIND_RELATION && + classForm->relkind !=3D RELKIND_FOREIGN_TABLE && classForm->relkind !=3D RELKIND_MATVIEW && classForm->relkind !=3D RELKIND_PARTITIONED_TABLE) continue; @@ -1795,7 +1796,17 @@ vacuum_rel(Oid relid, RangeVar *relation, VacuumPa= rams *params) /* * Check that it's of a vacuumable relkind. */ - if (onerel->rd_rel->relkind !=3D RELKIND_RELATION && + if (onerel->rd_rel->relkind =3D=3D RELKIND_FOREIGN_TABLE) + { + relation_close(onerel, lmode); + PopActiveSnapshot(); + CommitTransactionCommand(); + if (params->options & VACOPT_ANALYZE) + return true; + else + return false; + } + else if (onerel->rd_rel->relkind !=3D RELKIND_RELATION && onerel->rd_rel->relkind !=3D RELKIND_MATVIEW && onerel->rd_rel->relkind !=3D RELKIND_TOASTVALUE && onerel->rd_rel->relkind !=3D RELKIND_PARTITIONED_TABLE) diff --git a/src/backend/utils/adt/json.c b/src/backend/utils/adt/json.c index a7a91b72f6..adea67a18d 100644 --- a/src/backend/utils/adt/json.c +++ b/src/backend/utils/adt/json.c @@ -656,6 +656,12 @@ add_json(Datum val, bool is_null, StringInfo result,= datum_to_json(val, is_null, result, tcategory, outfuncoid, key_scalar);= } =20 +void +arr_to_json(Datum array, StringInfo result) +{ + array_to_json_internal(array, result, false); +} + /* * SQL function array_to_json(row) */ diff --git a/src/include/commands/vacuum.h b/src/include/commands/vacuum.= h index a4cd721400..9c1b89d5b6 100644 --- a/src/include/commands/vacuum.h +++ b/src/include/commands/vacuum.h @@ -283,6 +283,7 @@ extern Relation vacuum_open_relation(Oid relid, Range= Var *relation, extern void analyze_rel(Oid relid, RangeVar *relation, VacuumParams *params, List *va_cols, bool in_outer_xact, BufferAccessStrategy bstrategy); +extern const char *relstat_to_json(const char *relname); extern bool std_typanalyze(VacAttrStats *stats); =20 /* in utils/misc/sampling.c --- duplicate of declarations in utils/sampl= ing.h */ diff --git a/src/include/foreign/fdwapi.h b/src/include/foreign/fdwapi.h index 95556dfb15..51979ab3bf 100644 --- a/src/include/foreign/fdwapi.h +++ b/src/include/foreign/fdwapi.h @@ -169,6 +169,7 @@ typedef bool (*IsForeignScanParallelSafe_function) (P= lannerInfo *root, typedef List *(*ReparameterizeForeignPathByChild_function) (PlannerInfo = *root, List *fdw_private, RelOptInfo *child_rel); +typedef char *(*GetForeignRelStat_function) (Relation rel); =20 /* * FdwRoutine is the struct returned by a foreign-data wrapper's handler= @@ -246,6 +247,7 @@ typedef struct FdwRoutine =20 /* Support functions for path reparameterization. */ ReparameterizeForeignPathByChild_function ReparameterizeForeignPathByCh= ild; + GetForeignRelStat_function GetForeignRelStat; } FdwRoutine; =20 =20 diff --git a/src/include/utils/json.h b/src/include/utils/json.h index 4345fbdc31..a6b51bf191 100644 --- a/src/include/utils/json.h +++ b/src/include/utils/json.h @@ -20,5 +20,6 @@ extern void escape_json(StringInfo buf, const char *str); extern char *JsonEncodeDateTime(char *buf, Datum value, Oid typid, const int *tzp); +extern void arr_to_json(Datum array, StringInfo result); =20 #endif /* JSON_H */ --=20 2.17.1 --------------E1F506154831107939E331AC--