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
>

Reply via email to