Re: [GENERAL] jsonb case insensitive search

2017-06-01 Thread Guyren Howe
On Jun 1, 2017, at 13:44 , Karl Czajkowski wrote: > If I remember correctly, isn't a compound index always just using > btree? In general, I have found better luck using several smaller > btree indices than one large compound one. Unless your entire query > can be answered from

Re: [GENERAL] jsonb case insensitive search

2017-06-01 Thread Karl Czajkowski
On Jun 01, armand pirvu modulated: > Overall could it be that the optimizer blatantly ignores a scan index which > is cheaper than a table scan, or jsonb implementation still has a long way to > come up or the way it is used in my case is not the one designed for ? > If I remember correctly,

Re: [GENERAL] jsonb case insensitive search

2017-06-01 Thread armand pirvu
I apologize before hand replying again on my own reply . I know it is frowned upon . My inline comments. > On Jun 1, 2017, at 2:05 PM, armand pirvu wrote: > > Thank you Karl and David > > Ideally as far as I can tell the index would need to be show_id, file_id, >

Re: [GENERAL] jsonb case insensitive search

2017-06-01 Thread armand pirvu
Thank you Karl and David Ideally as far as I can tell the index would need to be show_id, file_id, lower(…) The question is if this is possible ? Thanks Armand > On Jun 1, 2017, at 12:24 PM, Karl Czajkowski wrote: > > On May 31, armand pirvu modulated: > >> The idea is

Re: [GENERAL] jsonb case insensitive search

2017-06-01 Thread Karl Czajkowski
On May 31, armand pirvu modulated: > The idea is that I would like to avoid having an index for each key > possibly wanted to search, but rather say have the whole record then > search by the key and get the key value, thus having one index serving > multiple purposes so to speak > First,

Re: [GENERAL] jsonb case insensitive search

2017-05-31 Thread armand pirvu
> On May 31, 2017, at 2:32 PM, David G. Johnston > wrote: > > On Wed, May 31, 2017 at 12:18 PM, armand pirvu > wrote: > > For the example mentioned > > SELECT * > FROM cfg_files_data > WHERE

Re: [GENERAL] jsonb case insensitive search

2017-05-31 Thread David G. Johnston
On Wed, May 31, 2017 at 12:18 PM, armand pirvu wrote: > > For the example mentioned > > SELECT * > FROM cfg_files_data > WHERE cfg_files_data.show_id = 32 > AND cfg_files_data.file_id = 123 > AND lower((file_data_record ->> 'Company')) = lower('CompuTestSystems'); > ; > >