PostgreSQL Discussion - view or index to optimize performance

Author

view or index to optimize performance

Klein Balázs

2005-12-21, 8:24 pm

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_Tes
tOccasionID_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

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Peter Eisentraut

2005-12-21, 8:24 pm

Am Mittwoch, 21. Dezember 2005 21:27 schrieb Klein Balázs:
> 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?

Whether the query is executed by a view or typed in in its full form by hand
is completely irrelevant to the question whether indexes are used or should
be created. Views do not optimize anything.

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql
.org so that your
message can get through to the mailing list cleanly

Klein Balázs

2005-12-21, 8:24 pm

I thought that if I used a view to retrieve data its content might be cached
so it would make the query faster.

Am Mittwoch, 21. Dezember 2005 21:27 schrieb Klein Balázs:
> 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?

Whether the query is executed by a view or typed in in its full form by hand

is completely irrelevant to the question whether indexes are used or should
be created. Views do not optimize anything.

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql
.org so that your
message can get through to the mailing list cleanly

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Richard Huxton

2005-12-22, 3:23 am

Klein Balázs wrote:
> Hello everybody,
>
> I have a table that stores responses to questions in different
> questionnaires. This table will grow to millions of rows.
& #91;snip]
> 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?

Create the index on the table and a view should use it.

You might like to read up on partial indexes where you can do something
like:

CREATE INDEX my_index ON itemresponse (testoccasionid,item
order)
WHERE bio > 0;

So long as your query/view definition has WHERE bio > 0 in it then this
index can be used.

HTH
--
Richard Huxton
Archonet Ltd

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Jim C. Nasby

2005-12-22, 11:23 am

On Wed, Dec 21, 2005 at 10:49:29PM +0100, Klein Bal?zs wrote:
> I thought that if I used a view to retrieve data its content might be cached
> so it would make the query faster.

No. A view is essentially exactly the same as inserting the view
definition into the query that's using it. IE:

CREATE VIEW v AS SELECT * FROM t;

SELECT * FROM v becomes:

SELECT * FROM (SELECT * FROM t) v;

What you could do is partition the table so that critical information is
stored in a smaller table while everything else goes to a larger table.
You can then do a UNION ALL view on top of that to 'glue' the two tables
together. You can even define rules so that you can do updates on the
view. http://cvs.distributed.net/viewcvs.cgi/stats-sql/logdb/ has an
example that's similar to this. Note that you'll need an appropriate
index on the large table so that PostgreSQL can quickly tell it doesn't
contain values that are in the small table. Or, in 8.1 you could use a
constraint. You could also do this with inherited tables instead of
views.
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Klein Balázs

2005-12-22, 8:24 pm

thanks for the help

>What you could do is partition the table so that critical information is
>stored in a smaller table while everything else goes to a larger table.

I was thinking the other way round - maybe I can split the large table by
creating a materialized view. But than I read that it is maybe unnecessary
to create a materialized view because pg

"Materialized views sound a useful workaround, if your database doesn't have
a query cache. If you do have a query cache, then you already effectively
have eager or lazy materialized views (depending on your isolation level):
Just use your normal view (or query) and let the database figure it out."
Quote from Farce Pest in
http://spyced.blogspot.com/2005/05/...postgresql.html

But later in the same blog it seems to indicate that there is a choice to
either use or not use the query cache of pg.

So I don't know now how this cache works and whether it could help me in
this.

SWK

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Tom Lane

2005-12-22, 8:24 pm

=?iso-8859-1?Q?Klein_Bal=E1zs?= <Balazs.Klein@axelero.hu> writes:
> But later in the same blog it seems to indicate that there is a choice to
> either use or not use the query cache of pg.

Hm? There is no query cache in PG.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql
.org so that your
message can get through to the mailing list cleanly

Sponsored Links