Oracle Database Discussions

I have set up DG4ODBC on a Windows 2003 32-bit 11g Oracle database to access a Windows 2008 64-bit 9.1 Postgres database, using the psqlodbc driver (32-bit).

I am able to issue queries, but they are very slow. The reason is that the where clause of the query is being removed somewhere along the line (I have seen the actual queries run by Postgres using pgAdmin server status screen), and all the rows in the table are retrieved from Postgres. Naturally with tables bigger than a few rows that means doing a full table scan in Postgres, as well as transferring lots of bytes through the network.

For instance, if I query from Oracle like this: select * from "dps_user"@pg where "id" = '2423', the sql run at Postgres is this: select * from dps_user, which retrieves all rows on the table! When I get the query result in Oracle, I only get the row with id 2423, so I assume that Oracle gets the whole table, and does the filtering at its end.

In a 32-bit Windows XP separate machine I have set up the same psqlodbc driver, and I have run the same query using Microsoft Query. In this case, the query run in Postgres contains the where clause, and therefore it is much faster because a unique index scan is performed in Postgres, and only one row is transferred back to Microsoft Query.

Could you help me, please?

Thanks a lot,

Carlos