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
mailto:ramkumar.ye...@siemens.com
http://www.siemens.co.in/STS
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
