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: | Whole Thread | Raw Message | 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
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 |