query for a time interval

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

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.

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

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.

[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,itemorder)
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

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

On Wed, Dec 21, 2005 at 11:52:56AM -0800, Mark wrote:

SELECT id
FROM mq
WHERE now - start_date > time_to_live;

The problem is you can't use an index on this, because you'd need to
index on (now() - start_date), which obviously wouldn't work. Instead,
re-write the WHERE as:

WHERE start_date < now() - time_to_live
--
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

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/materialized-views-in-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

=?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

On Thu, Dec 22, 2005 at 09:47:11AM -0600, Jim C. Nasby wrote:

On Wed, Dec 21, 2005 at 11:52:56AM -0800, Mark wrote:

SELECT id
FROM mq
WHERE now - start_date > time_to_live;

The problem is you can't use an index on this, because you'd need to
index on (now() - start_date), which obviously wouldn't work. Instead,
re-write the WHERE as:

WHERE start_date < now() - time_to_live

Unless I'm missing something that wouldn't use an index either,
because the planner wouldn't know what value to compare start_date
against without hitting each row to find that row's time_to_live.
But something like this should be able to use an expression index
on (start_date + time_to_live):

WHERE start_date + time_to_live < now()

--
Michael Fuhr

Michael Fuhr <mike@fuhr.org> writes:

Unless I'm missing something that wouldn't use an index either,
because the planner wouldn't know what value to compare start_date
against without hitting each row to find that row's time_to_live.
But something like this should be able to use an expression index
on (start_date + time_to_live):

WHERE start_date + time_to_live < now()

Or perhaps simpler, get rid of time_to_live in favor of an end_date
column, which could be indexed directly.

regards, tom lane

On Wed, Dec 21, 2005 at 11:52:56 -0800,
Mark <sendmailtomark@yahoo.com> wrote:

Hello everybody,

I'm looking for an elegant SQL statement that will work in
Postgresql, MySQL and ORACLE.
The query will be executed by Java client.

To have this query for Postgresql is priority number one.

In this query I try to get a list of message Ids that expired.

time_to_live is in seconds.

SELECT id
FROM mq
WHERE now - start_date > time_to_live;

An interval is not going to be comparable to an integer without casting.
If you use explicit casting the query probably won't be portable. If there
is some implicit cast that makes the above SQL valid, it is probably a cast
to text which won't do what you want.

I have a following table:

CREATE TABLE mq
{
msg_id INTEGER,
retry_date TIMESTAMP NOT NULL DEFAULT ('now'::text)::timestamp(1),
start_date TIMESTAMP NOT NULL DEFAULT ('now'::text)::timestamp(1),
time_to_live INTEGER
}

Can you make time_to_live an interval?