search

laurenz / oracle_fdw

PostgreSQL Foreign Data Wrapper for Oracle
http://laurenz.github.io/oracle_fdw/
Other
492 stars 157 forks source link

Query to Oracle when there are geometry conditions implies that all table are loaded in PostgreSQL. #662

Closed orencio closed 6 months ago

orencio commented 6 months ago

Hi, I have PostgreSQL 9.6 with these extensions:

postgres=# select * from pg_available_extensions where name like 'postgis%' or name like 'oracle%';
            name            | default_version | installed_version |                               comment                               
----------------------------+-----------------+-------------------+---------------------------------------------------------------------
 postgis-2.5                | 2.5.3           |                   | PostGIS geometry, geography, and raster spatial types and functions
 postgis                    | 2.5.3           |                   | PostGIS geometry, geography, and raster spatial types and functions
 postgis_topology-2.5       | 2.5.3           |                   | PostGIS topology spatial types and functions
 postgis_tiger_geocoder     | 2.5.3           |                   | PostGIS tiger geocoder and reverse geocoder
 postgis_sfcgal-2.5         | 2.5.3           |                   | PostGIS SFCGAL functions
 postgis_tiger_geocoder-2.5 | 2.5.3           |                   | PostGIS tiger geocoder and reverse geocoder
 oracle_fdw                 | 1.1             | 1.1               | foreign data wrapper for Oracle access
 postgis_topology           | 2.5.3           |                   | PostGIS topology spatial types and functions
 postgis_sfcgal             | 2.5.3           |                   | PostGIS SFCGAL functions
(9 filas)

I have an Oracle table with geometry column (geometria):

> desc pg_acciones_con_geometria
 Nombre                    ?Nulo?   Tipo
 ----------------------------------------- -------- ----------------------------
 ENTIDAD                        NUMBER(19)
 ACCION_ID                 NOT NULL NUMBER(19)
 ID                    NOT NULL NUMBER(19)
 ACCION_FECHAALTA                   TIMESTAMP(9)
 ACCION_NOMBRE                      VARCHAR2(50 CHAR)
 ACCION                         VARCHAR2(10)
 CABECERA_ID                        NUMBER(19)
 CABECERA_NUMERO                    NUMBER
 CABECERA_TIPO                      VARCHAR2(500 CHAR)
 TIPO_INTERVENCION_CODIGO               VARCHAR2(50 CHAR)
 TIPO_INTERVENCION_NOMBRE               VARCHAR2(100 CHAR)
 GRUPO_INTERVENCION_CODIGO              VARCHAR2(50 CHAR)
 GRUPO_INTERVENCION_NOMBRE              VARCHAR2(50 CHAR)
 SUBGRUPO_INTERVENCION_CODIGO               VARCHAR2(50 CHAR)
 SUBGRUPO_INTERVENCION_NOMBRE               VARCHAR2(50 CHAR)
 LUGAR                          VARCHAR2(572 CHAR)
 GEOMETRIA                      MDSYS.SDO_GEOMETRY

> select * from user_indexes where table_name = 'PG_ACCIONES_CON_GEOMETRIA'

INDEX_NAME                         |INDEX_TYPE|TABLE_OWNER  |TABLE_NAME               |TABLE_TYPE|UNIQUENESS|COMPRESSION|PREFIX_LENGTH|TABLESPACE_NAME|INI_TRANS|MAX_TRANS|INITIAL_EXTENT|NEXT_EXTENT|MIN_EXTENTS|MAX_EXTENTS|PCT_INCREASE|PCT_THRESHOLD|INCLUDE_COLUMN|FREELISTS|FREELIST_GROUPS|PCT_FREE|LOGGING|BLEVEL|LEAF_BLOCKS|DISTINCT_KEYS|AVG_LEAF_BLOCKS_PER_KEY|AVG_DATA_BLOCKS_PER_KEY|CLUSTERING_FACTOR|STATUS|NUM_ROWS|SAMPLE_SIZE|LAST_ANALYZED      |DEGREE|INSTANCES|PARTITIONED|TEMPORARY|GENERATED|SECONDARY|BUFFER_POOL|FLASH_CACHE|CELL_FLASH_CACHE|USER_STATS|DURATION|PCT_DIRECT_ACCESS|ITYP_OWNER|ITYP_NAME       |PARAMETERS|GLOBAL_STATS|DOMIDX_STATUS|DOMIDX_OPSTATUS|FUNCIDX_STATUS|JOIN_INDEX|IOT_REDUNDANT_PKEY_ELIM|DROPPED|VISIBILITY|DOMIDX_MANAGEMENT|SEGMENT_CREATED|ORPHANED_ENTRIES|INDEXING|
-----------------------------------+----------+-------------+-------------------------+----------+----------+-----------+-------------+---------------+---------+---------+--------------+-----------+-----------+-----------+------------+-------------+--------------+---------+---------------+--------+-------+------+-----------+-------------+-----------------------+-----------------------+-----------------+------+--------+-----------+-------------------+------+---------+-----------+---------+---------+---------+-----------+-----------+----------------+----------+--------+-----------------+----------+----------------+----------+------------+-------------+---------------+--------------+----------+-----------------------+-------+----------+-----------------+---------------+----------------+--------+
PG_ACCIONES_CON_GEOMETRIA_PK       |NORMAL    |P            |PG_ACCIONES_CON_GEOMETRIA|TABLE     |UNIQUE    |DISABLED   |             | USERS         |        2|      255|         65536|    1048576|          1| 2147483645|            |             |              |         |               |      10|YES    |     1|        119|        50276|                      1|                      1|            18919|VALID |   50276|      50276|01-04-2024 12:24:52|1     |1        |NO         |N        |N        |N        |DEFAULT    |DEFAULT    |DEFAULT         |NO        |        |                 |          |                |          |YES         |             |               |              |NO        |NO                     |NO     |VISIBLE   |                 |YES            |NO              |FULL    |
PG_ACCIONES_CON_GEOMETRIA_GEOMETRIA|DOMAIN    |P            |PG_ACCIONES_CON_GEOMETRIA|TABLE     |NONUNIQUE |DISABLED   |             |               |        0|        0|              |           |           |           |            |             |              |         |               |       0|YES    |      |           |             |                       |                       |                 |VALID |        |           |                   |1     |1        |NO         |N        |N        |N        |DEFAULT    |DEFAULT    |DEFAULT         |NO        |        |                 |MDSYS     |SPATIAL_INDEX_V2|          |NO          |VALID        |VALID          |              |NO        |NO                     |NO     |VISIBLE   |SYSTEM_MANAGED   |YES            |NO              |FULL    |

2 row(s) fetched.

I've imported this table in PostgreSQL world as fdw_oracle.pg_acciones_con_geometria.

When I query using a normal comparator with no-geometry data, query is sent to Oracle with conditons and Oracle indexes are correctly used:

> explain verbose
select
  "entidad",
  "accion_id",
  "id",
  "accion_fechaalta",
  "accion_nombre",
  "accion",
  "cabecera_id",
  "cabecera_numero",
  "cabecera_tipo",
  "tipo_intervencion_codigo",
  "tipo_intervencion_nombre",
  "grupo_intervencion_codigo",
  "grupo_intervencion_nombre",
  "subgrupo_intervencion_codigo",
  "subgrupo_intervencion_nombre",
  "lugar",
  encode(ST_AsBinary(case
    when ST_HasArc("geometria") then "geometria"
    else ST_Simplify(ST_Force2D("geometria"),
    1.6423799774543114E-4,
    true)
  end),
  'base64') as "geometria"
from
  fdw_oracle.pg_acciones_con_geometria
where
  id in (3191352145, 3192305026)
;

Foreign Scan on fdw_oracle.pg_acciones_con_geometria  (cost=10000.00..20035.00 rows=1000 width=2148)
  Output: entidad, accion_id, id, accion_fechaalta, accion_nombre, accion, cabecera_id, cabecera_numero, cabecera_tipo, tipo_intervencion_codigo, tipo_intervencion_nombre, grupo_intervencion_codigo, grupo_intervencion_nombre, subgrupo_intervencion_codigo, subgrupo_intervencion_nombre, lugar, encode(st_asbinary(CASE WHEN st_hasarc(geometria) THEN geometria ELSE st_simplify(st_force2d(geometria), '0.000164237997745431136'::double precision, true) END), 'base64'::text)
  Oracle query: SELECT /*aea97e4db6158dc92624bf94cbe32e7d*/ r1."ENTIDAD", r1."ACCION_ID", r1."ID", r1."ACCION_FECHAALTA", r1."ACCION_NOMBRE", r1."ACCION", r1."CABECERA_ID", r1."CABECERA_NUMERO", r1."CABECERA_TIPO", r1."TIPO_INTERVENCION_CODIGO", r1."TIPO_INTERVENCION_NOMBRE", r1."GRUPO_INTERVENCION_CODIGO", r1."GRUPO_INTERVENCION_NOMBRE", r1."SUBGRUPO_INTERVENCION_CODIGO", r1."SUBGRUPO_INTERVENCION_NOMBRE", r1."LUGAR", r1."GEOMETRIA" FROM "P"."PG_ACCIONES_CON_GEOMETRIA" r1 WHERE (r1."ID" IN (3191352145, 3192305026))
  Oracle plan: SELECT STATEMENT
  Oracle plan:   INLIST ITERATOR
  Oracle plan:     TABLE ACCESS BY INDEX ROWID PG_ACCIONES_CON_GEOMETRIA
  Oracle plan:       INDEX UNIQUE SCAN PG_ACCIONES_CON_GEOMETRIA_PK (condition ("R1"."ID"=3191352145 OR "R1"."ID"=3192305026))

But when Geoserver queries:

> explain verbose
select
  "entidad",
  "accion_id",
  "id",
  "accion_fechaalta",
  "accion_nombre",
  "accion",
  "cabecera_id",
  "cabecera_numero",
  "cabecera_tipo",
  "tipo_intervencion_codigo",
  "tipo_intervencion_nombre",
  "grupo_intervencion_codigo",
  "grupo_intervencion_nombre",
  "subgrupo_intervencion_codigo",
  "subgrupo_intervencion_nombre",
  "lugar",
  encode(ST_AsBinary(case
    when ST_HasArc("geometria") then "geometria"
    else ST_Simplify(ST_Force2D("geometria"),
    1.6423799774543114E-4,
    true)
  end),
  'base64') as "geometria"
from
  fdw_oracle.pg_acciones_con_geometria
where
  "geometria" && ST_GeomFromText(
    'POLYGON ((-1.865524190345588 37.462684547243825, -1.865524190345588 37.46555876415259, -1.8619183686968908 37.46555876415259, -1.8619183686968908 37.462684547243825, -1.865524190345588 37.462684547243825))',
    4326
  )
;
Foreign Scan on fdw_oracle.pg_acciones_con_geometria  (cost=10000.00..20035.00 rows=1000 width=2148)
  Output: entidad, accion_id, id, accion_fechaalta, accion_nombre, accion, cabecera_id, cabecera_numero, cabecera_tipo, tipo_intervencion_codigo, tipo_intervencion_nombre, grupo_intervencion_codigo, grupo_intervencion_nombre, subgrupo_intervencion_codigo, subgrupo_intervencion_nombre, lugar, encode(st_asbinary(CASE WHEN st_hasarc(geometria) THEN geometria ELSE st_simplify(st_force2d(geometria), '0.000164237997745431136'::double precision, true) END), 'base64'::text)
  Filter: (pg_acciones_con_geometria.geometria && '0103000020E610000001000000050000001CE6B6E42FD9FDBF6E634B3F39BB42401CE6B6E42FD9FDBF6633F96D97BB42403FFF55EA6ACAFDBF6633F96D97BB42403FFF55EA6ACAFDBF6E634B3F39BB42401CE6B6E42FD9FDBF6E634B3F39BB4240'::geometry)
  Oracle query: SELECT /*4e8c418d2d6215893138d68c2fdb97d2*/ r1."ENTIDAD", r1."ACCION_ID", r1."ID", r1."ACCION_FECHAALTA", r1."ACCION_NOMBRE", r1."ACCION", r1."CABECERA_ID", r1."CABECERA_NUMERO", r1."CABECERA_TIPO", r1."TIPO_INTERVENCION_CODIGO", r1."TIPO_INTERVENCION_NOMBRE", r1."GRUPO_INTERVENCION_CODIGO", r1."GRUPO_INTERVENCION_NOMBRE", r1."SUBGRUPO_INTERVENCION_CODIGO", r1."SUBGRUPO_INTERVENCION_NOMBRE", r1."LUGAR", r1."GEOMETRIA" FROM "P"."PG_ACCIONES_CON_GEOMETRIA" r1
  Oracle plan: SELECT STATEMENT
  Oracle plan:   TABLE ACCESS FULL PG_ACCIONES_CON_GEOMETRIA

all table is load to PosgreSQL and the conditions is resolved there.

This operation (&&) should be translated to Oracle to something like:

> explain plan for 
select *
from pg_acciones_con_geometria a
where
  SDO_ANYINTERACT(
    a.geometria
    , sdo_geometry(
      'POLYGON ((-1.865524190345588 37.462684547243825, -1.865524190345588 37.46555876415259, -1.8619183686968908 37.46555876415259, -1.8619183686968908 37.462684547243825, -1.865524190345588 37.462684547243825))'
      , 4326
    )
  ) = 'TRUE'
;

0 row(s) modified.

> select * from table(dbms_xplan.display)

PLAN_TABLE_OUTPUT                                                                                                      |
-----------------------------------------------------------------------------------------------------------------------+
Plan hash value: 517807451                                                                                             |
                                                                                                                       |
-----------------------------------------------------------------------------------------------------------------------|
| Id  | Operation                       | Name                                | Rows  | Bytes | Cost (%CPU)| Time     ||
-----------------------------------------------------------------------------------------------------------------------|
|   0 | SELECT STATEMENT                |                                     |    50 |  9300 |    15   (0)| 00:00:01 ||
|   1 |  TABLE ACCESS BY INDEX ROWID    | PG_ACCIONES_CON_GEOMETRIA           |    50 |  9300 |    15   (0)| 00:00:01 ||
|*  2 |   DOMAIN INDEX (SEL: 0.100000 %)| PG_ACCIONES_CON_GEOMETRIA_GEOMETRIA |       |       |     3   (0)| 00:00:01 ||
-----------------------------------------------------------------------------------------------------------------------|
                                                                                                                       |
Predicate Information (identified by operation id):                                                                    |
---------------------------------------------------                                                                    |
                                                                                                                       |
   2 - access("MDSYS"."SDO_ANYINTERACT"("A"."GEOMETRIA","SDO_GEOMETRY"."SDO_GEOMETRY"('POLYGON                         |
              ((-1.865524190345588 37.462684547243825, -1.865524190345588 37.46555876415259, -1.8619183686968908       |
              37.46555876415259, -1.8619183686968908 37.462684547243825, -1.865524190345588                            |
              37.462684547243825))',4326))='TRUE')                                                                     |
                                                                                                                       |
Note                                                                                                                   |
-----                                                                                                                  |
   - automatic DOP: Computed Degree of Parallelism is 1 because of parallel threshold                                  |

21 row(s) fetched.

that correctly uses the spatial index.

Is it possible?

Regards.

laurenz commented 6 months ago

oracle_fdw doesn't attempt to push down geometrical conditions, so that is expected. I have no plans to add such a feature.

This web page tells me that Geoserver can connect to Oracle directly. That would be the natural thing to do if you want efficient queries on an Oracle database.

laurenz commented 6 months ago

Is there anything else I can do for you?

orencio commented 6 months ago

Hello, I just wanted to point out this behaviour in case a solution could be found. Thank you very much for your reply.

Must I close this issue?