search

pramsey / pgsql-ogr-fdw

PostgreSQL foreign data wrapper for OGR
MIT License
237 stars 34 forks source link

oracle sql query is very slow? #229

Closed amwps290 closed 2 years ago

amwps290 commented 2 years ago

postgres:13.5 ogr_fdw: master

I create a foreign server as follow : 

CREATE SERVER ocidriver
    FOREIGN DATA WRAPPER ogr_fdw
    OPTIONS (
        datasource 'OCI:userna,e/password@(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.13.179)(PORT=1521))(CONNECT_DATA=(SID=orcl)))',
        format 'OCI');

import schema

create schema ocishcema;

import foreign schema ogr_all
    from server ocidriver into ocishcema;

when i execute select count(*) from ocishcema.tablenname ;

it's very slow.

the explain Analyse as follows: 

Aggregate  (cost=1027.50..1027.51 rows=1 width=8) (actual time=15047.668..15047.669 rows=1 loops=1)
  ->  Foreign Scan on smdtv_2 s  (cost=25.00..1025.00 rows=1000 width=0) (actual time=0.339..14988.025 rows=350991 loops=1)
Planning Time: 138.590 ms
Execution Time: 15089.064 ms
pramsey commented 2 years ago

There's no way for OGR to pushdown the Count(*) aggregate to your remote database, so the execution involves retrieving all the rows locally to Postgres and counting them there. I'm happy to see you got a result at all! In general OGR can only push down simple filters (=, <, >, LIKE) to the remote. If you want full pushdown to Oracle, use a purpose built FDW like the oracle_fdw.