From: | Marc Millas <marc(dot)millas(at)mokadb(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | Erik Wienhold <ewie(at)ewie(dot)name>, "pgsql-general(at)lists(dot)postgresql(dot)org" <pgsql-general(at)lists(dot)postgresql(dot)org> |
Subject: | Re: impact join syntax ?? and gist index ?? |
Date: | 2023-01-07 21:47:24 |
Message-ID: | CADX_1aYeLc7rzV0zU=_FubFw7i6Ge2R_eVr2vzgvbG31EFo0nQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
>
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2023-01-07 22:07:16 | Re: impact join syntax ?? and gist index ?? |
Previous Message | Tom Lane | 2023-01-07 21:32:57 | Re: impact join syntax ?? and gist index ?? |