Yes, I know the 2 syntax provide a different result: one provides the 6 meaningful lines, the left join do add 5 lines with a count of 0... ...
Marc MILLAS Senior Architect +33607850334 www.mokadb.com On Sat, Jan 7, 2023 at 8:46 PM Marc Millas <marc.mil...@mokadb.com> wrote: > Hi, > > postgres 12, postgis 3.0 > > I have a small table A, 11 rows with a varchar column x and a geometry > column y. > gist index on the geometry column. > the geometry do contains multipolygons (regions on a map) > I have a second table B , same structure, around 420 000 rows. > no index, > the geometry do contains points. > all geometries are on 4326 srid. > > If i ask to count points in each multipolygons: > > select A.x, count(B.x) from A, B where st_within(B.y, A.y) group by A.x; > it takes 11 seconds (everything in shared buffers). > If I do the very same thing as: > select A.x, count(B.x) from A left join B on st_within(B.y, A.y) group by > A.x; > same result, but 85 seconds (every thing in shared buffers, again) > if I redo asking with explain analyze, buffers, the plan is very different. > > > if I do create a gist index on geometry column of the big table, > both syntax takes 21 seconds. > > I get the feeling I am missing something.. (at least 2 things...) > can someone shed some light ?? > > thanks > > > Marc MILLAS > Senior Architect > +33607850334 > www.mokadb.com > >
