Skip to main content
added 1396 characters in body
Source Link
madpom
  • 185
  • 7

Indexing & fields used above:

dem1=# \d contour
                                       Table "public.contour"
    Column    |          Type          | Collation | Nullable |               Default                
--------------+------------------------+-----------+----------+--------------------------------------
 fid          | integer                |           | not null | nextval('contour_fid_seq'::regclass)
 ele          | double precision       |           |          | 
 wkb_geometry | geometry(Polygon,4326) |           |          | 
Indexes:
    "contour_pkey" PRIMARY KEY, btree (fid)
    "contour_wkb_geometry_geom_idx" gist (wkb_geometry)

Peaks:

dem1=# \d peaks
                                         Table "public.peaks"
    Column     |         Type         | Collation | Nullable |                Default                 
---------------+----------------------+-----------+----------+----------------------------------------
 ogc_fid       | integer              |           | not null | nextval('peaks_ogc_fid_seq'::regclass)
 elevation     | numeric(24,15)       |           |          | 
 wkb_geometry  | geometry(Point,4326) |           |          | 
Indexes:
    "peaks_pkey" PRIMARY KEY, btree (ogc_fid)
    "peaks_wkb_geometry_geom_idx" gist (wkb_geometry)

Indexing & fields used above:

dem1=# \d contour
                                       Table "public.contour"
    Column    |          Type          | Collation | Nullable |               Default                
--------------+------------------------+-----------+----------+--------------------------------------
 fid          | integer                |           | not null | nextval('contour_fid_seq'::regclass)
 ele          | double precision       |           |          | 
 wkb_geometry | geometry(Polygon,4326) |           |          | 
Indexes:
    "contour_pkey" PRIMARY KEY, btree (fid)
    "contour_wkb_geometry_geom_idx" gist (wkb_geometry)

Peaks:

dem1=# \d peaks
                                         Table "public.peaks"
    Column     |         Type         | Collation | Nullable |                Default                 
---------------+----------------------+-----------+----------+----------------------------------------
 ogc_fid       | integer              |           | not null | nextval('peaks_ogc_fid_seq'::regclass)
 elevation     | numeric(24,15)       |           |          | 
 wkb_geometry  | geometry(Point,4326) |           |          | 
Indexes:
    "peaks_pkey" PRIMARY KEY, btree (ogc_fid)
    "peaks_wkb_geometry_geom_idx" gist (wkb_geometry)
Added additional info as requested
Source Link
madpom
  • 185
  • 7

===

Further information as requested:

Table sizes:

 Schema |        Name         |   Type   |  Owner   |    Size    | Description 
--------+---------------------+----------+----------+------------+-------------
 public | contour             | table    | mbriggs  | 68 GB      | 
 public | contour_fid_seq     | sequence | mbriggs  | 8192 bytes | 
 public | peaks               | table    | mbriggs  | 7720 kB    | 
 public | peaks_ogc_fid_seq   | sequence | mbriggs  | 8192 bytes | 

Individual contour size (vertices) - max for example elevations:

# select ele, max(ST_NPoints(wkb_geometry)) as vert from contour  where ele in (1,100,1000,2000) group by ele;
 ele  |  vert   
------+---------
    1 | 1803355
  100 | 2097301
 1000 | 1019721
 2000 |   16959

Original query:

                                       QUERY PLAN                                       
----------------------------------------------------------------------------------------
 Limit  (cost=15637.61..15637.62 rows=1 width=204)
   InitPlan 1 (returns $0)
     ->  Index Scan using contour_pkey on contour  (cost=0.43..8.45 rows=1 width=44763)
           Index Cond: (fid = 13873)
   ->  Sort  (cost=15629.17..15641.21 rows=4816 width=204)
         Sort Key: p.elevation DESC
         ->  Seq Scan on peaks p  (cost=0.00..15605.09 rows=4816 width=204)
               Filter: ((elevation > '1000'::numeric) AND st_within(wkb_geometry, $0))

Faster query (but will this reliably return max altitude peak?):

                                                        QUERY PLAN                                                        
--------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=230.97..230.98 rows=1 width=204)
   InitPlan 1 (returns $0)
     ->  Index Scan using contour_pkey on contour  (cost=0.43..8.45 rows=1 width=44763)
           Index Cond: (fid = 13873)
   ->  Sort  (cost=222.52..222.53 rows=2 width=204)
         Sort Key: p.elevation DESC
         InitPlan 2 (returns $1)
           ->  Index Scan using contour_pkey on contour contour_1  (cost=0.43..8.45 rows=1 width=32)
                 Index Cond: (fid = 13873)
         ->  Bitmap Heap Scan on peaks p  (cost=4.70..214.06 rows=2 width=204)
               Recheck Cond: ($0 ~ wkb_geometry)
               Filter: ((elevation > '1000'::numeric) AND st_within(wkb_geometry, $1) AND _st_contains($0, wkb_geometry))
               ->  Bitmap Index Scan on peaks_wkb_geometry_geom_idx  (cost=0.00..4.70 rows=56 width=0)
                     Index Cond: ($0 ~ wkb_geometry)

===

Further information as requested:

Table sizes:

 Schema |        Name         |   Type   |  Owner   |    Size    | Description 
--------+---------------------+----------+----------+------------+-------------
 public | contour             | table    | mbriggs  | 68 GB      | 
 public | contour_fid_seq     | sequence | mbriggs  | 8192 bytes | 
 public | peaks               | table    | mbriggs  | 7720 kB    | 
 public | peaks_ogc_fid_seq   | sequence | mbriggs  | 8192 bytes | 

Individual contour size (vertices) - max for example elevations:

# select ele, max(ST_NPoints(wkb_geometry)) as vert from contour  where ele in (1,100,1000,2000) group by ele;
 ele  |  vert   
------+---------
    1 | 1803355
  100 | 2097301
 1000 | 1019721
 2000 |   16959

Original query:

                                       QUERY PLAN                                       
----------------------------------------------------------------------------------------
 Limit  (cost=15637.61..15637.62 rows=1 width=204)
   InitPlan 1 (returns $0)
     ->  Index Scan using contour_pkey on contour  (cost=0.43..8.45 rows=1 width=44763)
           Index Cond: (fid = 13873)
   ->  Sort  (cost=15629.17..15641.21 rows=4816 width=204)
         Sort Key: p.elevation DESC
         ->  Seq Scan on peaks p  (cost=0.00..15605.09 rows=4816 width=204)
               Filter: ((elevation > '1000'::numeric) AND st_within(wkb_geometry, $0))

Faster query (but will this reliably return max altitude peak?):

                                                        QUERY PLAN                                                        
--------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=230.97..230.98 rows=1 width=204)
   InitPlan 1 (returns $0)
     ->  Index Scan using contour_pkey on contour  (cost=0.43..8.45 rows=1 width=44763)
           Index Cond: (fid = 13873)
   ->  Sort  (cost=222.52..222.53 rows=2 width=204)
         Sort Key: p.elevation DESC
         InitPlan 2 (returns $1)
           ->  Index Scan using contour_pkey on contour contour_1  (cost=0.43..8.45 rows=1 width=32)
                 Index Cond: (fid = 13873)
         ->  Bitmap Heap Scan on peaks p  (cost=4.70..214.06 rows=2 width=204)
               Recheck Cond: ($0 ~ wkb_geometry)
               Filter: ((elevation > '1000'::numeric) AND st_within(wkb_geometry, $1) AND _st_contains($0, wkb_geometry))
               ->  Bitmap Index Scan on peaks_wkb_geometry_geom_idx  (cost=0.00..4.70 rows=56 width=0)
                     Index Cond: ($0 ~ wkb_geometry)
naming
Link
Vince
  • 20.5k
  • 16
  • 49
  • 65

postgis PostGIS ST_Within query - efficient way to order & limit

include possible solution and ask for comments on whether it will work reliably
Source Link
madpom
  • 185
  • 7
Loading
edited title
Source Link
madpom
  • 185
  • 7
Loading
added 27 characters in body
Source Link
madpom
  • 185
  • 7
Loading
added 94 characters in body
Source Link
madpom
  • 185
  • 7
Loading
Source Link
madpom
  • 185
  • 7
Loading