generic sql question

[Dennis Gearon]

My question is it possible to speed up a query doing preselects? What I'm working on could end up being a very large dataset. I hope to have 100-1000 queries per second (0r more?), and if very large tables are joined with very large tables, I imagine that the memory would be get very full, overfull?

So in the schema below the following queries, usrs own articles, articles are of article types, issues have dates and names, and issues_publishe d has an issue id and sets of articles, and article can be in many issues.

So if I wanted to find articles of a certain article type within a certain date range for the article and had actually been published, I believe that this query could find it, joining three tables and then doing the qualifications for date and type: (assume values in {} are escaped and proper syntax)

-----------------------------------------------------------------
SELECT article_id
FROM issues_publishe d, issues, articles

WHERE issues_publishe d.article_id = articles.articl e_id
AND
issues_publishe d.issue_id = issues.issue_id
AND
articles.articl e_type = {article_type_i d desired}
AND
article.article _date < {highest date}
AND
issues.article_ date > {lowest date};

But would the following reduce the size of the join in memory?

SELECT article_id
FROM (select *
from articles
where article_date < {highest date}
AND
article_date > {lowest date} ) as articles_in_ran ge, issues, issues_publishe d

WHERE issues_publishe d.article_id = articles_in_ran ge.article_id
AND
issues_publishe d.issue_id = issues.issue_id
AND
articles_in_ran ge.article_type = {article type desired}

-------------------------------------------------------------------------
CREATE TABLE usr (
usr_id SERIAL NOT NULL,
PRIMARY KEY (usr_id)
);

CREATE TABLE article_types (
ariticle_type_i d SERIAL NOT NULL,
article_type VARCHAR(40) NOT NULL,
PRIMARY KEY (ariticle_type_ id)
);

CREATE TABLE articles (
article_id SERIAL NOT NULL,
ariticle_type_i d INT4 NOT NULL,
author INT4 NOT NULL,
body TEXT NOT NULL,
date_written DATE NOT NULL,
PRIMARY KEY (article_id, ariticle_type_i d, author)
);

CREATE TABLE issues (
issue_id SERIAL NOT NULL,
issue_title VARCHAR(40) NOT NULL,
issue_date DATE NOT NULL,
PRIMARY KEY (issue_id)
);

CREATE TABLE issues_publishe d (
issue_id INT4 NOT NULL,
article_id INT4 NOT NULL,
PRIMARY KEY (issue_id, author, ariticle_type_i d, article_id)
);

/*============== =============== =============== =============== =============== */
/* Foreign Keys */
/*============== =============== =============== =============== =============== */

ALTER TABLE articles
ADD FOREIGN KEY (author) REFERENCES usr (usr_id);

ALTER TABLE articles
ADD FOREIGN KEY (ariticle_type_ id) REFERENCES article_types (ariticle_type_ id);

ALTER TABLE issue_articles
ADD FOREIGN KEY (issue_id) REFERENCES issues (issue_id);

ALTER TABLE issue_articles
ADD FOREIGN KEY (author,ariticl e_type_id,artic le_id) REFERENCES articles (author, ariticle_type_i d, article_id);
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[Hicham G. Elmongui]

i do no think writing the query in the second form differs from the first
one. In both cases, only the relevent articles (in range and of desired
type) will come out of the scan operator that scans the articles.
--h

"Dennis Gearon" <ge*****@firese rve.net> wrote in message
news:41******** ******@fireserv e.net...

My question is it possible to speed up a query doing preselects? What I'm working on could end up being a very large dataset. I hope to have 100-1000
queries per second (0r more?), and if very large tables are joined with very
large tables, I imagine that the memory would be get very full, overfull?
So in the schema below the following queries, usrs own articles, articles are of article types, issues have dates and names, and issues_publishe d has
an issue id and sets of articles, and article can be in many issues.
So if I wanted to find articles of a certain article type within a certain date range for the article and had actually been published, I believe that
this query could find it, joining three tables and then doing the
qualifications for date and type: (assume values in {} are escaped and
proper syntax)
-----------------------------------------------------------------
SELECT article_id
FROM issues_publishe d, issues, articles

WHERE issues_publishe d.article_id = articles.articl e_id
AND
issues_publishe d.issue_id = issues.issue_id
AND
articles.articl e_type = {article_type_i d desired}
AND
article.article _date < {highest date}
AND
issues.article_ date > {lowest date};

But would the following reduce the size of the join in memory?

SELECT article_id
FROM (select *
from articles
where article_date < {highest date}
AND
article_date > {lowest date} ) as articles_in_ran ge, issues, issues_publishe d
WHERE issues_publishe d.article_id = articles_in_ran ge.article_id
AND
issues_publishe d.issue_id = issues.issue_id
AND
articles_in_ran ge.article_type = {article type desired}

-------------------------------------------------------------------------
CREATE TABLE usr (
usr_id SERIAL NOT NULL,
PRIMARY KEY (usr_id)
);

CREATE TABLE article_types (
ariticle_type_i d SERIAL NOT NULL,
article_type VARCHAR(40) NOT NULL,
PRIMARY KEY (ariticle_type_ id)
);

CREATE TABLE articles (
article_id SERIAL NOT NULL,
ariticle_type_i d INT4 NOT NULL,
author INT4 NOT NULL,
body TEXT NOT NULL,
date_written DATE NOT NULL,
PRIMARY KEY (article_id, ariticle_type_i d, author)
);

CREATE TABLE issues (
issue_id SERIAL NOT NULL,
issue_title VARCHAR(40) NOT NULL,
issue_date DATE NOT NULL,
PRIMARY KEY (issue_id)
);

CREATE TABLE issues_publishe d (
issue_id INT4 NOT NULL,
article_id INT4 NOT NULL,
PRIMARY KEY (issue_id, author, ariticle_type_i d, article_id)
);

/*============== =============== =============== =============== ===============
*/ /* Foreign Keys */ /*============== =============== =============== =============== ===============
*/
ALTER TABLE articles
ADD FOREIGN KEY (author) REFERENCES usr (usr_id);

ALTER TABLE articles
ADD FOREIGN KEY (ariticle_type_ id) REFERENCES article_types (ariticle_type_ id);
ALTER TABLE issue_articles
ADD FOREIGN KEY (issue_id) REFERENCES issues (issue_id);

ALTER TABLE issue_articles
ADD FOREIGN KEY (author,ariticl e_type_id,artic le_id) REFERENCES articles (author, ariticle_type_i d, article_id);

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