[postgis-tickets] [PostGIS] #3344: Unnecessary use of table/possible speed improvement

[PostGIS]

#3344: Unnecessary use of table/possible speed improvement
--------------------------+---------------------------
  Reporter:  postgispaul  |      Owner:  pramsey
      Type:  enhancement  |     Status:  closed
  Priority:  medium       |  Milestone:  PostGIS 2.1.9
 Component:  postgis      |    Version:  2.1.x
Resolution:  wontfix      |   Keywords:
--------------------------+---------------------------

Comment (by robe):

 It does.  Because if the query planner can't guarantee that a join with
 names table doesn't change the count of rows to process, then it can't
 just ignore it.  That's how this new feature (it was in 9.0 actually)
 works.

 From 9.0 release notes:
 http://www.postgresql.org/docs/9.0/static/release-9-0.html

 ----
 E.24.3.1.3. Optimizer

     Remove unnecessary outer joins (Robert Haas)

     Outer joins where the inner side is unique and not referenced above
 the join are unnecessary and are therefore now removed. This will
 accelerate many automatically generated queries, such as those created by
 object-relational mappers (ORMs).
 ----

 Even though ST_Extent answer wouldn't change for this particular case you
 have, the planner doesn't know the details of what goes in ST_Extent, so
 can't assume if it passes in 3 duplicatate rows the end result is same as
 single row.  It treats it like a black box.


 So I would suggest if you can to put a primary key or unique key on the
 names.id field.

--
Ticket URL: <https://trac.osgeo.org/postgis/ticket/3344#comment:4>
PostGIS <http://trac.osgeo.org/postgis/>
The PostGIS Trac is used for bug, enhancement & task tracking, a user and developer wiki, and a view into the subversion code repository of PostGIS project.