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
<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]
<mailto:[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] <mailto:[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]
<mailto:[email protected]>
https://lists.osgeo.org/mailman/listinfo/postgis-users
<https://lists.osgeo.org/mailman/listinfo/postgis-users>
_______________________________________________
postgis-users mailing list
[email protected] <mailto:[email protected]>
https://lists.osgeo.org/mailman/listinfo/postgis-users
<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
