impact join syntax ?? and gist index ??

From: Marc Millas <marc(dot)millas(at)mokadb(dot)com>
To: "pgsql-general(at)lists(dot)postgresql(dot)org" <pgsql-general(at)lists(dot)postgresql(dot)org>
Subject: impact join syntax ?? and gist index ??
Date: 2023-01-07 19:46:29
Message-ID: CADX_1aabU_Og_tCEKX4MK=jkq1stgwFmxi0RsX5Qq8g1C1=LBg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Marc Millas 2023-01-07 19:55:13 Re: impact join syntax ?? and gist index ??
Previous Message Николай Кобзарев 2023-01-07 17:38:39 Re: Purging few months old data and vacuuming in production