Suboptimal GIST index?

From: Jeremy Finzel <finzelj(at)gmail(dot)com>
To: PostgreSQL General <pgsql-general(at)lists(dot)postgresql(dot)org>
Subject: Suboptimal GIST index?
Date: 2022-12-06 16:24:34
Message-ID: CAMa1XUiyQT8e1SaKerTw1rRWAoAyLCOfz_WDE+huj3UrVwj6ZA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Greetings!

I am getting the following message using DEBUG logging, telling me that a
particular GIST index is suboptimal. This is for a table with ids and date
ranges that are supposed to be non-overlapping. Here is the index def:

"my_fact_table_id_as_of_date_excl" EXCLUDE USING gist (id WITH =,
as_of_date WITH &&)

This is on PG 13.9, but I got this exact same message on the same cluster
when it was on version 10. The fact table has about 250m rows and is about
275GB in size.

Here is the approximate log message:

2022-12-06 06:00:00.152
GMT,"me","foo",3559072,"0.0.0.0:5000",638ead26.364ea0,27080,"SELECT",2022-12-06
02:47:02 GMT,30/296653,2464130682,DEBUG,XX000,"picksplit method for column
2 of index ""my_fact_table_id_as_of_date_excl"" failed",,"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.",,,"SQL statement ""WITH
ended_last_fact AS
(UPDATE my_fact_table
SET as_of_date = daterange(lower(as_of_date), v_as_of_date_start)
, updated = v_updated
WHERE id = v_id
AND lower(as_of_date) <> v_as_of_date_start
AND upper(as_of_date) = 'infinity'
RETURNING *)

INSERT INTO my_fact_table AS f (
as_of_date
, customer_id
, foo
, id
updated)
SELECT
daterange(v_as_of_date_start, 'infinity')
, v_customer_id
, 'mock' AS foo
, v_id
, v_updated
FROM (SELECT v_id AS id) nes
LEFT JOIN ended_last_fact ON nes.id = ended_last_fact.id
ON CONFLICT (id, lower(daterange(as_of_date)))
DO UPDATE
SET
foo = f.foo
, updated = f.updated;

So I suppose this means I could be getting better performance but in any
case, I don't understand the hint suggested. Any insight is much
appreciated. Thanks!

Jeremy

Browse pgsql-general by date

  From Date Subject
Next Message Nunya Business 2022-12-06 18:19:36 Re[2]: PG 14.5 -- Impossible to restore dump due to interaction/order of views, functions, and generated columns
Previous Message Adrian Klaver 2022-12-06 15:51:51 Re: PG 14.5 -- Impossible to restore dump due to interaction/order of views, functions, and generated columns