Bug report #18976: DB Manager Import Layer / File Extremely Slow - QGIS Application - QGIS Issue Tracking

Bug report #18976

DB Manager Import Layer / File Extremely Slow

Added by Sean Wohltman over 6 years ago. Updated over 6 years ago.

Status:	Closed
Priority:	High
Assignee:	Even Rouault
Category:	DB Manager
Affected QGIS version:	3.0.2	Regression?:	No
Operating System:	OSX	Easy fix?:	No
Pull Request or Patch supplied:	No	Resolution:
Crashes QGIS or corrupts data:	Yes	Copied to github as #:	26807

Description

When using DB Manager to load a shapefile ( http://njgin.state.nj.us/download2/parcels/parcels_shp_Monmouth.zip ) to a PostGIS Database on OSX, QGIS becomes unresponsive and it takes hours to load the data.

It does eventually finish, but this should not take hours.

The same data loads in a around a minute using ogr2ogr directly:

/Library/Frameworks/GDAL.framework/Versions/2.1/Programs/ogr2ogr -overwrite -lco "SCHEMA=public" -f "PostgreSQL" PG:"dbname='gis' host='localhost' port='5432' user='postgres' password='xxxxxxx'" -nlt PROMOTE_TO_MULTI -nln monmouthcountyparcels3 MonmouthCountyParcels2.shp

This occurs on:

QGIS version
3.0.2-Girona
QGIS code branch
Release 3.0
Compiled against Qt
5.9.3
Running against Qt
5.9.3
Compiled against GDAL/OGR
2.2.4
Running against GDAL/OGR
2.2.4
Compiled against GEOS
3.6.2-CAPI-1.10.2
Running against GEOS
3.6.2-CAPI-1.10.2 4d2925d6
PostgreSQL Client Version
9.6.6
SpatiaLite Version
4.4.0-RC0
QWT Version
6.1.3
QScintilla2 Version
2.10.3
PROJ.4 Version
493

AND:

QGIS version
2.18.15
QGIS code branch
Release 2.18
Compiled against Qt
4.8.6
Running against Qt
4.8.6
Compiled against GDAL/OGR
2.1.3
Running against GDAL/OGR
2.1.3
Compiled against GEOS
3.6.2-CAPI-1.10.2
Running against GEOS
3.6.1-CAPI-1.10.1 r0
PostgreSQL Client Version
9.3.4
SpatiaLite Version
4.3.0a
QWT Version
6.1.3
PROJ.4 Version
492
QScintilla2 Version
2.9.3

Associated revisions

Revision cc25727a
Added by Even Rouault over 6 years ago

[Postgres provider] Speed up feature insertion when pkid column is not set (fixes #18976)

When inserting features whose attribute column corresponding to the pkid
is empty, and tat this pkid column has a default value using a sequence,
then we can remove it from the INSERT statement completely, which save us
from doing a SELECT nextval(...) for each row. On the provided test case
of that ticket, on a debug build, this cuts down the insertion time from 5 minutes
to 1 minute 10s.

Revision 7e2ca8f6
Added by Even Rouault over 6 years ago

Merge pull request #7144 from rouault/fix_18976

[Postgres provider] Speed up feature insertion when pkid column is not set (fixes #18976)