Обсуждение: impact join syntax ?? and gist index ??

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

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

> On 07/01/2023 20:46 CET Marc Millas <marc.millas@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 ??

Please provide the executions plans for both queries with and without the index on B.y.

--
Erik

Marc Millas <marc.millas@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