Re: [QGIS-Developer] Postgis loading using COPY instead of INSERTS?

[Régis Haubourg]

Hi Nyall,
That's great!

from a user point of view, the first two main use cases that would benefit
of implementing that would be the DB Manager drag and drop loader and the
processing postgis loader algorithm.
Cheers
Régis

2017-06-15 6:00 GMT+02:00 Nyall Dawson :

> On 11 June 2017 at 08:04, Nyall Dawson  wrote:
> > On 11 June 2017 at 05:49, Matthias Kuhn  wrote:
> >
> >>
> >> Something else to discuss is what will be sent along with the
> >> `featureAdded(QgsFeatureId fid)` signal if the id is generated on the
> DB but
> >> not propagated back to the client.
> >>
> >
> > I don't think this is a big limitation - we just note it in the docs
> > for the fast insert flag that featureAdded won't be emitted. It's only
> > emitted when adding to a vector layer anyway, and most of the cases
> > where the fast insert flag will be used will be adding directly to a
> > data provider.
>
> Right, I've implemented the flag over in https://github.com/qgis/QGIS/
> pull/4734
>
> This opens the possibility of using a COPY statement in the postgres
> provider when FastInsert flag is set. I haven't done that, so
> presuming the PR is acceptable it'd be a good time for someone else to
> jump in and make this improvement. All required changes would be self
> contained in QgsPostgresProvider::addFeatures.
>
> This would be great to have for 3.0 - we've already made huge
> improvements in the speed of various related operations, and
> implementing COPY for FastInsert would then also have massive flow on
> benefits to processing algs which are saving their results direct to a
> postgres database.
>
> Nyall
>
___
QGIS-Developer mailing list
QGIS-Developer@lists.osgeo.org
List info: https://lists.osgeo.org/mailman/listinfo/qgis-developer
Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-developer

Re: [QGIS-Developer] Postgis loading using COPY instead of INSERTS?

[Nyall Dawson]

On 11 June 2017 at 08:04, Nyall Dawson  wrote:
> On 11 June 2017 at 05:49, Matthias Kuhn  wrote:
>
>>
>> Something else to discuss is what will be sent along with the
>> `featureAdded(QgsFeatureId fid)` signal if the id is generated on the DB but
>> not propagated back to the client.
>>
>
> I don't think this is a big limitation - we just note it in the docs
> for the fast insert flag that featureAdded won't be emitted. It's only
> emitted when adding to a vector layer anyway, and most of the cases
> where the fast insert flag will be used will be adding directly to a
> data provider.

Right, I've implemented the flag over in https://github.com/qgis/QGIS/pull/4734

This opens the possibility of using a COPY statement in the postgres
provider when FastInsert flag is set. I haven't done that, so
presuming the PR is acceptable it'd be a good time for someone else to
jump in and make this improvement. All required changes would be self
contained in QgsPostgresProvider::addFeatures.

This would be great to have for 3.0 - we've already made huge
improvements in the speed of various related operations, and
implementing COPY for FastInsert would then also have massive flow on
benefits to processing algs which are saving their results direct to a
postgres database.

Nyall
___
QGIS-Developer mailing list
QGIS-Developer@lists.osgeo.org
List info: https://lists.osgeo.org/mailman/listinfo/qgis-developer
Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-developer

Re: [QGIS-Developer] Postgis loading using COPY instead of INSERTS?

[Nyall Dawson]

On 11 June 2017 at 05:49, Matthias Kuhn  wrote:

>
> Something else to discuss is what will be sent along with the
> `featureAdded(QgsFeatureId fid)` signal if the id is generated on the DB but
> not propagated back to the client.
>

I don't think this is a big limitation - we just note it in the docs
for the fast insert flag that featureAdded won't be emitted. It's only
emitted when adding to a vector layer anyway, and most of the cases
where the fast insert flag will be used will be adding directly to a
data provider.

Nyall
___
QGIS-Developer mailing list
QGIS-Developer@lists.osgeo.org
List info: https://lists.osgeo.org/mailman/listinfo/qgis-developer
Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-developer

Re: [QGIS-Developer] Postgis loading using COPY instead of INSERTS?

[Matthias Kuhn]

On 6/10/17 8:55 PM, Régis Haubourg wrote:

>
>
> 2017-06-10 14:17 GMT+02:00 Matthias Kuhn  >:
>
> > I'd estimate 90+% of the time we are bulk adding features we don't
> > care about the updated feature ids, and it's only when adding small
> > amounts of features that this updated id is used. Definitely for
> > things like QgsVectorLayerExporter it's not required, so we'd
> get the
> > COPY speed boost used.
> >
> > Thoughts?
>
>
> Thanks for that! 
>  In edit mode, except for copy-paste , I don't really see where we
> could benefit from COPY instead of atomic INSERTS. What are the other
> use cases for batch insert deserving a COPY boost?
>
>
> This would also help fixing https://issues.qgis.org/issues/16552
> 
> Sounds like a good idea to me
>
>
> It seems fixed by Jurgen in fact :)
Views created with the DB manager work now, but the related issue
https://issues.qgis.org/issues/16083 still persists.


Something else to discuss is what will be sent along with the
`featureAdded(QgsFeatureId fid)` signal if the id is generated on the DB
but not propagated back to the client.

Matthias
>
> Régis 
>
> Matthias
> > Nyall
> > ___
> > QGIS-Developer mailing list
> > qgis-develo...@lists.osgeo.or
> 
>
>  
>
> g 
> > List info:
> https://lists.osgeo.org/mailman/listinfo/qgis-developer
> 
> > Unsubscribe:
> https://lists.osgeo.org/mailman/listinfo/qgis-developer
> 
> >
>
> ___
> QGIS-Developer mailing list
> QGIS-Developer@lists.osgeo.org 
> List info: https://lists.osgeo.org/mailman/listinfo/qgis-developer
> 
> Unsubscribe:
> https://lists.osgeo.org/mailman/listinfo/qgis-developer
> 
>
>

___
QGIS-Developer mailing list
QGIS-Developer@lists.osgeo.org
List info: https://lists.osgeo.org/mailman/listinfo/qgis-developer
Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-developer

Re: [QGIS-Developer] Postgis loading using COPY instead of INSERTS?

[Régis Haubourg]

2017-06-10 14:17 GMT+02:00 Matthias Kuhn :

> > I'd estimate 90+% of the time we are bulk adding features we don't
> > care about the updated feature ids, and it's only when adding small
> > amounts of features that this updated id is used. Definitely for
> > things like QgsVectorLayerExporter it's not required, so we'd get the
> > COPY speed boost used.
> >
> > Thoughts?
>

Thanks for that!
 In edit mode, except for copy-paste , I don't really see where we could
benefit from COPY instead of atomic INSERTS. What are the other use cases
for batch insert deserving a COPY boost?


> This would also help fixing https://issues.qgis.org/issues/16552
> Sounds like a good idea to me
>
>
It seems fixed by Jurgen in fact :)

Régis

> Matthias
> > Nyall
> > ___
> > QGIS-Developer mailing list
> > qgis-develo...@lists.osgeo.or 
>


> g 
> > List info: https://lists.osgeo.org/mailman/listinfo/qgis-developer
> > Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-developer
> >
>
> ___
> QGIS-Developer mailing list
> QGIS-Developer@lists.osgeo.org
> List info: https://lists.osgeo.org/mailman/listinfo/qgis-developer
> Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-developer
>
___
QGIS-Developer mailing list
QGIS-Developer@lists.osgeo.org
List info: https://lists.osgeo.org/mailman/listinfo/qgis-developer
Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-developer

Re: [QGIS-Developer] Postgis loading using COPY instead of INSERTS?

[Matthias Kuhn]

On 6/10/17 2:11 PM, Nyall Dawson wrote:

> On 19 May 2017 at 17:04, Régis Haubourg  wrote:
>> Forwarding Nyall tips, might be usefull for others
>>
>>> I tested drag and drop in browser and dbmanager (in 2.18.7 and master) and
>>> couldn't find anything about that in the issues or the code base.
>> Both of these codepaths use QgsVectorLayerExporter, which batches
>> calls to QgsVectorDataProvider::addFeatures in groups of 200 features.
>> So there's definitely the potential for a good speed bump here by
>> moving to the COPY command.
>>
>>> Did I miss something? Is there someone working in that area?
>> I don't think so. The tricky bit would be handling the "RETURNING"
>> part of the INSERT command, which is used to update the add features
>> with their new feature IDs/primary keys. I'm not sure that's possible
>> using a COPY command.
> I've been thinking here...
>
> I think it may be worthwhile to add a "flags" argument to the vector
> data provider addFeature(s) methods (i.e. add it to the base class
> methods in QgsFeatureSink). Initially we could have a single available
> flag - something like "SkipFidUpdates". If set, providers could then
> choose to optimise the way they add features to the backend store
> given that the updated feature ids aren't required. We could then
> safely use COPY in the postgres provider when this flag is set.
>
> I'd estimate 90+% of the time we are bulk adding features we don't
> care about the updated feature ids, and it's only when adding small
> amounts of features that this updated id is used. Definitely for
> things like QgsVectorLayerExporter it's not required, so we'd get the
> COPY speed boost used.
>
> Thoughts?

This would also help fixing https://issues.qgis.org/issues/16552
Sounds like a good idea to me

Matthias
> Nyall
> ___
> QGIS-Developer mailing list
> QGIS-Developer@lists.osgeo.org
> List info: https://lists.osgeo.org/mailman/listinfo/qgis-developer
> Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-developer
>

___
QGIS-Developer mailing list
QGIS-Developer@lists.osgeo.org
List info: https://lists.osgeo.org/mailman/listinfo/qgis-developer
Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-developer

Re: [QGIS-Developer] Postgis loading using COPY instead of INSERTS?

[Nyall Dawson]

On 19 May 2017 at 17:04, Régis Haubourg  wrote:
> Forwarding Nyall tips, might be usefull for others
>
>> I tested drag and drop in browser and dbmanager (in 2.18.7 and master) and
>> couldn't find anything about that in the issues or the code base.
>
> Both of these codepaths use QgsVectorLayerExporter, which batches
> calls to QgsVectorDataProvider::addFeatures in groups of 200 features.
> So there's definitely the potential for a good speed bump here by
> moving to the COPY command.
>
>> Did I miss something? Is there someone working in that area?
>
> I don't think so. The tricky bit would be handling the "RETURNING"
> part of the INSERT command, which is used to update the add features
> with their new feature IDs/primary keys. I'm not sure that's possible
> using a COPY command.

I've been thinking here...

I think it may be worthwhile to add a "flags" argument to the vector
data provider addFeature(s) methods (i.e. add it to the base class
methods in QgsFeatureSink). Initially we could have a single available
flag - something like "SkipFidUpdates". If set, providers could then
choose to optimise the way they add features to the backend store
given that the updated feature ids aren't required. We could then
safely use COPY in the postgres provider when this flag is set.

I'd estimate 90+% of the time we are bulk adding features we don't
care about the updated feature ids, and it's only when adding small
amounts of features that this updated id is used. Definitely for
things like QgsVectorLayerExporter it's not required, so we'd get the
COPY speed boost used.

Thoughts?
Nyall
___
QGIS-Developer mailing list
QGIS-Developer@lists.osgeo.org
List info: https://lists.osgeo.org/mailman/listinfo/qgis-developer
Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-developer

Re: [QGIS-Developer] Postgis loading using COPY instead of INSERTS?

[Régis Haubourg]

Forwarding Nyall tips, might be usefull for others

> I tested drag and drop in browser and dbmanager (in 2.18.7 and master) and
> couldn't find anything about that in the issues or the code base.

Both of these codepaths use QgsVectorLayerExporter, which batches
calls to QgsVectorDataProvider::addFeatures in groups of 200 features.
So there's definitely the potential for a good speed bump here by
moving to the COPY command.

> Did I miss something? Is there someone working in that area?

I don't think so. The tricky bit would be handling the "RETURNING"
part of the INSERT command, which is used to update the add features
with their new feature IDs/primary keys. I'm not sure that's possible
using a COPY command.




--
View this message in context: 
http://osgeo-org.1560.x6.nabble.com/QGIS-Developer-Postgis-loading-using-COPY-instead-of-INSERTS-tp5320528p5321038.html
Sent from the QGIS - Developer mailing list archive at Nabble.com.
___
QGIS-Developer mailing list
QGIS-Developer@lists.osgeo.org
List info: https://lists.osgeo.org/mailman/listinfo/qgis-developer
Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-developer