I created an immutable function as below: CREATE OR REPLACE FUNCTION GetCurrentDate() RETURNS DATE AS $p$ BEGIN RETURN CURRENT_TIMESTAMP::DATE; END; $p$ LANGUAGE plpgsql IMMUTABLE SECURITY DEFINER; And I use this in a UNIQUE INDEX's WHERE clause like: CREATE UNIQUE INDEX…
I have the following query: select count(*) as aggregate from users where date(created_at) < '2024-04-01'; It returns 2337355 which is a correct number. But it takes over 1.5 secs to be executed. Any idea how can I make it more…
In the database, there is a field that stores JSON, and the content is roughly as follows: "content":{"systemToken":false,"timezone":"America/Los_Angeles","totalCount":false,"userIds":[]}" The requirement is to query data where totalCount is true. SELECT * from task where column_a like '%"totalCount%' This query returns results,…
[ { "B4": 14, "B5": 12 }, { "B4": 58, "B5": 54 }, { "B4": 26, "B5": 65 } ] I want to create index ID in my uploaded json file. The json file look like as in the image.…
I have millions of rows of data in MySQL DB. Can you suggest me any services or platforms that support JOINS and fast data retrieval. I have looked on elastic search and manticore search, but these don't support JOINS. So,…
Does anyone know how to create Azure AI Search indexes for email .msg files? I have been able to find sample indexes for JSON content but can't seem to find samples that index email content. I would like to be…
I'm using a postgresql (15.5) database with the following logic: We have "meters" who measure electrical consumption. We have "computer meters" who are calculation of the measurements of different meters. For exemple "computedMeterA" = "meterA" + "meterB" I have classical…
So I have this query which is extremely slow to run - SELECT "conversation".*, "teamBotLocation"."id" AS "teamBotLocation.id", "teamBotLocation"."name" AS "teamBotLocation.name" FROM "conversation" AS "conversation" LEFT OUTER JOIN "teamBotLocation" AS "teamBotLocation" ON "conversation"."teamBotLocationId" = "teamBotLocation"."id" WHERE "conversation"."teamBotId" = 'some_id' AND "conversation"."teamBotLocationId"…
I want to improve performance of my queries using GIN index on jsonb column for pattern matching For example, I have a table defined as: CREATE TABLE my_table ( uuid text, doc jsonb ); In every row of table doc…
I have two tables "Tasks"(300k rows) and "TaskAssignedRoles"(500k rows). CREATE TABLE public."Tasks" ( "ID" uuid NOT NULL, "RowID" uuid NOT NULL, "Planned" timestamptz NOT NULL, CONSTRAINT "pk_Tasks" PRIMARY KEY ("RowID") ); CREATE INDEX "idx_Tasks_ID" ON public."Tasks" USING btree ("ID"); CREATE…