<div dir="ltr">Hi, Ruven Brooks,<div><br></div><div>Good to hear from you about your insight into all these.</div><div><br></div><div>Some queries which I wrote for handling Big Data failed due to that these got killed or error messages said 'out of memory'.</div><div><br></div><div>On attempting to write different queries, I found queries may keep running and appeared to take up days and weeks.</div><div><br></div><div>I will be interested in how to write efficient queries.</div><div><br></div><div>Regards,</div><div><br></div><div>Shao</div></div><br><div class="gmail_quote"><div dir="ltr" class="gmail_attr">On Fri, 31 Jul 2020 at 16:37, <<a href="mailto:ruvenml@beamerbrooks.com">ruvenml@beamerbrooks.com</a>> wrote:<br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">
<div>
<br>
<font face="Helvetica, Arial, sans-serif">Shao,<br>
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.<br>
<br>
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.<br>
<br>
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. <br>
<br>
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. <br>
<br>
Ruven Brooks </font><br>
<br>
<div>On 7/31/2020 6:59 AM, Shaozhong SHI
wrote:<br>
</div>
<blockquote type="cite">
<div dir="ltr">Hi, Laura,
<div><br>
</div>
<div>Thank you for your reply.</div>
<div><br>
</div>
<div>1. Please look at this documentation link.</div>
<div><a href="https://www.postgresql.org/docs/9.1/external-pl.html" target="_blank">https://www.postgresql.org/docs/9.1/external-pl.html</a> </div>
<div><br>
</div>
<div>There is also PL/sh language.</div>
<div><br>
</div>
<div>So many varieties. I wonder the use cases for all these.</div>
<div><br>
</div>
<div>2. My immediate concern is to know which one is good for
periodically processing Big Data.</div>
<div><br>
</div>
<div>Standard queries in PostgreSQL may fail or keep running for
days, weeks when data is very big.</div>
<div><br>
</div>
<div>If you do a programme with standard queries of PostgreSQL,
I would think that it could run forever.</div>
<div><br>
</div>
<div>3. Does that mean Python can be treated as a native
language in PostgreSQL/PostGIS? Will it run faster?</div>
<div><br>
</div>
<div>Regards,</div>
<div><br>
</div>
<div>Shao <br>
</div>
</div>
<br>
<div class="gmail_quote">
<div dir="ltr" class="gmail_attr">On Fri, 31 Jul 2020 at 12:01,
Augori <<a href="mailto:augori@gmail.com" target="_blank">augori@gmail.com</a>> wrote:<br>
</div>
<blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">
<div dir="auto">Hi Shao,
<div dir="auto">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.</div>
<div dir="auto"><br>
</div>
<div dir="auto">Kind regards,</div>
<div dir="auto">Laura</div>
</div>
<br>
<div class="gmail_quote">
<div dir="ltr" class="gmail_attr">On Fri, Jul 31, 2020, 5:05
AM Shaozhong SHI <<a href="mailto:shishaozhong@gmail.com" target="_blank">shishaozhong@gmail.com</a>>
wrote:<br>
</div>
<blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">
<div dir="ltr">Hi,
<div><br>
</div>
<div>What is the advantage of querying in Python?</div>
<div><br>
</div>
<div>Has anyone got much experience?</div>
<div><br>
</div>
<div>What not just use standard query?</div>
<div><br>
</div>
<div>What is the rationale for querying in Python?</div>
<div><br>
</div>
<div>Regards,</div>
<div><br>
</div>
<div>Shao</div>
</div>
_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@lists.osgeo.org" rel="noreferrer" target="_blank">postgis-users@lists.osgeo.org</a><br>
<a href="https://lists.osgeo.org/mailman/listinfo/postgis-users" rel="noreferrer noreferrer" target="_blank">https://lists.osgeo.org/mailman/listinfo/postgis-users</a></blockquote>
</div>
_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a><br>
<a href="https://lists.osgeo.org/mailman/listinfo/postgis-users" rel="noreferrer" target="_blank">https://lists.osgeo.org/mailman/listinfo/postgis-users</a></blockquote>
</div>
<br>
<fieldset></fieldset>
<pre>_______________________________________________
postgis-users mailing list
<a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a>
<a href="https://lists.osgeo.org/mailman/listinfo/postgis-users" target="_blank">https://lists.osgeo.org/mailman/listinfo/postgis-users</a></pre>
</blockquote>
<br>
</div>
_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a><br>
<a href="https://lists.osgeo.org/mailman/listinfo/postgis-users" rel="noreferrer" target="_blank">https://lists.osgeo.org/mailman/listinfo/postgis-users</a></blockquote></div>