Timeline for Optimizing a "categorized" search table using trigrams
Current License: CC BY-SA 4.0
11 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| Jul 18, 2018 at 8:13 | history | edited | Erwin Brandstetter | CC BY-SA 4.0 |
typos
|
| Jul 17, 2018 at 15:53 | comment | added | brad | I added my results below. Looks like the gist with NULLs worked fine, but as stated, the amount of NULL values is pretty low (on my current dataset) | |
| Jul 17, 2018 at 15:53 | vote | accept | brad | ||
| May 15, 2018 at 18:54 | comment | added | brad | ya I'm going to benchmark this when I have a chance. I'll post my findings when I do | |
| May 15, 2018 at 2:34 | comment | added | jjanes | Just my own experimentation. There is this somewhat cryptic comment in the code "On non-leaf page we can't conclude that child hasn't NULL values because of assumption in GiST: union (VAL, NULL) is VAL." It seems like that assumption should be explained elsewhere as well in more depth, but I can't find it. | |
| May 15, 2018 at 1:58 | comment | added | Erwin Brandstetter |
@brad: Since you have a use case at hand: Could you test performance with the suggested index with user_id = 123 and user_id IS NULL?
|
|
| May 15, 2018 at 1:54 | comment | added | Erwin Brandstetter | @jjanes: That's bad news. I didn't test performance with NULL values in particular. Would you happen to have a link to more info on this? | |
| May 14, 2018 at 20:21 | comment | added | jjanes |
Actually it appears to the entire GiST framework, not just btree_gist, which can't cope gracefully with NULLs.
|
|
| May 14, 2018 at 14:05 | comment | added | jjanes |
While NULL values are supported in indexes in general, the way btree_gist in particular supports them is rather disgraceful. He is quite unlikely to find the multi-column gist index to outperform the three independent indexes, unless he recodes his data so that it uses a dummy non-NULL value rather than NULL.
|
|
| May 14, 2018 at 10:19 | history | edited | Erwin Brandstetter | CC BY-SA 4.0 |
explain, add links
|
| May 14, 2018 at 3:00 | history | answered | Erwin Brandstetter | CC BY-SA 4.0 |