[postgis-devel] The index is not optimal. To optimize it, contact a developer, or try to use the column as the second one in the CREATE INDEX command.

Fri Feb 4 07:04:53 PST 2022

In 2016 in pgsql-general Tom Lane posts that this is normal working
condition message if you have a whole page of index with same ranges:

https://www.postgresql.org/message-id/27333.1476387518%40sss.pgh.pa.us

If you're doing many updates this is expected so the only way to fight that
is to minimize the number of updates.

Alternatively you can use PROCEDUREs that are outside transactions. VACUUM
FULL for some stupid overlook does not work there but CLUSTER which is the
same thing does.

On Fri, Feb 4, 2022 at 3:52 PM Sandro Santilli <strk at kbt.io> wrote:

> On Fri, Feb 04, 2022 at 01:07:42PM +0100, Sandro Santilli wrote:
> > On Fri, Feb 04, 2022 at 02:26:48PM +0300, Darafei "Komяpa" Praliaskouski
> wrote:
> >
> > > What branch is it on?
> >
> > Master branch as of commit 0ffe2870eade1a26a1573eb11e7540eacc207783.
> >
> > > I touched picksplit on master but it passed the tests fine.
> >
> > It's not an error, just a DEBUG with an HINT.
> >
> > > This normally can happen if you're pushing dozens of same values into
> > > index. I'd also expect it on EMPTY's. May happen if you're having a
> lot of
> > > updates on one row without vacuum collecting stuff.
> >
> > Topology building does have a lot of updates of the same rows over and
> > over again (and never expliclty runs vacuum). Maybe this would be good
> > to change.
>
> Is there now a way to decide which picksplit method to use for a given
> index ? Because during execution of topology building function there's
> no way to VACUUM becuase changes are all still not committed, so the
> index will always (for such batch loads).
>
> --strk;
> _______________________________________________
> postgis-devel mailing list
> postgis-devel at lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/postgis-devel
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-devel/attachments/20220204/501347d4/attachment.html>