Search Postgresql Archives

Suboptimal GIST index?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux