Hi, Paul Ramsay, I tried TEMP tables and found that it is useful.
I had to write LOOPs in PL/PgSQL since I had to intersect each geometry in a table with each geometry in another table. The logic needs to be iterated. If that is wrong, how can I write different SQL to do it? Do you have an example to enlighten me? About using Python, are you suggesting that it is not a good idea? Regards, Shao On Mon, 24 Aug 2020 at 18:35, Paul Ramsey <[email protected]> wrote: > > > On Sat, Aug 22, 2020 at 5:16 PM Simon Greener <[email protected]> > wrote: > > > > Python, unless I am wrong, is a mid/client tier language only; plpgsql > can only be deployed in the data tier. > > Python can be used in the client side or on the server side via PL/Python. > > However: > > The server-side Python binding (create extension plpythonu) is "untrusted" > which means it is frequently not available (for example, on AWS RDS) > because it represents a server-side security risk. That means that > developing large chunks of functionality in PL/Python risks making your > application limited in terms of the deployment environments you can use. > > The downside of doing Python processing on the *client* side is that all > the data you process has to traverse from the database to the client, and > back, and that is frequently a non-trivial overhead. > > All this speaks to doing processing in PL/PgSQL (which is universally > available, and runs local to the database) when it makes sense for you. > > With respect to optimization, it makes sense to use full SQL statements > often. If you find yourself writing LOOPs in PL/PgSQL it's possible you've > done something wrong, logically. If you let your processing be defined in > SQL you increase the odds of the planner finding optimizations that you > might miss if you're trying to be smart and control execution. > > That said, some multi-stage processes benefit from writing temporary > results into TEMP tables and building indexes on them, as you go from stage > to stage. YMMV, depends on the problem. Again though, avoiding pushing the > data back and forth over the wire is a good thing. > > ATB, > P > > > _______________________________________________ > postgis-users mailing list > [email protected] > https://lists.osgeo.org/mailman/listinfo/postgis-users
_______________________________________________ postgis-users mailing list [email protected] https://lists.osgeo.org/mailman/listinfo/postgis-users
