Hi, Ruven Brooks, Good to hear from you about your insight into all these.
Some queries which I wrote for handling Big Data failed due to that these got killed or error messages said 'out of memory'. On attempting to write different queries, I found queries may keep running and appeared to take up days and weeks. I will be interested in how to write efficient queries. Regards, Shao On Fri, 31 Jul 2020 at 16:37, <[email protected]> wrote: > > Shao, > There are literally thousands of languages which can be used to > formulate queries and send them to the PostgreSQL server for processing. > You mentioned Python. How about Java, Javascript, Typescript, C, C++, C#, > Objective-C, Swift, Go, Matlab or R to name a randomly chosen few? > Interfaces like ODBC/JDBC make it really easy to add PostgreSQL support to > a programming language or calculation package. > > All of these languages are handy for writing things which are difficult to > express in SQL and all of them offer capabilities which may make it easier > to get data into and out of the application but all of them take about the > same amount of time to process a query because queries are processed by the > PostgreSQL server, not by the programming language. The only time > PL/<whatever> or one of these other languages helps with performance is > when the algorithm used has higher performance than what is available in > SQL. Often, the opposite turns out to be the case. In other words, > writing things in PL/<whatever> may actually slow things down. > > What should you do? Step 1 is to make sure that you are writing efficient > queries. You need to have a deep understanding of the results of the > EXPLAIN command and know how to re-arrange queries to control the > PostgreSQL processing. > > Suppose that no matter how you re-write the query, it's still slow. Step > 2 is to treat yourself to an online course on analysis of algorithms. Not > only will you learn ways of speeding up algorithms but you will also learn > to recognize when it's impossible to speed up an algorithm. > > Ruven Brooks > > On 7/31/2020 6:59 AM, Shaozhong SHI wrote: > > Hi, Laura, > > Thank you for your reply. > > 1. Please look at this documentation link. > https://www.postgresql.org/docs/9.1/external-pl.html > > There is also PL/sh language. > > So many varieties. I wonder the use cases for all these. > > 2. My immediate concern is to know which one is good for periodically > processing Big Data. > > Standard queries in PostgreSQL may fail or keep running for days, weeks > when data is very big. > > If you do a programme with standard queries of PostgreSQL, I would think > that it could run forever. > > 3. Does that mean Python can be treated as a native language in > PostgreSQL/PostGIS? Will it run faster? > > Regards, > > Shao > > On Fri, 31 Jul 2020 at 12:01, Augori <[email protected]> wrote: > >> Hi Shao, >> Using Python allows you to integrate a query into a workflow that has >> some batch component to it. Examples would be a query which allows you to >> select a set of records that need to be updated daily. Or it could be a >> query that needs to be run on hundreds of files to build a summary of the >> data or it could be part of a process that is triggered by some outside >> event, like a file being updated. I hope I haven't misinterpreted your >> question but you seem to be asking why you'd want to automate interactions >> with a database, a question to which there are thousands of responses. >> Please clarify if that is not the case. >> >> Kind regards, >> Laura >> >> On Fri, Jul 31, 2020, 5:05 AM Shaozhong SHI <[email protected]> >> wrote: >> >>> Hi, >>> >>> What is the advantage of querying in Python? >>> >>> Has anyone got much experience? >>> >>> What not just use standard query? >>> >>> What is the rationale for querying in Python? >>> >>> Regards, >>> >>> Shao >>> _______________________________________________ >>> 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 > > > _______________________________________________ > postgis-users mailing > [email protected]https://lists.osgeo.org/mailman/listinfo/postgis-users > > > _______________________________________________ > 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
