Closed fernandorb10 closed 3 years ago
laurenz
commented
3 years ago The queries sent to Oracle by oracle_fdw will use indexes just like all other queries, if the Oracle optimizer decides that it makes sense.
If you post the result of
EXPLAIN (ANALYZE, VERBOSE) SELECT ...;I may be able to tell you more.
fernandorb10
commented
3 years ago QUERY PLAN
Sort (cost=2068186.52..2068194.52 rows=1 width=1448) (actual time=418941.017..418941.211 rows=443 loops=1)
Output: (count(*)), lr.generatedtype, ld.dominio_id, ld.showname, ld.description, lr.empresa_id, pn.contract_id, ld.lfc_priority, lr.status_id
Sort Key: (count(*)) DESC
Sort Method: quicksort Memory: 118kB
-> HashAggregate (cost=2068166.50..2068170.51 rows=1 width=1448) (actual time=418940.347..418940.667 rows=443 loops=1)
Output: count(*), lr.generatedtype, ld.dominio_id, ld.showname, ld.description, lr.empresa_id, pn.contract_id, ld.lfc_priority, lr.status_id
Group Key: ld.lfc_priority, lr.status_id, lr.generatedtype, ld.dominio_id, ld.showname, ld.description, lr.empresa_id, pn.contract_id
-> Merge Join (cost=859224.00..1167604.00 rows=25000 width=1440) (actual time=418843.974..418932.218 rows=7321 loops=1)
Output: lr.generatedtype, ld.dominio_id, ld.showname, ld.description, lr.empresa_id, pn.contract_id, ld.lfc_priority, lr.status_id
Merge Cond: ((pn.guid)::text = (lr.guid)::text)
-> Sort (cost=99776.10..103778.60 rows=1000 width=150) (actual time=360965.500..395244.637 rows=48136178 loops=1)
Output: pn.contract_id, pn.guid
Sort Key: pn.guid
Sort Method: external merge Disk: 2219544kB
-> Foreign Scan on "LOGALTYTECA".pt_nodes pn (cost=10000.00..20000.00 rows=1000 width=150) (actual time=65547.909..294049.490 rows=48136178 loops=1)
Output: pn.contract_id, pn.guid
Oracle query: SELECT /*65ccf9ba136dfd0ce38d2408b453f0d2*/ r6."GUID", r6."CONTRACT_ID" FROM "LOGALTYTECA"."PT_NODES" r6
Oracle plan: SELECT STATEMENT
Oracle plan: VIEW index$_join$_001
Oracle plan: HASH JOIN (condition ROWID=ROWID)
Oracle plan: INDEX FAST FULL SCAN NDX_PTNODES_USERID_GUID
Oracle plan: INDEX FAST FULL SCAN NDX_PT_NODES_CONTRACT_ID
-> Sort (cost=759447.89..779460.39 rows=5000 width=1826) (actual time=780.390..784.326 rows=7489 loops=1)
Output: lr.generatedtype, lr.empresa_id, lr.status_id, lr.guid, ld.dominio_id, ld.showname, ld.description, ld.lfc_priority
Sort Key: lr.guid
Sort Method: quicksort Memory: 2154kB
-> Merge Join (cost=199552.21..267632.21 rows=5000 width=1826) (actual time=752.784..771.588 rows=7510 loops=1)
Output: lr.generatedtype, lr.empresa_id, lr.status_id, lr.guid, ld.dominio_id, ld.showname, ld.description, ld.lfc_priority
Merge Cond: (ld.dominio_id = ld2.dominioid)
-> Sort (cost=99776.10..103778.60 rows=1000 width=1826) (actual time=746.102..749.905 rows=7510 loops=1)
Output: lr.generatedtype, lr.empresa_id, lr.status_id, lr.guid, ld.dominio_id, ld.showname, ld.description, ld.lfc_priority
Sort Key: ld.dominio_id
Sort Method: quicksort Memory: 2154kB
-> Foreign Scan (cost=10000.00..20000.00 rows=1000 width=1826) (actual time=59.805..737.793 rows=7510 loops=1)
Output: lr.generatedtype, lr.empresa_id, lr.status_id, lr.guid, ld.dominio_id, ld.showname, ld.description, ld.lfc_priority
Oracle query: SELECT /*11f533c319763a1538997fc3d9386b3a*/ r1."GENERATEDTYPE", r1."EMPRESA_ID", r1."STATUS_ID", r1."GUID", r2."DOMINIO_ID", r2."SHOWNAME", r2."DESCRIPTION", r2."LFC_PRIORITY" FROM ("LGTWEB"."LGTW_REQUESTC" r1 INNER JOIN "LGTWEB"."LGTW_EMPRESA" r2 ON (r1."EMPRESA_ID" = r2."EMPRESAID") AND (r1."CREATED" > (TRUNC(CAST (CAST(:now AS TIMESTAMP WITH TIME ZONE) AS DATE)) - 1)))
Oracle plan: SELECT STATEMENT
Oracle plan: HASH JOIN (condition "R1"."EMPRESA_ID"="R2"."EMPRESAID")
Oracle plan: NESTED LOOPS
Oracle plan: NESTED LOOPS
Oracle plan: STATISTICS COLLECTOR
Oracle plan: TABLE ACCESS FULL LGTW_EMPRESA
Oracle plan: INDEX RANGE SCAN FKEDC565953B4BD13F_IDX (condition "R1"."EMPRESA_ID"="R2"."EMPRESAID")
Oracle plan: TABLE ACCESS BY INDEX ROWID LGTW_REQUESTC (filter "R1"."CREATED">TRUNC(CAST(CAST(:NOW AS TIMESTAMP WITH TIME ZONE) AS DATE))-1)
Oracle plan: TABLE ACCESS BY INDEX ROWID BATCHED LGTW_REQUESTC
Oracle plan: INDEX RANGE SCAN LGTW_REQUESTC_CREATED_IDX (condition "R1"."CREATED">TRUNC(CAST(CAST(:NOW AS TIMESTAMP WITH TIME ZONE) AS DATE))-1)
-> Sort (cost=99776.10..103778.60 rows=1000 width=8) (actual time=6.634..10.152 rows=8041 loops=1)
Output: ld2.dominioid
Sort Key: ld2.dominioid
Sort Method: quicksort Memory: 56kB
-> Foreign Scan on "LGTWEB".lgtw_dominio ld2 (cost=10000.00..20000.00 rows=1000 width=8) (actual time=2.717..6.227 rows=668 loops=1)
Output: ld2.dominioid
Oracle query: SELECT /*ca4f8585e2576f87cce58a66d1ab7b61*/ r4."DOMINIOID" ROM "LGTWEB"."LGTW_DOMINIO" r4
Oracle plan: SELECT STATEMENT
Oracle plan: INDEX FAST FULL SCAN SYS_C0011864
Planning Time: 335.447 ms
JIT:
Functions: 23
Options: Inlining true, Optimization true, Expressions true, Deforming true
Timing: Generation 3.830 ms, Inlining 63.227 ms, Optimization 235.350 ms, Emission 142.442 ms, Total 444.849 ms
Execution Time: 419359.702 msYou didn't ANALYZE the foreign tables.
Do that and try again. Next time you post an execution plan, please make sure you don't mangle the formatting and preserve all spaces as they are.
Also, note that oracle_fdw will push down at most a single join. When running complicated queries involving many foreign tables, that will lead to most of the joins being performed locally.
fernandorb10
commented
3 years ago is a dbeaver output:
Sort (cost=2068186.52..2068194.52 rows=1 width=1448) (actual time=401399.003..401399.246 rows=523 loops=1) Output: (count()), lr.generatedtype, ld.dominio_id, ld.showname, ld.description, lr.empresa_id, pn.contract_id, ld.lfc_priority, lr.status_id Sort Key: (count()) DESC Sort Method: quicksort Memory: 134kB -> HashAggregate (cost=2068166.50..2068170.51 rows=1 width=1448) (actual time=401398.194..401398.572 rows=523 loops=1) Output: count(), lr.generatedtype, ld.dominio_id, ld.showname, ld.description, lr.empresa_id, pn.contract_id, ld.lfc_priority, lr.status_id Group Key: ld.lfc_priority, lr.status_id, lr.generatedtype, ld.dominio_id, ld.showname, ld.description, lr.empresa_id, pn.contract_id -> Merge Join (cost=859224.00..1167604.00 rows=25000 width=1440) (actual time=401264.566..401388.307 rows=8741 loops=1) Output: lr.generatedtype, ld.dominio_id, ld.showname, ld.description, lr.empresa_id, pn.contract_id, ld.lfc_priority, lr.status_id Merge Cond: ((pn.guid)::text = (lr.guid)::text) -> Sort (cost=99776.10..103778.60 rows=1000 width=150) (actual time=341369.995..377723.201 rows=48161729 loops=1) Output: pn.contract_id, pn.guid Sort Key: pn.guid Sort Method: external merge Disk: 2220760kB -> Foreign Scan on "LOGALTYTECA".pt_nodes pn (cost=10000.00..20000.00 rows=1000 width=150) (actual time=64021.048..269936.793 rows=48161729 loops=1) Output: pn.contract_id, pn.guid Oracle query: SELECT /65ccf9ba136dfd0ce38d2408b453f0d2/ r6."GUID", r6."CONTRACT_ID" FROM "LOGALTYTECA"."PT_NODES" r6 Oracle plan: SELECT STATEMENT Oracle plan: VIEW index$_join$_001 Oracle plan: HASH JOIN (condition ROWID=ROWID) Oracle plan: INDEX FAST FULL SCAN NDX_PTNODES_USERID_GUID Oracle plan: INDEX FAST FULL SCAN NDX_PT_NODES_CONTRACT_ID -> Sort (cost=759447.89..779460.39 rows=5000 width=1826) (actual time=663.188..668.029 rows=8972 loops=1) Output: lr.generatedtype, lr.empresa_id, lr.status_id, lr.guid, ld.dominio_id, ld.showname, ld.description, ld.lfc_priority Sort Key: lr.guid Sort Method: quicksort Memory: 2717kB -> Merge Join (cost=199552.21..267632.21 rows=5000 width=1826) (actual time=629.871..652.533 rows=9013 loops=1) Output: lr.generatedtype, lr.empresa_id, lr.status_id, lr.guid, ld.dominio_id, ld.showname, ld.description, ld.lfc_priority Merge Cond: (ld.dominio_id = ld2.dominioid) -> Sort (cost=99776.10..103778.60 rows=1000 width=1826) (actual time=623.817..628.457 rows=9013 loops=1) Output: lr.generatedtype, lr.empresa_id, lr.status_id, lr.guid, ld.dominio_id, ld.showname, ld.description, ld.lfc_priority Sort Key: ld.dominio_id Sort Method: quicksort Memory: 2717kB -> Foreign Scan (cost=10000.00..20000.00 rows=1000 width=1826) (actual time=33.676..612.315 rows=9013 loops=1) Output: lr.generatedtype, lr.empresa_id, lr.status_id, lr.guid, ld.dominio_id, ld.showname, ld.description, ld.lfc_priority Oracle query: SELECT /11f533c319763a1538997fc3d9386b3a/ r1."GENERATEDTYPE", r1."EMPRESA_ID", r1."STATUS_ID", r1."GUID", r2."DOMINIO_ID", r2."SHOWNAME", r2."DESCRIPTION", r2."LFC_PRIORITY" FROM ("LGTWEB"."LGTW_REQUESTC" r1 INNER JOIN "LGTWEB"."LGTW_EMPRESA" r2 ON (r1."EMPRESA_ID" = r2."EMPRESAID") AND (r1."CREATED" > (TRUNC(CAST (CAST(:now AS TIMESTAMP WITH TIME ZONE) AS DATE)) - 1))) Oracle plan: SELECT STATEMENT Oracle plan: HASH JOIN (condition "R1"."EMPRESA_ID"="R2"."EMPRESAID") Oracle plan: NESTED LOOPS Oracle plan: NESTED LOOPS Oracle plan: STATISTICS COLLECTOR Oracle plan: TABLE ACCESS FULL LGTW_EMPRESA Oracle plan: INDEX RANGE SCAN FKEDC565953B4BD13F_IDX (condition "R1"."EMPRESA_ID"="R2"."EMPRESAID") Oracle plan: TABLE ACCESS BY INDEX ROWID LGTW_REQUESTC (filter "R1"."CREATED">TRUNC(CAST(CAST(:NOW AS TIMESTAMP WITH TIME ZONE) AS DATE))-1) Oracle plan: TABLE ACCESS BY INDEX ROWID BATCHED LGTW_REQUESTC Oracle plan: INDEX RANGE SCAN LGTW_REQUESTC_CREATED_IDX (condition "R1"."CREATED">TRUNC(CAST(CAST(:NOW AS TIMESTAMP WITH TIME ZONE) AS DATE))-1) -> Sort (cost=99776.10..103778.60 rows=1000 width=8) (actual time=5.993..10.154 rows=9532 loops=1) Output: ld2.dominioid Sort Key: ld2.dominioid Sort Method: quicksort Memory: 56kB -> Foreign Scan on "LGTWEB".lgtw_dominio ld2 (cost=10000.00..20000.00 rows=1000 width=8) (actual time=1.634..5.577 rows=668 loops=1) Output: ld2.dominioid Oracle query: SELECT /ca4f8585e2576f87cce58a66d1ab7b61*/ r4."DOMINIOID" FROM "LGTWEB"."LGTW_DOMINIO" r4 Oracle plan: SELECT STATEMENT Oracle plan: INDEX FAST FULL SCAN SYS_C0011864 Planning Time: 167.720 ms JIT: Functions: 23 Options: Inlining true, Optimization true, Expressions true, Deforming true Timing: Generation 3.369 ms, Inlining 65.152 ms, Optimization 269.476 ms, Emission 147.882 ms, Total 485.878 ms Execution Time: 401788.633 ms
laurenz
commented
3 years ago Then use a better tool. I am unwilling to wade through yet another mangled execution plan to get it in shape.
You still haven't ANALYZEd the foreign tables.
What exactly do you want me to do for you? If you want joins to be pushed down to Oracle, don't join more than two foreign tables.
In general, you cannot expect a complicated query involving foreign tables to perform as well as with local tables.
Perhaps you can define a view in Oracle that joins the tables and use that view as target for a foreign table.
Hello,
My problem is that the sentences that we execute from postgrsql connected through oracle_fdw, do not use indexes, so the cost of "foreing scan" is very high.
That could be happening?
The schema was imported through import foreing schema, tables are not created one by one.
My version of postgres is 11, oracle 12 and oracle fdw 2.3.0
Thank you very much for the help.