user constructed where clause - Mailing list pgsql-general

From Yelai, Ramkumar IN BLR STS
Subject user constructed where clause
Date
Msg-id 8D15F77F211D7D4786182E1C8E679FAD2C72A8FDF6@INBLRK77M1MSX.in002.siemens.net
Whole thread Raw
Responses Re: user constructed where clause
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. 
 
With best regards,
Ramkumar Yelai
 
Siemens Technology and Services Private Limited
CT DC AA I HOUSE DEV GL4
84, Hosur Road
Bengaluru 560100, Indien
Tel.: +91 80 33136494
Fax: +91 80 33133389
Mobil: +91 9886182031
 
Registered Office: 130, Pandurang Budhkar Marg, Worli, Mumbai 400 018. Telephone +91 22 39677000. Fax +91 22 39677075. Other Offices: Bangalore, Chennai, Gurgaon, Noida, Kolkata, Pune. Corporate Identity number:U99999MH1986PLC093854
 
 

pgsql-general by date:

Previous
From: Ravi Tammineni
Date:
Subject: Re: Postgresql BDR Replication Setup Issue
Next
From: "David G. Johnston"
Date:
Subject: Re: user constructed where clause