Hello everybody,
I have a table that stores responses to questions in different
questionnaires. This table will grow to millions of rows.
My problem is that while most of the data in the table are rarely used in
queries one type of response will be used quite often: biodata - name,
gender, e-mail and this sort of. This data is also collected as responses to
questionnaires.
My question: what is the best strategy if I wanted to quickly retrieve
biodata from this table:
CREATE TABLE "public"."itemresponse" (
"testoccasionid" INTEGER NOT NULL,
"itemorder" SMALLINT NOT NULL,
"response" TEXT NOT NULL,
"bio" INTEGER DEFAULT 0 NOT NULL,
"datatype" SMALLINT NOT NULL,
CONSTRAINT "ItemResponseText_pk" PRIMARY KEY("testoccasionid",
"itemorder"),
CONSTRAINT "ItemResponseText_TestOccasionID_fkey" FOREIGN KEY
("testoccasionid")
REFERENCES "public"."testoccasion"("testoccasionid")
ON DELETE NO ACTION
ON UPDATE NO ACTION
NOT DEFERRABLE
) WITH OIDS;
I can store the fact that it is biodata in the bio field - it is biodata if
the value of that field is not 0 and I can index that field and simply use
that as one of the conditions in queries.
Or should I instead create a view that contains only the biodata and select
from that? But will postgres use the indexes than? Would that be a better
approach?
Thanks for the help.
SWK
