Moving GiST index constant to parameter

From: Thomas Mercieca <tmercieca(at)msn(dot)com>
To: "pgsql-hackers(at)postgresql(dot)org" <pgsql-hackers(at)postgresql(dot)org>
Subject: Moving GiST index constant to parameter
Date: 2017-04-02 09:27:35
Message-ID: HE1PR0701MB257105EA5426C54FE61EA65BA6090@HE1PR0701MB2571.eurprd07.prod.outlook.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Hi all,

The GiST index has a picksplit support procedure (in gistproc.c) with a constant set up using #define - LIMIT_RATIO, set to 0.3. The PostGIS extension which shares similar code for GiST has this set to 0.1 (gserialized_gist_picksplit_2d.c). Compiling new builds, I could see a difference in my indexes with different values.

I would like to introduce it as a parameter so the procedure will behave according to the user's defined value at run-time. In other words, different GiST indexes would have different values of LIMIT_RATIO without requiring a new PostgreSQL/PostGIS build. However, I am not sure what the best approach is.

I have tried introducing a GUC variable (entry in guc.c/config_real) and I have also tried having the parser recognize it (same as fillfactor is recognized), but irrelevant of what I set the value to at run-time, I always get the same unusual index stats (cannot reproduce results) and the following messages in the logfile, suggesting that I broke the function:

"DEBUG: picksplit method for column 1 of index "idx2243" failed
HINT: 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.
STATEMENT: create index idx2243 on geom_table using gist(geom)"

and also

"DEBUG: mapped win32 error code 2 to 2
STATEMENT: create index idx2243 on geom_table using gist(geom)"

I was thinking that picksplit would always look at an updated version of LIMIT_RATIO. But I'm at a loss at how to approach the problem at this stage. Perhaps my implementation is not correct or my approach not appropriate at all.

Is there a better way of introducing this parameter? Or maybe I am missing something? I would be very grateful for any feedback, pointers or comments.

Best regards,
Thomas

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Pavel Stehule 2017-04-02 10:08:56 Re: Variable substitution in psql backtick expansion
Previous Message David Rowley 2017-04-02 09:21:06 Re: Performance improvement for joins where outer side is unique