user constructed where clause - Mailing list pgsql-general

Hi All,

 

I have one requirement in my project and don’t know how to achieve.

 

My project is receiving the log information from PC in the network, and that information is stored in the below table.

 

CREATE TABLE "PCLogTable"

(

  "LOG_ID" serial NOT NULL,

  "LOG_USER_ID" integer DEFAULT 0,

  "LOG_TYPE_ID" integer,

  "LOG_PC_ID" integer NOT NULL,

  "LOG_PORT" text,

  "LOG_NOTE" text,

  "LOG_ACK" boolean,

  "LOG_TIME" timestamp without time zone,

  "LOG_NON_PENDING_STATUS" text,

  "LOG_STATUS" text,

  "LOG_MONITORED_STATE" text,

  "LOG_RSE_RAISE_TIMESTAMP" text,

  "LOG_ADD_INFO" jsonb,

  CONSTRAINT "LOG_ID" PRIMARY KEY ("LOG_ID"),

  CONSTRAINT "LOG_TYPE_ID" FOREIGN KEY ("LOG_TYPE_ID")

      REFERENCES "LogTextTable" ("LOG_TYPE_ID") MATCH SIMPLE

      ON UPDATE NO ACTION ON DELETE CASCADE

)

 

CREATE INDEX log_table_index

  ON "PCLogTable"

  USING btree

  ("LOG_TIME" DESC, "LOG_PC_ID”);

 

At present, I have written a query to get latest 5000 log information from this table and it executes in 15 seconds periodically.

 

Now, the requirement is if user provides filter information based on every column from the web UI, this filter will let the user construct the “where clause” and provide to postgresql.

In a month this table exceeds millions of record. If  I use the user-constructed query then it would take lot of time as the user may not include indexed column in the user-constructed query.

 

Also, they want to see all the record that matches the user-constructed query.