[postgis-tickets] [PostGIS] #3864: Sorting by geometry is slower than sorting by geohash

[PostGIS]

#3864: Sorting by geometry is slower than sorting by geohash
----------------------+---------------------------
  Reporter:  komzpa   |      Owner:  pramsey
      Type:  defect   |     Status:  new
  Priority:  medium   |  Milestone:  PostGIS 2.4.1
 Component:  postgis  |    Version:  2.3.x
Resolution:           |   Keywords:
----------------------+---------------------------

Comment (by gsmol):

 About 128GB been mercilessly eated in komzpa`s case:
 via gdb I can detect that there is a lot of palloc() coming from
 lwgeom_cmp, but no pfree():

 #0  palloc (size=size at entry=32) at
 ./build/../src/backend/utils/mmgr/mcxt.c:850
 #1  0x00005575da620a0a in heap_tuple_untoast_attr (attr=0x5577c344d1c0) at
 ./build/../src/backend/access/heap/tuptoaster.c:240
 #2  0x00007f6c77386af2 in lwgeom_cmp () from
 /usr/lib/postgresql/10/lib/postgis-2.4.so
 #3  0x00005575da9e76b1 in comparison_shim (x=<optimized out>, y=<optimized
 out>, ssup=<optimized out>)
     at ./build/../src/backend/utils/sort/sortsupport.c:53
 #4  0x00005575da9ec64e in ApplySortComparator (ssup=0x5575dbbb7b98,
 isNull2=<optimized out>, datum2=<optimized out>, isNull1=<optimized out>,
     datum1=<optimized out>) at
 ./build/../src/include/utils/sortsupport.h:225
 #5  qsort_ssup (a=0x7f6c37b25078, n=<optimized out>, n at entry=14,
 ssup=ssup at entry=0x5575dbbb7b98) at ./qsort_tuple.c:244
 #6  0x00005575da9ec930 in qsort_ssup (a=0x7f6c37b24b20,
 a at entry=0x7f6c37b24340, n=<optimized out>, ssup=ssup at entry=0x5575dbbb7b98)
     at ./qsort_tuple.c:323
 #7  0x00005575da9ec55d in qsort_ssup (a=0x7f6c37b24340, n=<optimized out>,
 n at entry=778, ssup=ssup at entry=0x5575dbbb7b98) at ./qsort_tuple.c:309
 #8  0x00005575da9ec930 in qsort_ssup (a=0x7f6c37b1d470, n=<optimized out>,
 n at entry=40979, ssup=ssup at entry=0x5575dbbb7b98) at ./qsort_tuple.c:323
 #9  0x00005575da9ec930 in qsort_ssup (a=0x7f6c37924d20, n=<optimized out>,
 n at entry=140380, ssup=ssup at entry=0x5575dbbb7b98) at ./qsort_tuple.c:323
 #10 0x00005575da9ec930 in qsort_ssup (a=0x7f6c3755cf20,
 a at entry=0x7f6c3710f6c8, n=<optimized out>, ssup=ssup at entry=0x5575dbbb7b98)
     at ./qsort_tuple.c:323
 #11 0x00005575da9ec55d in qsort_ssup (a=a at entry=0x7f6c3710f6c8,
 n=<optimized out>, ssup=ssup at entry=0x5575dbbb7b98) at ./qsort_tuple.c:309
 #12 0x00005575da9ec55d in qsort_ssup (a=a at entry=0x7f6c3710f6c8,
 n=<optimized out>, ssup=ssup at entry=0x5575dbbb7b98) at ./qsort_tuple.c:309
 #13 0x00005575da9ec55d in qsort_ssup (a=a at entry=0x7f6c3710f6c8,
 n=<optimized out>, ssup=ssup at entry=0x5575dbbb7b98) at ./qsort_tuple.c:309
 #14 0x00005575da9ec55d in qsort_ssup (a=0x7f6c3710f6c8, n=<optimized out>,
 n at entry=20343532, ssup=ssup at entry=0x5575dbbb7b98) at ./qsort_tuple.c:309
 #15 0x00005575da9ec930 in qsort_ssup (a=0x7f6c161fb048, n=<optimized out>,
 ssup=<optimized out>) at ./qsort_tuple.c:323
 #16 0x00005575da9eca89 in tuplesort_sort_memtuples
 (state=state at entry=0x5575dbbb7788) at
 ./build/../src/backend/utils/sort/tuplesort.c:3514

--
Ticket URL: <https://trac.osgeo.org/postgis/ticket/3864#comment:10>
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.