Marc Millas writes:
> I read your answer, Tom, but I cannot connect it to my measurements: why
> adding the index did slow the request twice ??
Are you referring to
>>> if I do create a gist index on geometry column of the big table,
>>> both syntax takes 21 seconds.
? That result is pretty mu
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 wrote:
> Marc Millas writes:
> > 2023=# explain (analyze, buffers) select
Marc Millas 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 r
here they are: (I replace the column and table names) also I post 2 more
remarks, one on left join, and one on the test I did on postgres 15 postgis
3.3...
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;
> On 07/01/2023 20:46 CET Marc Millas 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 , sam
on postgres 15 and postgis 3.3, with the very same dataset,
without gist index on the 420k rows table, the syntax with the left join
takes 25 seconds, and without 770 ms.
so to get 5 empty lines its 30 times slower.
if I add the gist index, both syntaxes takes 770 ms...
at least, this close the di
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 wrote:
> Hi,
>
> postgres 12, postgis 3.0
>
> I h
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 conta
Суббота, 7 января 2023, 20:23 +03:00 от Peter J. Holzer :
>On 2023-01-07 07:40:01 -0600, Ron wrote:
>> On 1/7/23 05:29, Peter J. Holzer wrote:
>> If I understood correctly, you have to delete about 3 million records
>> (worst case) from the main table each day. Including the other 8 tab
On 2023-01-07 07:40:01 -0600, Ron wrote:
> On 1/7/23 05:29, Peter J. Holzer wrote:
> If I understood correctly, you have to delete about 3 million records
> (worst case) from the main table each day. Including the other 8 tables
> those are 27 million DELETE queries each of which delete
On 1/7/23 05:29, Peter J. Holzer wrote:
[snip]
If I understood correctly, you have to delete about 3 million records
(worst case) from the main table each day. Including the other 8 tables
those are 27 million DELETE queries each of which deletes only a few
records. That's about 300 queries per s
On 2023-01-07 05:33:33 +, Ranjith Paliyath wrote:
>
> > This is because the deletion step is executed record
> > by record in main table, with its connected record(s)
> > delete executions in rest of tables?
>
> > I don't know if you have ON DE
12 matches
Mail list logo
