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
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 |