<div dir="ltr"><div>Hi,</div><div><br></div><div>Regarding the JOIN performance with ST_Point(),</div><div>I got a different result on PostGIS 3.0.</div><div>(Note that I changed the target machine,)<br></div><div><br></div><div>I could get faster result for the test case (2), i.e. JOIN ON table1.coord = table2.coord, </div><div>than the test case (3), i.e. JOIN ON ST_AsText(table1.coord) = ST_AsText(table2.coord) .</div><div><br></div><div>The test case (1), i.e. JOIN ON ST_Equals(table1.coord, table2.coord), is still slow</div><div>because it cannot benefit from Sort-Merge-Join.</div><div><br></div><div>Anyway, I will use the test case (2) on PostGIS 3.0.</div><div>Thank you very much for your help.</div><div><br></div><div>-- Results FROM HERE</div><div>BEGIN<br> version | postgis_version <br>------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------<br> PostgreSQL 10.12 (Debian 10.12-2.pgdg90+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 6.3.0-18+deb9u1) 6.3.0 20170516, 64-bit | 3.0 USE_GEOS=1 USE_PROJ=1 USE_STATS=1<br>(1 row)<br><br>DROP TABLE<br>DROP TABLE<br>CREATE SEQUENCE<br>SELECT 10000<br>SELECT 2601<br> QUERY PLAN <br>--------------------------------------------------------------------------------------------------------------------------<br> Aggregate (cost=1064709.98..1064709.99 rows=1 width=8) (actual time=7371.205..7371.205 rows=1 loops=1)<br> -> Nested Loop (cost=0.00..1064687.80 rows=8870 width=0) (actual time=1872.328..7370.892 rows=2601 loops=1)<br> Join Filter: ((table1.coord ~= table2.coord) AND _st_equals(table1.coord, table2.coord))<br> Rows Removed by Join Filter: 26007399<br> -> Seq Scan on table1 (cost=0.00..184.80 rows=10080 width=32) (actual time=0.005..1.112 rows=10000 loops=1)<br> -> Materialize (cost=0.00..61.60 rows=2640 width=32) (actual time=0.000..0.096 rows=2601 loops=10000)<br> -> Seq Scan on table2 (cost=0.00..48.40 rows=2640 width=32) (actual time=0.003..0.238 rows=2601 loops=1)<br> Planning time: 0.159 ms<br> Execution time: 7371.344 ms<br>(9 rows)<br><br> QUERY PLAN <br>-----------------------------------------------------------------------------------------------------------------------------<br> Aggregate (cost=1532.41..1532.42 rows=1 width=8) (actual time=10.253..10.253 rows=1 loops=1)<br> -> Merge Join (cost=1053.52..1465.88 rows=26611 width=0) (actual time=6.229..10.094 rows=2601 loops=1)<br> Merge Cond: (table2.coord = table1.coord)<br> -> Sort (cost=198.44..205.04 rows=2640 width=32) (actual time=1.177..1.295 rows=2601 loops=1)<br> Sort Key: table2.coord<br> Sort Method: quicksort Memory: 300kB<br> -> Seq Scan on table2 (cost=0.00..48.40 rows=2640 width=32) (actual time=0.007..0.295 rows=2601 loops=1)<br> -> Sort (cost=855.08..880.28 rows=10080 width=32) (actual time=4.835..5.213 rows=8676 loops=1)<br> Sort Key: table1.coord<br> Sort Method: quicksort Memory: 1166kB<br> -> Seq Scan on table1 (cost=0.00..184.80 rows=10080 width=32) (actual time=0.005..1.167 rows=10000 loops=1)<br> Planning time: 0.240 ms<br> Execution time: 10.436 ms<br>(13 rows)<br><br> QUERY PLAN <br>------------------------------------------------------------------------------------------------------------------------------<br> Aggregate (cost=10180.00..10180.01 rows=1 width=8) (actual time=48.501..48.501 rows=1 loops=1)<br> -> Merge Join (cost=1053.52..9847.36 rows=133056 width=0) (actual time=45.565..48.353 rows=2601 loops=1)<br> Merge Cond: ((st_astext(table2.coord)) = (st_astext(table1.coord)))<br> -> Sort (cost=198.44..205.04 rows=2640 width=32) (actual time=4.414..4.516 rows=2601 loops=1)<br> Sort Key: (st_astext(table2.coord))<br> Sort Method: quicksort Memory: 300kB<br> -> Seq Scan on table2 (cost=0.00..48.40 rows=2640 width=32) (actual time=0.020..3.571 rows=2601 loops=1)<br> -> Sort (cost=855.08..880.28 rows=10080 width=32) (actual time=40.711..40.996 rows=7329 loops=1)<br> Sort Key: (st_astext(table1.coord))<br> Sort Method: quicksort Memory: 1166kB<br> -> Seq Scan on table1 (cost=0.00..184.80 rows=10080 width=32) (actual time=0.007..13.382 rows=10000 loops=1)<br> Planning time: 0.115 ms<br> Execution time: 48.632 ms<br>(13 rows)<br><br>COMMIT<br></div><div>-- Results TO HERE</div><div dir="ltr"><br></div><div>Best regards,</div><div>Ryo</div><br><div class="gmail_quote"><div dir="ltr" class="gmail_attr">2020年3月6日(金) 8:51 Ryo NEYAMA <neyama@toyota-tokyo.tech>:<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"><div>Hello,</div><div><br></div><div>Thank you for the information.</div><div>I'll try it with PostGIS 3.0.</div><div><br></div><div>Ryo</div><br><div class="gmail_quote"><div dir="ltr" class="gmail_attr">2020年3月5日(木) 19:01 Darafei "Komяpa" Praliaskouski <<a href="mailto:me@komzpa.net" target="_blank">me@komzpa.net</a>>:<br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">Hello,<br>
<br>
Equality has been significantly reworked in 3.0. Can you reproduce<br>
your issue in PostGIS 3.0?<br>
<br>
On Thu, Mar 5, 2020 at 9:46 AM Ryo NEYAMA <neyama@toyota-tokyo.tech> wrote:<br>
><br>
> Hello,<br>
><br>
> I had a performance issue when I JOIN two tables by using the equivalence<br>
> of two ST_Point()s.<br>
> I'm wondering if it is a kind of specification...<br>
><br>
> -- SQL statements FROM HERE<br>
> BEGIN;<br>
><br>
> SELECT version(), postgis_version();<br>
><br>
> DROP TABLE IF EXISTS table1;<br>
> DROP TABLE IF EXISTS table2;<br>
><br>
> CREATE TEMPORARY SEQUENCE gen_serial;<br>
><br>
> CREATE TABLE table1 AS (<br>
> SELECT<br>
> nextval('gen_serial') AS id,<br>
> ST_Point(x, y) AS coord<br>
> FROM (<br>
> SELECT<br>
> x, y<br>
> FROM<br>
> (SELECT generate_series(1, 100) x) set_x<br>
> CROSS JOIN<br>
> (SELECT generate_series(1, 100) y) set_y<br>
> ) tmp<br>
> );<br>
><br>
> CREATE TABLE table2 AS (<br>
> SELECT<br>
> nextval('gen_serial') AS id,<br>
> ST_Point(x, y) AS coord<br>
> FROM (<br>
> SELECT<br>
> x, y<br>
> FROM<br>
> (SELECT generate_series(25, 75) x) set_x<br>
> CROSS JOIN<br>
> (SELECT generate_series(25, 75) y) set_y<br>
> ) tmp<br>
> );<br>
><br>
> -- test case (1)<br>
> EXPLAIN ANALYZE<br>
> SELECT<br>
> COUNT(*)<br>
> FROM<br>
> table1<br>
> JOIN<br>
> table2<br>
> ON<br>
> ST_Equals(table1.coord, table2.coord)<br>
> ;<br>
><br>
> -- test case (2)<br>
> EXPLAIN ANALYZE<br>
> SELECT<br>
> COUNT(*)<br>
> FROM<br>
> table1<br>
> JOIN<br>
> table2<br>
> ON<br>
> table1.coord = table2.coord<br>
> ;<br>
><br>
> -- test case (3)<br>
> EXPLAIN ANALYZE<br>
> SELECT<br>
> COUNT(*)<br>
> FROM<br>
> table1<br>
> JOIN<br>
> table2<br>
> ON<br>
> ST_AsText(table1.coord) = ST_AsText(table2.coord)<br>
> ;<br>
><br>
> END;<br>
><br>
> -- SQL statements TO HERE<br>
><br>
> -- Results FROM HERE<br>
> BEGIN<br>
> version<br>
> |<br>
> postgis_version<br>
> ----------------------------------------------------------------------------------------------------------------------------------+---------------------------------------<br>
> PostgreSQL 11.2 (Debian 11.2-1.pgdg90+1) on x86_64-pc-linux-gnu, compiled<br>
> by gcc (Debian 6.3.0-18+deb9u1) 6.3.0 20170516, 64-bit | 2.5 USE_GEOS=1<br>
> USE_PROJ=1 USE_STATS=1<br>
> (1 row)<br>
><br>
> DROP TABLE<br>
> DROP TABLE<br>
> CREATE SEQUENCE<br>
> SELECT 10000<br>
> SELECT 2601<br>
> QUERY PLAN<br>
><br>
> --------------------------------------------------------------------------------------------------------------------------<br>
> Aggregate (cost=7052229.97..7052229.98 rows=1 width=8) (actual<br>
> time=7096.372..7096.373 rows=1 loops=1)<br>
> -> Nested Loop (cost=0.00..7052207.80 rows=8870 width=0) (actual<br>
> time=1692.069..7096.193 rows=2601 loops=1)<br>
> Join Filter: ((table1.coord ~= table2.coord) AND<br>
> _st_equals(table1.coord, table2.coord))<br>
> Rows Removed by Join Filter: 26007399<br>
> -> Seq Scan on table1 (cost=0.00..184.80 rows=10080 width=32)<br>
> (actual time=0.004..0.767 rows=10000 loops=1)<br>
> -> Materialize (cost=0.00..61.60 rows=2640 width=32) (actual<br>
> time=0.000..0.090 rows=2601 loops=10000)<br>
> -> Seq Scan on table2 (cost=0.00..48.40 rows=2640<br>
> width=32) (actual time=0.004..0.244 rows=2601 loops=1)<br>
> Planning Time: 0.136 ms<br>
> Execution Time: 7096.493 ms<br>
> (9 rows)<br>
><br>
> QUERY PLAN<br>
><br>
> --------------------------------------------------------------------------------------------------------------------------<br>
> Aggregate (cost=399474.33..399474.34 rows=1 width=8) (actual<br>
> time=6870.980..6870.980 rows=1 loops=1)<br>
> -> Nested Loop (cost=0.00..399407.80 rows=26611 width=0) (actual<br>
> time=1665.642..6870.790 rows=2601 loops=1)<br>
> Join Filter: (table1.coord = table2.coord)<br>
> Rows Removed by Join Filter: 26007399<br>
> -> Seq Scan on table1 (cost=0.00..184.80 rows=10080 width=32)<br>
> (actual time=0.004..0.870 rows=10000 loops=1)<br>
> -> Materialize (cost=0.00..61.60 rows=2640 width=32) (actual<br>
> time=0.000..0.099 rows=2601 loops=10000)<br>
> -> Seq Scan on table2 (cost=0.00..48.40 rows=2640<br>
> width=32) (actual time=0.004..0.258 rows=2601 loops=1)<br>
> Planning Time: 0.031 ms<br>
> Execution Time: 6871.063 ms<br>
> (9 rows)<br>
><br>
> QUERY PLAN<br>
><br>
> ------------------------------------------------------------------------------------------------------------------------------<br>
> Aggregate (cost=512255.20..512255.21 rows=1 width=8) (actual<br>
> time=44.998..44.998 rows=1 loops=1)<br>
> -> Merge Join (cost=1053.52..511922.56 rows=133056 width=0) (actual<br>
> time=42.285..44.865 rows=2601 loops=1)<br>
> Merge Cond: ((st_astext(table2.coord)) = (st_astext(table1.coord)))<br>
> -> Sort (cost=198.44..205.04 rows=2640 width=32) (actual<br>
> time=4.408..4.514 rows=2601 loops=1)<br>
> Sort Key: (st_astext(table2.coord))<br>
> Sort Method: quicksort Memory: 300kB<br>
> -> Seq Scan on table2 (cost=0.00..48.40 rows=2640<br>
> width=32) (actual time=0.018..3.587 rows=2601 loops=1)<br>
> -> Sort (cost=855.08..880.28 rows=10080 width=32) (actual<br>
> time=37.466..37.760 rows=7329 loops=1)<br>
> Sort Key: (st_astext(table1.coord))<br>
> Sort Method: quicksort Memory: 1166kB<br>
> -> Seq Scan on table1 (cost=0.00..184.80 rows=10080<br>
> width=32) (actual time=0.005..13.276 rows=10000 loops=1)<br>
> Planning Time: 0.088 ms<br>
> Execution Time: 45.104 ms<br>
> (13 rows)<br>
><br>
> COMMIT<br>
> -- Results TO HERE<br>
><br>
> The above three SQL statements, test case (1), (2), and (3) provides<br>
> exactly the same result, i.e. count = 2601.<br>
><br>
> The test case (1) uses ST_Equals and (2) uses '=' for comparison.<br>
> The test case (3) converts a ST_Point to text by using ST_AsText().<br>
> The test case (1)(2) is more intuitive and straightforward than the test<br>
> case (3).<br>
> However, the test case (3) is much faster than the test cases (1) and (2).<br>
><br>
> As described in QUERY PLAN, only the test case (3) could benefit from<br>
> Sort-Merge-Join.<br>
> Is it possible to get the equivalent performance with test case (1) and (2)?<br>
><br>
> Best regards,<br>
> Ryo Neyama<br>
> _______________________________________________<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><br>
<br>
<br>
<br>
-- <br>
Darafei Praliaskouski<br>
Support me: <a href="http://patreon.com/komzpa" rel="noreferrer" target="_blank">http://patreon.com/komzpa</a><br>
_______________________________________________<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></div>
</blockquote></div></div>