Re: impact join syntax ?? and gist index ??

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 <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Marc Millas <marc(dot)millas(at)mokadb(dot)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
>