The thing about CTEs is that in PostgreSQL 12+ they don’t necessarily
materialize. So the behavior should be the same CTE / Subquery
(unless you throw in an OFFSET 0 which will absolutely force a
materialization). You can do that in subquery as well.
AS mentioned in my other note – ST_Area, ST_Perimeter are very low
cost functions so I would expect no materialization for PG 12+
regardless if you use CTE or subquery.
*From:*postgis-users [mailto:[email protected]]
*On Behalf Of *Marco Boeringa
*Sent:* Monday, September 28, 2020 3:37 AM
*To:* [email protected]
*Subject:* Re: [postgis-users] Setting multiple columns in one UPDATE
request
Regina,
Thanks for your suggestion.
How is this performance wise? Is not using a CTE as in your
suggestion, supposedly faster than with using a CTE, or is this just a
syntax thing and performance is expected to be equal?
It would still be nice though, if PostgreSQL somehow handled this
automatically, and one could use the most basic form yet be sure it
was optimized. It also reads more easily to just see:
UPDATE <MY_TABLE> SET area = ST_Area(<GEOMETRY_COLUMN>),
area_perimeter = ST_Area(<GEOMETRY_COLUMN>) /
ST_Perimeter(<GEOMETRY_COLUMN>)
in your code, instead of more elaborate construct involving a join.
Marco
Op 28-9-2020 om 03:26 schreef Regina Obe:
I prefer doing it in the FROM and not bothering using a CTE.
So something like
UPDATE <MY_TABLE> SET area = f.area, area_perimeter = f.area/f.perimeter
FROM (SELECT id, ST_Area(<GEOMETRY_COLUMN>) AS area, ST_Perimeter(<GEOMETRY
COLUMN>) AS perimeter
FROM <MY TABLE> ) AS f
WHERE f.id = <MY TABLE>.id;
Note the FROM does not need to be the same as your table, you just
need to have a common join. That makes it particularly handy for
updating with aggregate values
*From:*postgis-users
[mailto:[email protected]] *On Behalf Of
*Marco Boeringa
*Sent:* Sunday, September 27, 2020 1:18 PM
*To:* [email protected]
<mailto:[email protected]>
*Subject:* Re: [postgis-users] Setting multiple columns in one
UPDATE request
Thanks for pointing that out Alexander. I was just about to start
a test after some code modifications, but now realized based on
the example you pointed out that I was missing the "FROM cte"
clause in my SQL statement. It feels a bit unnatural to have to
specify that one, as you already define the cte name after the
WITH keyword. But this example sorted it out.
Curious to see how it runs and if it leads to a measurable
performance enhancement.
Marco
Op 27-9-2020 om 18:30 schreef Alexander Gataric:
You have the correct link. Here's an example.
https://stackoverflow.com/questions/36908495/update-with-result-from-cte-postgresql
Get BlueMail for Android <http://www.bluemail.me/r?b=16117>
On Sep 27, 2020, at 7:47 AM, Marco Boeringa
<[email protected] <mailto:[email protected]>> wrote:
Hi Alexander,
I guess I could use the optional WITH clause that is part
of UPDATE in PostgreSQL?:
https://www.postgresql.org/docs/12/sql-update.html
It would have been nice to see an example of such usage in
the PostgreSQL help, but I'll figure it out.
Marco
Op 27-9-2020 om 13:59 schreef Alexander Gataric:
Rewrite to have a CTE with the area calculations and
join to the table.
Get BlueMail for Android
<http://www.bluemail.me/r?b=16117>
On Sep 27, 2020, at 2:29 AM, Marco Boeringa
<[email protected]
<mailto:[email protected]>> wrote:
Hi all,
This may be an irrelevant basic question, but I just cannot
find a clear
answer to this, there is no documentation in the PostgreSQL
docs for
UPDATE about this:
In case I set multiple columns using some PostGIS function
that clearly
has a (considerable) cost associated with it, and the value
of the first
column being set is also needed to set the second column,
does
PostgreSQL automatically optimize this and re-use the value
already
calculated for column 1 to set column 2's value, or is each
column's SET
statement treated as independent entity?
E.g. let's say I want to calculate both area, and the
division of area
and perimeter, like:
UPDATE <MY_TABLE> SET area = ST_Area(<GEOMETRY_COLUMN>),
area_perimeter
= ST_Area(<GEOMETRY_COLUMN>) /
ST_Perimeter(<GEOMETRY_COLUMN>)
Will PostgreSQL only process 'ST_Area(<GEOMETRY_COLUMN>)'
once in this
case, thus saving CPU load, or do I need to rewrite the SQL
statement
somehow to achieve that?
Marco
------------------------------------------------------------------------
postgis-users mailing list
[email protected]
<mailto:[email protected]>
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
------------------------------------------------------------------------
postgis-users mailing list
[email protected]
<mailto:[email protected]>
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
_______________________________________________
postgis-users mailing list
[email protected] <mailto:[email protected]>
https://lists.osgeo.org/mailman/listinfo/postgis-users
_______________________________________________
postgis-users mailing list
[email protected]
https://lists.osgeo.org/mailman/listinfo/postgis-users
