This is the server i am running
select version();
version
---------------------------------------------------------------------------
PostgreSQL 10.6 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-36), 64-bit
(1 row)
I started by writing the select (ext.t_event and ext.t_event_data are two foreign table that oracle_fdw (version 1.1) takes from a remote oracle db)
select
te.id_data,
te.id_device,
te.date_write,
te.date_event,
ted.i_inout,
ted.value
from ext.t_event te, ext.t_event_data ted
where te.id_device =2749651
and te.date_event >= '2019-01-16'and te.date_event < '2019-01-17'
and te.id_data=ted.id_data;
Takes about 10 seconds to fetch the entire recordset (3600 records).
But then i turned the select into a insert select
insert into stg_data
select
te.id_data,
te.id_device,
te.date_write,
te.date_event,
ted.i_inout,
ted.value
from ext.t_event te, ext.t_event_data ted
where te.id_device =2749651
and te.date_event >= '2019-01-16'and te.date_event < '2019-01-17'
and te.id_data=ted.id_data;
and i was forced to kill the query, it had been running for more than 30 minutes!
After a few hours of struggle and desperate attempts i decided to try this one
insert into stg_data
select
te.id_data,
te.id_device,
te.date_write,
te.date_event,
ted.i_inout,
ted.value
from ext.t_event te, ext.t_event_data ted
where te.id_device =2749651
and te.date_event >= '2019-01-16'and te.date_event < '2019-01-17'
and te.id_data=ted.id_data
limit 5000;
and...surprise surprise in 20 seconds i had the entire recordset stored in stg_data.
To better understand the differences i decided to analyze the plans.
SELECT NO LIMIT
Foreign Scan (cost=10000.00..20000.00 rows=1000 width=548)
Oracle query: SELECT /*eb01c463a72c3b6350f86f5db25e1353*/ r1."ID_DATA",
r1."ID_DEVICE", r1."DATE_WRITE", r1."DATE_EVENT", r2."I_INOUT",
r2."VALUE" FROM ("DISPATCH"."T_EVENT" r1 INNER JOIN
"DISPATCH"."T_EVENT_DATA" r2 ON (r1."ID_DATA" = r2."ID_DATA") AND
(r1."DATE_EVENT" >= (CAST ('2019-01-16 00:00:00.000000 AD' AS
TIMESTAMP))) AND (r1."DATE_EVENT" <
(CAST ('2019-01-17 00:00:00.000000 AD' AS TIMESTAMP)))
AND (r1."ID_DEVICE" = 2749651))
SELECT WITH LIMIT
Limit (cost=10000.00..20000.00 rows=1000 width=548)
-> Foreign Scan (cost=10000.00..20000.00 rows=1000 width=548)
Oracle query: SELECT /*eb01c463a72c3b6350f86f5db25e1353*/
r1."ID_DATA", r1."ID_DEVICE", r1."DATE_WRITE", r1."DATE_EVENT",
r2."I_INOUT", r2."VALUE" FROM ("DISPATCH"."T_EVENT" r1 INNER
JOIN "DISPATCH"."T_EVENT_DATA" r2 ON (r1."ID_DATA" = r2."ID_DATA")
AND (r1."DATE_EVENT" >= (CAST ('2019-01-16 00:00:00.000000 AD' AS
TIMESTAMP))) AND (r1."DATE_EVENT" < (CAST ('2019-01-17
00:00:00.000000 AD' AS TIMESTAMP))) AND (r1."ID_DEVICE" = 2749651))
So it basically send the same query to Oracle and applies the FILTER locally as soon as the fetch is completed.
Do the INSER-SELECT plan look the same? NOPE!
INSERT_SELECT with LIMIT
Insert on stg_data_hist (cost=10000.00..20010.00 rows=1000 width=548)
-> Limit (cost=10000.00..20000.00 rows=1000 width=548)
-> Foreign Scan (cost=10000.00..20000.00 rows=1000 width=548)
Oracle query: SELECT /*eb01c463a72c3b6350f86f5db25e1353*/
r1."ID_DATA", r1."ID_DEVICE", r1."DATE_WRITE",
r1."DATE_EVENT", r2."I_INOUT", r2."VALUE" FROM
("DISPATCH"."T_EVENT" r1 INNER JOIN
"DISPATCH"."T_EVENT_DATA" r2 ON (r1."ID_DATA" =
r2."ID_DATA") AND (r1."DATE_EVENT" >= (CAST ('2019-01-16
00:00:00.000000 AD' AS TIMESTAMP))) AND (r1."DATE_EVENT" <
(CAST('2019-01-17 00:00:00.000000 AD' AS TIMESTAMP))) AND
(r1."ID_DEVICE" = 2749651))
INSERT-SELECT no LIMIT clause
Insert on stg_data_hist (cost=30012.50..40190.00 rows=5000 width=548)
-> Hash Join (cost=30012.50..40190.00 rows=5000 width=548)
Hash Cond: (te.id_data = ted.id_data)
-> Foreign Scan on t_event te (cost=10000.00..20000.00 rows=1000 width=28)
Oracle query: SELECT /*93379c271b3f1bc08a1dbb94fb89f739*/
r3."ID_DATA", r3."ID_DEVICE", r3."DATE_WRITE", r3."DATE_EVENT"
FROM "DISPATCH"."T_EVENT" r3 WHERE (r3."DATE_EVENT" >=
(CAST ('2019-01-16 00:00:00.000000 AD' AS TIMESTAMP))) AND
(r3."DATE_EVENT" < (CAST ('2019-01-17 00:00:00.000000 AD' AS
TIMESTAMP))) AND (r3."ID_DEVICE" = 2749651)
-> Hash (cost=20000.00..20000.00 rows=1000 width=528)
-> Foreign Scan on t_event_data ted
(cost=10000.00..20000.00 rows=1000 width=528)
Oracle query: SELECT /*21c8741f2fa8a8d13d037c3191e8ac96*/
r4."ID_DATA", r4."I_INOUT", r4."VALUE" FROM
"DISPATCH"."T_EVENT_DATA" r4
And that explains why it takes waaaaaay longer than the other. It's retrieving the date-filtered records from one foreign table, the complete set from the second foreign table and doing the join locally. That is going to take ages!! It's several milion of records vs few thousands.
And finally my two question
1) I want to have the first plan but get rid of the LIMIT clause (send shivers down my spine :-) ). How would you do that? I have no mean to apply filters on the ext.t_event_data except from the join clause.
2) Why the two INSERT-SELECT plan look so different even though the two SELECT plan look so similar?
Thanks for reading and have a nice day
The planner seems to thinks it will just get a few thousand rows either way which is obviously way off, make sure the statistics for the foreign tables are up to date by running 'ANALYZE ext.t_event' and the same for ext.t_event_data because:
https://github.com/laurenz/oracle_fdw
PostgreSQL will not automatically gather statistics for foreign tables with the autovacuum daemon.
Keep in mind that analyzing an Oracle foreign table will result in a full sequential table scan. You can use the table option sample_percent to speed this up by using only a sample of the Oracle table.
The joins is pushed down to Oracle in the select case and in the insert case if limit is used so the only reason I can see for not using it in the insert without limit is the lack of precise table statistics. You could try rewriting the insert query as a CTE (haven't tested this query for obvious reasons):
WITH foreign_data AS (
select
te.id_data,
te.id_device,
te.date_write,
te.date_event,
ted.i_inout,
ted.value
from ext.t_event te, ext.t_event_data ted
where te.id_device =2749651
and te.date_event >= '2019-01-16'and te.date_event < '2019-01-17'
and te.id_data=ted.id_data
)
insert into stg_data from foreign_data
You could also try rewriting the query as an explicit inner join instead of having the join condition in the where clause (te.id_data=ted.id_data).