Thread: Indices and Foreign Tables
Hi,
I have a question regarding the use of indices when querying foreign data tables (using postgres_fdw of 9.3 to another
postgresDB).
Everything works fine beside the fact that the indices which are defined in the foreign DB are not used at all when I
doquery it through the foreign data wrapper. This leads to an extremely slow query performance.
Here is what I did:
CREATE EXTENSION postgres_fdw;
CREATE SERVER app_db
FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (dbname ‘<DBNAME>', host 'localhost');
CREATE USER MAPPING for openbis
SERVER app_db
OPTIONS (user ‘<USRE>', password ‘<PW>');
CREATE FOREIGN TABLE data_sets_fdw
(
id bigint,
code code,
location file_path
)
SERVER app_db OPTIONS (table_name 'data_sets’);
The data_sets_fdw corresponds to the table in the other DB which is defined as this:
db=> \d data_sets
Table "public.data_sets"
Column | Type | Modifiers
----------+-----------+--------------------------------------------------------
id | bigint | not null default nextval('data_sets_id_seq'::regclass)
code | code | not null
location | file_path | not null
Indexes:
"data_sets_pkey" PRIMARY KEY, btree (id)
"data_sets_code_key" UNIQUE CONSTRAINT, btree (code)
"data_sets_code_idx" btree (code)
When I realised that the queries are so slow I ran an EXPLAIN ANALYZE which shows that the indices are not used. I can
alsopost the query plan if it helps.
So am I doing something wrong here, or is this not possible that the indices are used by the fdw?
Any hints are appreciated.
Cheers
Manuel
--
Kohler Manuel (ID SIS)
Research Informatics, Scientific IT Services (ID ETHZ)
Quantitative Genomics Facility (QGF), D-BSSE
ETH Zurich, Mattenstr. 26 (1078 1.02), CH-4058 Basel, +41 61 387 3132
Kohler Manuel wrote:
> I have a question regarding the use of indices when querying foreign data tables (using postgres_fdw
> of 9.3 to another postgres DB).
> Everything works fine beside the fact that the indices which are defined in the foreign DB are not
> used at all when I do query it through the foreign data wrapper. This leads to an extremely slow query
> performance.
>
> Here is what I did:
>
> CREATE EXTENSION postgres_fdw;
>
> CREATE SERVER app_db
> FOREIGN DATA WRAPPER postgres_fdw
> OPTIONS (dbname ‘<DBNAME>', host 'localhost');
>
> CREATE USER MAPPING for openbis
> SERVER app_db
> OPTIONS (user ‘<USRE>', password ‘<PW>');
>
> CREATE FOREIGN TABLE data_sets_fdw
> (
> id bigint,
> code code,
> location file_path
> )
> SERVER app_db OPTIONS (table_name 'data_sets’);
>
> The data_sets_fdw corresponds to the table in the other DB which is defined as this:
>
> db=> \d data_sets
> Table "public.data_sets"
> Column | Type | Modifiers
> ----------+-----------+--------------------------------------------------------
> id | bigint | not null default nextval('data_sets_id_seq'::regclass)
> code | code | not null
> location | file_path | not null
> Indexes:
> "data_sets_pkey" PRIMARY KEY, btree (id)
> "data_sets_code_key" UNIQUE CONSTRAINT, btree (code)
> "data_sets_code_idx" btree (code)
>
> When I realised that the queries are so slow I ran an EXPLAIN ANALYZE which shows that the indices
> are not used. I can also post the query plan if it helps.
>
> So am I doing something wrong here, or is this not possible that the indices are used by the fdw?
The indices should be used.
Did you ANALYZE the remote table on the remote database?
What is the remote query (EXPLAIN VERBOSE)?
What do you get for EXPLAIN ANALYZE of the remote query when executed on the remote database?
Yours,
Laurenz Albe
Hi,
here are the Query plans. The first plan is on the source database directly. So no fdw involved:
source_db=# EXPLAIN ANALYZE select ds.code, count(*), sum(dsf.size_in_bytes) as
"raw_size",pg_size_pretty(sum(dsf.size_in_bytes))as "size" from data_set_files dsf, data_sets ds where dsf.parent_id is
nulland dsf.dase_id=ds.id group by ds.code order by raw_size desc;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=341248.80..341281.56 rows=13103 width=34) (actual time=695.519..724.286 rows=13839 loops=1)
Sort Key: (sum(dsf.size_in_bytes))
Sort Method: quicksort Memory: 2283kB
-> HashAggregate (cost=340188.93..340352.71 rows=13103 width=34) (actual time=536.229..615.115 rows=13839 loops=1)
-> Nested Loop (cost=0.56..340057.90 rows=13103 width=34) (actual time=0.104..456.139 rows=13839 loops=1)
-> Seq Scan on data_sets ds (cost=0.00..385.39 rows=13839 width=34) (actual time=0.022..40.113
rows=13839loops=1)
-> Index Scan using data_set_files_dase_id_parent_id_idx on data_set_files dsf (cost=0.56..24.43
rows=11width=16) (actual time=0.015..0.019 rows=1 loops=13839)
Index Cond: (((dase_id)::bigint = ds.id) AND (parent_id IS NULL))
Total runtime: 752.695 ms
(9 rows)
Here is the same query with fdw:
db=# EXPLAIN VERBOSE select ds.code, count(*), sum(dsf.size_in_bytes) as
"raw_size",pg_size_pretty(sum(dsf.size_in_bytes))as "size" from data_set_files_fdw dsf, data_sets_fdw ds where
dsf.parent_idis null and dsf.dase_id=ds.id group by ds.code order by raw_size desc;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------
Sort (cost=327.81..327.97 rows=64 width=40)
Output: ds.code, (count(*)), (sum(dsf.size_in_bytes)), (pg_size_pretty(sum(dsf.size_in_bytes)))
Sort Key: (sum(dsf.size_in_bytes))
-> HashAggregate (cost=325.09..325.89 rows=64 width=40)
Output: ds.code, count(*), sum(dsf.size_in_bytes), pg_size_pretty(sum(dsf.size_in_bytes))
-> Hash Join (cost=270.61..324.45 rows=64 width=40)
Output: dsf.size_in_bytes, ds.code
Hash Cond: (ds.id = (dsf.dase_id)::bigint)
-> Foreign Scan on public.data_sets_fdw ds (cost=100.00..148.40 rows=1280 width=40)
Output: ds.id, ds.code, ds.location
Remote SQL: SELECT id, code FROM public.data_sets
-> Hash (cost=170.48..170.48 rows=10 width=16)
Output: dsf.size_in_bytes, dsf.dase_id
-> Foreign Scan on public.data_set_files_fdw dsf (cost=100.00..170.48 rows=10 width=16)
Output: dsf.size_in_bytes, dsf.dase_id
Filter: (dsf.parent_id IS NULL)
Remote SQL: SELECT dase_id, parent_id, size_in_bytes FROM public.data_set_files
(17 rows)
And also with ANALYZE:
db=# EXPLAIN ANALYZE select ds.code, count(*), sum(dsf.size_in_bytes) as
"raw_size",pg_size_pretty(sum(dsf.size_in_bytes))as "size" from data_set_files_fdw dsf, data_sets_fdw ds where
dsf.parent_idis null and dsf.dase_id=ds.id group by ds.code order by raw_size desc;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=327.81..327.97 rows=64 width=40) (actual time=219401.864..219414.641 rows=13839 loops=1)
Sort Key: (sum(dsf.size_in_bytes))
Sort Method: quicksort Memory: 2283kB
-> HashAggregate (cost=325.09..325.89 rows=64 width=40) (actual time=219327.664..219363.709 rows=13839 loops=1)
-> Hash Join (cost=270.61..324.45 rows=64 width=40) (actual time=219127.848..219277.308 rows=13839 loops=1)
Hash Cond: (ds.id = (dsf.dase_id)::bigint)
-> Foreign Scan on data_sets_fdw ds (cost=100.00..148.40 rows=1280 width=40) (actual
time=1.057..77.415rows=13839 loops=1)
-> Hash (cost=170.48..170.48 rows=10 width=16) (actual time=219126.713..219126.713 rows=13839 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 649kB
-> Foreign Scan on data_set_files_fdw dsf (cost=100.00..170.48 rows=10 width=16) (actual
time=1082.614..219083.326rows=13839 loops=1)
Filter: (parent_id IS NULL)
Rows Removed by Filter: 35726596
Total runtime: 219438.925 ms
(13 rows)
Regards
Manuel
--
Kohler Manuel (ID SIS)
Research Informatics, Scientific IT Services (ID ETHZ)
Quantitative Genomics Facility (QGF), D-BSSE
ETH Zurich, Mattenstr. 26 (1078 1.02), CH-4058 Basel, +41 61 387 3132
On 04 Apr 2014, at 17:01, Albe Laurenz <laurenz.albe@wien.gv.at> wrote:
> Kohler Manuel wrote:
>> I have a question regarding the use of indices when querying foreign data tables (using postgres_fdw
>> of 9.3 to another postgres DB).
>> Everything works fine beside the fact that the indices which are defined in the foreign DB are not
>> used at all when I do query it through the foreign data wrapper. This leads to an extremely slow query
>> performance.
>>
>> Here is what I did:
>>
>> CREATE EXTENSION postgres_fdw;
>>
>> CREATE SERVER app_db
>> FOREIGN DATA WRAPPER postgres_fdw
>> OPTIONS (dbname ‘<DBNAME>', host 'localhost');
>>
>> CREATE USER MAPPING for openbis
>> SERVER app_db
>> OPTIONS (user ‘<USRE>', password ‘<PW>');
>>
>> CREATE FOREIGN TABLE data_sets_fdw
>> (
>> id bigint,
>> code code,
>> location file_path
>> )
>> SERVER app_db OPTIONS (table_name 'data_sets’);
>>
>> The data_sets_fdw corresponds to the table in the other DB which is defined as this:
>>
>> db=> \d data_sets
>> Table "public.data_sets"
>> Column | Type | Modifiers
>> ----------+-----------+--------------------------------------------------------
>> id | bigint | not null default nextval('data_sets_id_seq'::regclass)
>> code | code | not null
>> location | file_path | not null
>> Indexes:
>> "data_sets_pkey" PRIMARY KEY, btree (id)
>> "data_sets_code_key" UNIQUE CONSTRAINT, btree (code)
>> "data_sets_code_idx" btree (code)
>>
>> When I realised that the queries are so slow I ran an EXPLAIN ANALYZE which shows that the indices
>> are not used. I can also post the query plan if it helps.
>>
>> So am I doing something wrong here, or is this not possible that the indices are used by the fdw?
>
> The indices should be used.
>
> Did you ANALYZE the remote table on the remote database?
> What is the remote query (EXPLAIN VERBOSE)?
> What do you get for EXPLAIN ANALYZE of the remote query when executed on the remote database?
>
> Yours,
> Laurenz Albe
"Kohler Manuel (ID SIS)" <manuel.kohler@id.ethz.ch> writes:
> here are the Query plans. The first plan is on the source database directly. So no fdw involved:
Do you have use_remote_estimate enabled? The rowcount estimates for the
foreign tables seem pretty far from reality; use_remote_estimates should
help.
regards, tom lane
On 04/04/14 17:14, Kohler Manuel (ID SIS) wrote:
> Here is the same query with fdw:
>
> db=# EXPLAIN VERBOSE select ds.code, count(*), sum(dsf.size_in_bytes) as
"raw_size",pg_size_pretty(sum(dsf.size_in_bytes))as "size" from data_set_files_fdw dsf, data_sets_fdw ds where
dsf.parent_idis null and dsf.dase_id=ds.id group by ds.code order by raw_size desc;
> QUERY PLAN
> ---------------------------------------------------------------------------------------------------------------
> Sort (cost=327.81..327.97 rows=64 width=40)
> Output: ds.code, (count(*)), (sum(dsf.size_in_bytes)), (pg_size_pretty(sum(dsf.size_in_bytes)))
> Sort Key: (sum(dsf.size_in_bytes))
> -> HashAggregate (cost=325.09..325.89 rows=64 width=40)
> Output: ds.code, count(*), sum(dsf.size_in_bytes), pg_size_pretty(sum(dsf.size_in_bytes))
> -> Hash Join (cost=270.61..324.45 rows=64 width=40)
> Output: dsf.size_in_bytes, ds.code
> Hash Cond: (ds.id = (dsf.dase_id)::bigint)
> -> Foreign Scan on public.data_sets_fdw ds (cost=100.00..148.40 rows=1280 width=40)
> Output: ds.id, ds.code, ds.location
> Remote SQL: SELECT id, code FROM public.data_sets
> -> Hash (cost=170.48..170.48 rows=10 width=16)
> Output: dsf.size_in_bytes, dsf.dase_id
> -> Foreign Scan on public.data_set_files_fdw dsf (cost=100.00..170.48 rows=10 width=16)
> Output: dsf.size_in_bytes, dsf.dase_id
> Filter: (dsf.parent_id IS NULL)
> Remote SQL: SELECT dase_id, parent_id, size_in_bytes FROM public.data_set_files
> (17 rows)
>
> And also with ANALYZE:
>
> db=# EXPLAIN ANALYZE select ds.code, count(*), sum(dsf.size_in_bytes) as
"raw_size",pg_size_pretty(sum(dsf.size_in_bytes))as "size" from data_set_files_fdw dsf, data_sets_fdw ds where
dsf.parent_idis null and dsf.dase_id=ds.id group by ds.code order by raw_size desc;
> QUERY PLAN
>
--------------------------------------------------------------------------------------------------------------------------------------------------------------
> Sort (cost=327.81..327.97 rows=64 width=40) (actual time=219401.864..219414.641 rows=13839 loops=1)
> Sort Key: (sum(dsf.size_in_bytes))
> Sort Method: quicksort Memory: 2283kB
> -> HashAggregate (cost=325.09..325.89 rows=64 width=40) (actual time=219327.664..219363.709 rows=13839 loops=1)
> -> Hash Join (cost=270.61..324.45 rows=64 width=40) (actual time=219127.848..219277.308 rows=13839
loops=1)
> Hash Cond: (ds.id = (dsf.dase_id)::bigint)
> -> Foreign Scan on data_sets_fdw ds (cost=100.00..148.40 rows=1280 width=40) (actual
time=1.057..77.415rows=13839 loops=1)
> -> Hash (cost=170.48..170.48 rows=10 width=16) (actual time=219126.713..219126.713 rows=13839
loops=1)
> Buckets: 1024 Batches: 1 Memory Usage: 649kB
> -> Foreign Scan on data_set_files_fdw dsf (cost=100.00..170.48 rows=10 width=16) (actual
time=1082.614..219083.326rows=13839 loops=1)
> Filter: (parent_id IS NULL)
> Rows Removed by Filter: 35726596
> Total runtime: 219438.925 ms
> (1
Do you know that you can combine VERBOSE and ANALYZE in one EXPLAIN?
EXPLAIN (ANALYZE, VERBOSE) SELECT ...
The problem is that your qualifier is not pushed down to the backend
database. So, you pull (35726596+13839) rows (which is the complete
table) from data_set_files in the remote database, filter them locally
and throw 35726596 of them away. Then you copy the remote data_sets
table (13839 rows). And then you join them.
I don't think use_remote_estimate can help a lot in this situation.
We tried to use postgres_fdw in a similar approach last year. Then
switched to dblink because, although it is possible for FDW to push
qualifiers to the backend, it too often does not do so. Then it copies
large tables over the network and scans them sequentially on the local
machine. Also, LIMIT for instance is never pushed to the backend.
Now, we create functions like this:
CREATE OR REPLACE FUNCTION __get_client_metrics_on(srv TEXT)
RETURNS TABLE(loginid TEXT,
...)
AS $def$
SELECT tb.*
FROM dblink($1, $$
-- remote query starts here
SELECT c.loginid, ...
FROM clients c
JOIN ...
-- remote query ends here
$$) AS tb(loginid TEXT,
...)
$def$ LANGUAGE sql VOLATILE SECURITY definer ROWS ... COST ...;
CREATE OR REPLACE FUNCTION get_client_metrics()
RETURNS TABLE(srv TEXT,
loginid TEXT,
...)
AS $def$
SELECT s.srvname, rem.*
FROM production_servers() s
CROSS JOIN __get_client_metrics_on(s.srvname) rem
$def$ LANGUAGE sql STABLE SECURITY invoker;
The production_servers function is defined more or less like this:
CREATE OR REPLACE FUNCTION production_servers()
RETURNS TABLE(srvname TEXT) AS $def$
SELECT s.srvname::TEXT
FROM pg_catalog.pg_foreign_data_wrapper w
JOIN pg_catalog.pg_foreign_server s ON (s.srvfdw=w.oid)
WHERE w.fdwname IN ('postgres_fdw', 'dblink_fdw')
$def$ LANGUAGE sql STABLE;
Then you can
SELECT ... FROM get_client_metrics() JOIN ... WHERE ...
It is much more work than simply import tables from the backend as
foreign tables. But for us it worked very well and we achieved in many
cases much better performance than before. This new approach replaces a
system where all the production databases pour their data into a large
one using bucardo only to do some analysis on the complete data set.
And there is more you can achieve with dblink. For instance you can
execute the remote queries concurrently (you don't even need PLPGSQL for
that, pure SQL is sufficient).
Torsten