If your queries all reference userid, then you only need indices on gdid
and userid.
On Mon, Dec 23, 2024 at 12:49 PM Divyansh Gupta JNsThMAudy <
ag1567...@gmail.com> wrote:
> I have one confusion with this design if I opt to create 50 columns I need
> to create 50 index which will work with userid index in Bitmap on the other
> hand if I create a JSONB column I need to create a single index ?
>
> On Mon, 23 Dec 2024, 11:10 pm Ron Johnson, <ronljohnso...@gmail.com>
> wrote:
>
>> Given what you just wrote, I'd stick with 50 separate t* columns.
>> Simplifies queries, simplifies updates, and eliminates JSONB conversions.
>>
>> On Mon, Dec 23, 2024 at 12:29 PM Divyansh Gupta JNsThMAudy <
>> ag1567...@gmail.com> wrote:
>>
>>> Values can be updated based on customer actions
>>>
>>> All rows won't have all 50 key value pairs always if I make those keys
>>> into columns the rows might have null value on the other hand if it is
>>> JSONB then the key value pair will not be there
>>>
>>> Yes in UI customers can search for the key value pairs
>>>
>>> During data population the key value pair will be empty array in case of
>>> JSONB column or NULL in case of table columns, later when customer performs
>>> some actions that time the key value pairs will populate and update, based
>>> on what action customer performs.
>>>
>>> On Mon, 23 Dec 2024, 10:51 pm Divyansh Gupta JNsThMAudy, <
>>> ag1567...@gmail.com> wrote:
>>>
>>>> Let's make it more understandable, here is the table schema with 50
>>>> columns in it
>>>>
>>>> CREATE TABLE dbo.googledocs_tbl (
>>>> gdid int8 GENERATED BY DEFAULT AS IDENTITY( INCREMENT BY 1 MINVALUE 1
>>>> MAXVALUE 9223372036854775807 START 1 CACHE 1 NO CYCLE) NOT NULL,
>>>> userid int8 NOT NULL,
>>>> t1 int4 NULL,
>>>> t2 int4 NULL,
>>>> t3 int4 NULL,
>>>> t4 int4 NULL,
>>>> t5 int4 NULL,
>>>> t6 int4 NULL,
>>>> t7 int4 NULL,
>>>> t8 int4 NULL,
>>>> t9 int4 NULL,
>>>> t10 int4 NULL,
>>>> t11 int4 NULL,
>>>> t12 int4 NULL,
>>>> t13 int4 NULL,
>>>> t14 int4 NULL,
>>>> t15 int4 NULL,
>>>> t16 int4 NULL,
>>>> t17 int4 NULL,
>>>> t18 int4 NULL,
>>>> t19 int4 NULL,
>>>> t20 int4 NULL,
>>>> t21 int4 NULL,
>>>> t22 int4 NULL,
>>>> t23 int4 NULL,
>>>> t24 int4 NULL,
>>>> t25 int4 NULL,
>>>> t26 int4 NULL,
>>>> t27 int4 NULL,
>>>> t28 int4 NULL,
>>>> t29 int4 NULL,
>>>> t30 int4 NULL,
>>>> t31 int4 NULL,
>>>> t32 int4 NULL,
>>>> t33 int4 NULL,
>>>> t34 int4 NULL,
>>>> t35 int4 NULL,
>>>> t36 int4 NULL,
>>>> t37 int4 NULL,
>>>> t38 int4 NULL,
>>>> t39 int4 NULL,
>>>> t40 int4 NULL,
>>>> t41 int4 NULL,
>>>> t42 int4 NULL,
>>>> t43 int4 NULL,
>>>> t44 int4 NULL,
>>>> t45 int4 NULL,
>>>> t46 int4 NULL,
>>>> t47 int4 NULL,
>>>> t48 int4 NULL,
>>>> t49 int4 NULL,
>>>> t50 int4 NULL,
>>>> CONSTRAINT googledocs_tbl_pkey PRIMARY KEY (gdid),
>>>> );
>>>>
>>>> Every time when i query I will query it along with userid
>>>> Ex : where userid = 12345678 and t1 in (1,2,3) and t2 in (0,1,2)
>>>> more key filters if customer applies
>>>>
>>>> On the other hand if I create a single jsonb column the schema will
>>>> look like :
>>>>
>>>> CREATE TABLE dbo.googledocs_tbl (
>>>> gdid int8 GENERATED BY DEFAULT AS IDENTITY( INCREMENT BY 1 MINVALUE 1
>>>> MAXVALUE 9223372036854775807 START 1 CACHE 1 NO CYCLE) NOT NULL,
>>>> userid int8 NOT NULL,
>>>> addons_json jsonb default '{}'::jsonb
>>>> CONSTRAINT googledocs_tbl_pkey PRIMARY KEY (gdid),
>>>> );
>>>>
>>>> and the query would be like
>>>> where userid = 12345678 and ((addons_json @> {t1:1}) or (addons_json @>
>>>> {t1:2}) or (addons_json @> {t1:3})
>>>> more key filters if customer applies
>>>>
>>>>
>>>>
>>>> On Mon, Dec 23, 2024 at 10:38 PM David G. Johnston <
>>>> david.g.johns...@gmail.com> wrote:
>>>>
>>>>>
>>>>>
>>>>> On Mon, Dec 23, 2024, 10:01 Divyansh Gupta JNsThMAudy <
>>>>> ag1567...@gmail.com> wrote:
>>>>>
>>>>>>
>>>>>> So here my question is considering one JSONB column is perfect or
>>>>>> considering 50 columns will be more optimised.
>>>>>>
>>>>> The relational database engine is designed around the column-based
>>>>> approach. Especially if the columns are generally unchanging, combined
>>>>> with using fixed-width data types.
>>>>>
>>>>> David J.
>>>>>
>>>>>
>>
>> --
>> Death to <Redacted>, and butter sauce.
>> Don't boil me, I'm still alive.
>> <Redacted> lobster!
>>
>
--
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!
