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
