I read your answer, Tom, but I cannot connect it to my measurements: why adding the index did slow the request twice ??
Marc MILLAS Senior Architect +33607850334 www.mokadb.com On Sat, Jan 7, 2023 at 10:33 PM Tom Lane <t...@sss.pgh.pa.us> wrote: > Marc Millas <marc.mil...@mokadb.com> writes: > > 2023=# explain (analyze, buffers) select A.x, count(B.x) from A left > join B > > on st_within(B.geom, A.geom) group by A.x; > > So the problem with this is that the only decently-performant way to > do the join is like > > > -> Nested Loop (cost=0.13..6275745.36 > rows=4971999 width=16) (actual time=0.505..11781.817 rows=210651 loops=2) > > -> Parallel Seq Scan on B > (cost=0.00..18454.99 rows=248699 width=40) (actual time=0.005..22.859 > rows=211395 loops=2) > > -> Index Scan using A_geom_idx on A > (cost=0.13..25.15 rows=1 width=17752) (actual time=0.054..0.055 rows=1 > loops=422789) > > Index Cond: (geom ~ B.geom) > > Filter: st_within(B.geom, geom) > > (Ignore the parallelism, it's not very relevant here.) There's no > chance for merge or hash join because those require simple equality > join conditions. The only way to avoid a stupid > compare-every-row-of-A-to-every-row-of-B nestloop is to use a > parameterized inner indexscan, as this plan does. But that only works > if the join is inner or has the indexed table on the nullable side. > We have no support for nestloop right join, which is what would be > needed to make things run fast with no index on B. > > regards, tom lane >
