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: | Re: impact join syntax ?? and gist index ?? |
Date: | 2023-01-07 19:55:13 |
Message-ID: | CADX_1abyV2PzhU6RmfYmf_A7S_LpW0hq5oMtd1CikRNVMpkxRQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Yes, I know the 2 syntax provide a different result: one provides the 6
meaningful lines, the left join do add 5 lines with a count of 0...
...
Marc MILLAS
Senior Architect
+33607850334
www.mokadb.com
On Sat, Jan 7, 2023 at 8:46 PM Marc Millas <marc(dot)millas(at)mokadb(dot)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 ??
>
> thanks
>
>
> Marc MILLAS
> Senior Architect
> +33607850334
> www.mokadb.com
>
>
From | Date | Subject | |
---|---|---|---|
Next Message | Marc Millas | 2023-01-07 20:38:16 | Re: impact join syntax ?? and gist index ?? |
Previous Message | Marc Millas | 2023-01-07 19:46:29 | impact join syntax ?? and gist index ?? |