Slow Query Performance Using ogr_fdw on Windows 2012 R2 with PG 9.5

From: Jack Ort <jack(dot)ort(at)gmail(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: Slow Query Performance Using ogr_fdw on Windows 2012 R2 with PG 9.5
Date: 2016-02-18 00:23:56
Message-ID: CAKiJYzj1YcvgWr_k=9qMoUiS2OmDnrkmDoB1cMr77QiBNDXtYg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

Installed postgres 9.5.1 using EnterpriseDB binary on a Windows 2012 R2
server.

Downloaded org_fdw for x64 from PostGIS.net (
http://winnie.postgis.net/download/windows/pg95/buildbot/extras/ogrfdw-pg95-binaries-1.0.1w64gcc48.zip)
and copied files to 9.5 share\extension, \bin and \lib

Using DependencyWalker, kept adding to my PATH variable for missing DLLs
until my CREATE EXTENSION command succeeded:
------
create extension ogr_fdw schema my_extensions;

CREATE SERVER xyz
FOREIGN DATA WRAPPER ogr_fdw
OPTIONS (
datasource 'ODBC:xxxxx/yyyyy(at)MedidataVPN',
format 'ODBC' );

create schema if not exists xyz;

import foreign schema ogr_all
from server xyz into xyz;

All of this worked, although the CREATE SERVER command took about 5 minutes.
----------------------------------------------------------------------------------------------
Using a VPN connection to a remote SQLServer database.

Defined an 64-bit ODBC System DSN using the SQL Server driver (driver
version 6.03.9600.16384)

Chose a simple 91-record table as my query target. SSMS shows the design
as:

/****** Object: Table [dbo].[ObjectTypeR] Script Date: 2/17/2016
5:30:44 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[ObjectTypeR](
[ObjectTypeID] [tinyint] NOT NULL,
[ObjectName] [varchar](100) NOT NULL,
CONSTRAINT [PK_ObjectTypeR] PRIMARY KEY CLUSTERED
(
[ObjectTypeID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = ON, IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO
/***************************************/

This query run on the Windows server that hosts postgres under Powershell
using ODBC gives an instant response:

SELECT * FROM [dbo].[objecttyper]

------------------
postgres' information_schema.columns shows this for the foreign table (I
wonder if that huge character_octet_length is a problem?) - OGR_FDW
apparently adds the fid and geom columns:

table_name column_name ordinal_position column_default is_nullable data_type
character_maximum_length character_octet_length
dbo_objecttyper fid 1 YES integer
dbo_objecttyper geom 2 YES bytea
dbo_objecttyper objecttypeid 3 YES character varying 1073741824
dbo_objecttyper objectname 4 YES character varying 1073741824

---------------------------
Running the equivalent query under pgAdmin III using the foreign server
defined above consistently takes > 10 minutes(!):

EXPLAIN ANALYZE select * from xyz.dbo_objecttyper;

"Foreign Scan on dbo_objecttyper (cost=25.00..1025.00 rows=1000 width=100)
(actual time=48.275..49.171 rows=91 loops=1)"
"Planning time: 316745.992 ms"
"Execution time: 316735.626 ms"
----------------------------

Thank you in advance for any suggestions to improve my response times! If
another FDW would be better and is available for 64-bit Windows, I would
certainly try it.

Best Regards,
-Jack Ort

Browse pgsql-novice by date

  From Date Subject
Next Message Killian Driscoll 2016-02-18 20:35:30 Create view that retrieves both table and column comments
Previous Message Shmagi Kavtaradze 2016-02-16 16:39:34 Optimizing the Query