Thanks a bunch,

John

= ------=_NextPart_000_0012_01C2CF0C.0E422BD0-- From rmello@cc.usu.edu Sat Feb 8 06:30:08 2003 X-Original-To: pgsql-sql@postgresql.org Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id DE1F047621F for ; Sat, 8 Feb 2003 01:30:08 -0500 (EST) Received: from brasileiro.net (slxwy.dorms.usu.edu [129.123.227.56]) by postgresql.org (Postfix) with ESMTP id 702C8476019 for ; Sat, 8 Feb 2003 01:28:36 -0500 (EST) Received: from roberto by brasileiro.net with local (Exim 3.35 #1 (Debian)) id 18hOaR-0005HG-00; Fri, 07 Feb 2003 23:35:39 -0700 Date: Fri, 7 Feb 2003 23:35:39 -0700 From: Roberto Mello To: John Cavacas Cc: pgsql-sql@postgresql.org Subject: Re: query help/sugestions Message-ID: <20030208063539.GA20208@cc.usu.edu> References: <001501c2cf35$f7584a10$6401a8c0@spook> Mime-Version: 1.0 Content-Type: text/plain; charset=iso-8859-1 Content-Disposition: inline In-Reply-To: <001501c2cf35$f7584a10$6401a8c0@spook> User-Agent: Mutt/1.4i X-message-flag: Please consider using an e-mail program other than Outlook. It's insecure. X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200302/168 X-Sequence-Number: 11566 On Sat, Feb 08, 2003 at 12:50:20AM -0500, John Cavacas wrote: Can you please fix your e-mail client so it breaks lines at 72 characters? > Anyway, i'm trying to solve a problem which I had with my previous version. The problem boils down to how to efficiently display an article in either in its full form or in a article listing page (think front page of slashdot) while at the same time finding out what the comment count is for that article. > > I have 2 tables a "content" table which holds the articles, and a "comments" table that you guessed it holds the comments. In the past what I did was to create one query that returned all of the articles (remember I was using MySQL), then while I looped through the results of that query, in PHP I would create another SQL object and issue another query that went out and did a select count(newsid) for each article. This did what I wanted but I always thought it was a bit of a hack and it did not perform well under stress. Well, no wonder. For each article you'd issue a new query that had to be sent to, parsed, planned, optimized, executed, and returned by the database. All you needed was to join the tables, use a group by or some other technique. I suggest you read C.J. Date's "An Introduction to Database Systems". > Is there a better way to this? I have a few ideas... Definitely. > I was thinking of using a SQL sub select, but to be honest I can't seem to construct the proper query to give me what I need, which would be a result set that would look something like this: > > newsid | user | date | title | intro | commentcount You could use a GROUP BY but that could become expensive. > Another idea I had was to just create a count column in my content table, which gets incremented each time a comment is posted. > > Taking the above idea a step forward, i could problably create a trigger to keep that column counter updated whenver a new comment is inserted. Now that I think of it, it problably wouldn't work as the trigger could not be generic. > > Anyway, I was just wondering if anyone out here had any ideas or past experience that wouldn't mind sharing. This has been solved many times by several open source projects (OpenACS being one of them). You could, for the page that shoms articles with comment count, do a query that would join with the comments table, doing a COUNT on it (sorry you didn't send your table definitions). For the full article page, you could query for the comment and all the comments, caching part or all of it in ram if you foresee it'll be requested often. -Roberto -- +----| Roberto Mello - http://www.brasileiro.net/ |------+ + Computer Science Graduate Student, Utah State University + + USU Free Software & GNU/Linux Club - http://fslc.usu.edu/ + if(crash) grab_ankles();kiss_xxx_goodbye() From oogly@rogers.com Sat Feb 8 06:56:41 2003 X-Original-To: pgsql-sql@postgresql.org Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 91C4C475ADD for ; Sat, 8 Feb 2003 01:56:41 -0500 (EST) Received: from fep03-mail.bloor.is.net.cable.rogers.com (fep03-mail.bloor.is.net.cable.rogers.com [66.185.86.73]) by postgresql.org (Postfix) with ESMTP id D234D475A45 for ; Sat, 8 Feb 2003 01:56:40 -0500 (EST) Received: from spook ([65.48.10.226]) by fep03-mail.bloor.is.net.cable.rogers.com (InterMail vM.5.01.05.06 201-253-122-126-106-20020509) with ESMTP id <20030208065628.SHWB121195.fep03-mail.bloor.is.net.cable.rogers.com@spook> for ; Sat, 8 Feb 2003 01:56:28 -0500 Message-ID: <001901c2cf3f$3066d4d0$6401a8c0@spook> From: "John Cavacas" To: References: <001501c2cf35$f7584a10$6401a8c0@spook> <20030208063539.GA20208@cc.usu.edu> Subject: Re: query help/sugestions Date: Sat, 8 Feb 2003 01:56:21 -0500 MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2720.3000 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000 X-Authentication-Info: Submitted using SMTP AUTH LOGIN at fep03-mail.bloor.is.net.cable.rogers.com from [65.48.10.226] using ID at Sat, 8 Feb 2003 01:56:28 -0500 X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200302/169 X-Sequence-Number: 11567 Roberto, Thanks for the sugestions. I was actually starting to look down that path. Here are my table defs: CREATE TABLE "content" ( "id" int4 DEFAULT nextval('public.content_id_seq') NOT NULL , "userid" int4 NOT NULL , "catid" int4 NOT NULL , "type" int4 NOT NULL , "status" int4 NOT NULL , "datecreated" timestamp without time zone DEFAULT now() NOT NULL , "lastmodified" timestamp without time zone DEFAULT now() NOT NULL , "title" text NOT NULL , "intro" text NOT NULL , "body" text NOT NULL , CONSTRAINT "userid" FOREIGN KEY ("userid") REFERENCES users(id) MATCH FULL , CONSTRAINT "categories_content_fkey" FOREIGN KEY ("catid") REFERENCES categories(id) MATCH FULL , CONSTRAINT "types_content_fkey" FOREIGN KEY ("type") REFERENCES types(type) MATCH FULL , CONSTRAINT "status_content_fkey" FOREIGN KEY ("status") REFERENCES status(id) MATCH FULL , CONSTRAINT "content_pkey" PRIMARY KEY ("id") ); CREATE TABLE "comments" ( "id" int4 DEFAULT nextval('public.comments_id_seq') NOT NULL , "cid" int4 NOT NULL , "pid" int4 NOT NULL , "userid" int4 NOT NULL , "datecreated" timestamp without time zone DEFAULT now() NOT NULL , "lastmodified" timestamp without time zone DEFAULT now() NOT NULL , "title" text NOT NULL , "commentbody" text NOT NULL , "ip" varchar(16) NOT NULL , CONSTRAINT "content_comments_fkey" FOREIGN KEY ("cid") REFERENCES content(id) MATCH FULL , CONSTRAINT "comments_pkey" PRIMARY KEY ("id") ); I set my client to break lines at 72 chars, i hope it worked... This query: select c.id, c.title, COUNT(cc.cid) AS ccount FROM content c LEFT JOIN comments cc ON (c.id=cc.cid) GROUP BY c.id, c.title Does what I need, however I need to query 3 other tables as well to get the data that I need to display, trying to make that work now. Is that what you ment? Thanks a bunch, John ----- Original Message ----- From: "Roberto Mello" To: "John Cavacas" Cc: Sent: February 8, 2003 1:35 AM Subject: Re: [SQL] query help/sugestions > On Sat, Feb 08, 2003 at 12:50:20AM -0500, John Cavacas wrote: > > Can you please fix your e-mail client so it breaks lines at 72 characters? > > > Anyway, i'm trying to solve a problem which I had with my previous version. The problem boils down to how to efficiently display an article in either in its full form or in a article listing page (think front page of slashdot) while at the same time finding out what the comment count is for that article. > > > > I have 2 tables a "content" table which holds the articles, and a "comments" table that you guessed it holds the comments. In the past what I did was to create one query that returned all of the articles (remember I was using MySQL), then while I looped through the results of that query, in PHP I would create another SQL object and issue another query that went out and did a select count(newsid) for each article. This did what I wanted but I always thought it was a bit of a hack and it did not perform well under stress. > > Well, no wonder. For each article you'd issue a new query that had to be > sent to, parsed, planned, optimized, executed, and returned by the > database. All you needed was to join the tables, use a group by or some > other technique. > > I suggest you read C.J. Date's "An Introduction to Database Systems". > > > Is there a better way to this? I have a few ideas... > > Definitely. > > > I was thinking of using a SQL sub select, but to be honest I can't seem to construct the proper query to give me what I need, which would be a result set that would look something like this: > > > > newsid | user | date | title | intro | commentcount > > You could use a GROUP BY but that could become expensive. > > > Another idea I had was to just create a count column in my content table, which gets incremented each time a comment is posted. > > > > Taking the above idea a step forward, i could problably create a trigger to keep that column counter updated whenver a new comment is inserted. Now that I think of it, it problably wouldn't work as the trigger could not be generic. > > > > Anyway, I was just wondering if anyone out here had any ideas or past experience that wouldn't mind sharing. > > This has been solved many times by several open source projects (OpenACS > being one of them). > > You could, for the page that shoms articles with comment count, do a query > that would join with the comments table, doing a COUNT on it (sorry you > didn't send your table definitions). > > For the full article page, you could query for the comment and all the > comments, caching part or all of it in ram if you foresee it'll be > requested often. > > -Roberto > > -- > +----| Roberto Mello - http://www.brasileiro.net/ |------+ > + Computer Science Graduate Student, Utah State University + > + USU Free Software & GNU/Linux Club - http://fslc.usu.edu/ + > if(crash) grab_ankles();kiss_xxx_goodbye() From oogly@rogers.com Sat Feb 8 07:34:18 2003 X-Original-To: pgsql-sql@postgresql.org Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 852BB474E61 for ; Sat, 8 Feb 2003 02:34:18 -0500 (EST) Received: from fep01-mail.bloor.is.net.cable.rogers.com (fep01-mail.bloor.is.net.cable.rogers.com [66.185.86.71]) by postgresql.org (Postfix) with ESMTP id C91AB474E4F for ; Sat, 8 Feb 2003 02:34:17 -0500 (EST) Received: from spook ([65.48.10.226]) by fep01-mail.bloor.is.net.cable.rogers.com (InterMail vM.5.01.05.06 201-253-122-126-106-20020509) with ESMTP id <20030208073417.UDUO4811.fep01-mail.bloor.is.net.cable.rogers.com@spook> for ; Sat, 8 Feb 2003 02:34:17 -0500 Message-ID: <002a01c2cf44$71948600$6401a8c0@spook> From: "John Cavacas" To: References: <001501c2cf35$f7584a10$6401a8c0@spook> <20030208063539.GA20208@cc.usu.edu> Subject: Re: query help/sugestions Date: Sat, 8 Feb 2003 02:33:58 -0500 MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2720.3000 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000 X-Authentication-Info: Submitted using SMTP AUTH LOGIN at fep01-mail.bloor.is.net.cable.rogers.com from [65.48.10.226] using ID at Sat, 8 Feb 2003 02:34:17 -0500 X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200302/170 X-Sequence-Number: 11568 Roberto, Here's my solution. CREATE VIEW viewcontent (id,statusid,title,datecreated,lastmodified,userid,username,catid,catego ry,intro,body,typeid,content,commentcount) as SELECT a.id as id, a.status as statusid, a.title as title, a.datecreated as datecreated, a.lastmodified as lastmodified, b.id as userid, b.username as username, c.id as catid, c.longname as category, a.intro as intro, a.body as body, d.type as typeid, d.name as content, COUNT(e.cid) as commentcount FROM content a LEFT JOIN comments e ON (a.id = e.cid) , users b, categories c, types d WHERE a.id = b.id AND a.catid = c.id AND a.type = d.type AND a.status = 1 GROUP BY a.id, a.status, a.title, a.datecreated, a.lastmodified, b.id, b.username, c.id, c.longname, a.intro, a.body, d.type, d.name ORDER BY a.datecreated DESC ; Is creating the view a good idea in terms of performance? This is a general query, I would for example not include the main body of the text needed for the front page listing. Thanks again, John ----- Original Message ----- From: "Roberto Mello" To: "John Cavacas" Cc: Sent: February 8, 2003 1:35 AM Subject: Re: [SQL] query help/sugestions > On Sat, Feb 08, 2003 at 12:50:20AM -0500, John Cavacas wrote: > > Can you please fix your e-mail client so it breaks lines at 72 characters? > > > Anyway, i'm trying to solve a problem which I had with my previous version. The problem boils down to how to efficiently display an article in either in its full form or in a article listing page (think front page of slashdot) while at the same time finding out what the comment count is for that article. > > > > I have 2 tables a "content" table which holds the articles, and a "comments" table that you guessed it holds the comments. In the past what I did was to create one query that returned all of the articles (remember I was using MySQL), then while I looped through the results of that query, in PHP I would create another SQL object and issue another query that went out and did a select count(newsid) for each article. This did what I wanted but I always thought it was a bit of a hack and it did not perform well under stress. > > Well, no wonder. For each article you'd issue a new query that had to be > sent to, parsed, planned, optimized, executed, and returned by the > database. All you needed was to join the tables, use a group by or some > other technique. > > I suggest you read C.J. Date's "An Introduction to Database Systems". > > > Is there a better way to this? I have a few ideas... > > Definitely. > > > I was thinking of using a SQL sub select, but to be honest I can't seem to construct the proper query to give me what I need, which would be a result set that would look something like this: > > > > newsid | user | date | title | intro | commentcount > > You could use a GROUP BY but that could become expensive. > > > Another idea I had was to just create a count column in my content table, which gets incremented each time a comment is posted. > > > > Taking the above idea a step forward, i could problably create a trigger to keep that column counter updated whenver a new comment is inserted. Now that I think of it, it problably wouldn't work as the trigger could not be generic. > > > > Anyway, I was just wondering if anyone out here had any ideas or past experience that wouldn't mind sharing. > > This has been solved many times by several open source projects (OpenACS > being one of them). > > You could, for the page that shoms articles with comment count, do a query > that would join with the comments table, doing a COUNT on it (sorry you > didn't send your table definitions). > > For the full article page, you could query for the comment and all the > comments, caching part or all of it in ram if you foresee it'll be > requested often. > > -Roberto > > -- > +----| Roberto Mello - http://www.brasileiro.net/ |------+ > + Computer Science Graduate Student, Utah State University + > + USU Free Software & GNU/Linux Club - http://fslc.usu.edu/ + > if(crash) grab_ankles();kiss_xxx_goodbye() From rmello@cc.usu.edu Sat Feb 8 16:32:21 2003 X-Original-To: pgsql-sql@postgresql.org Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 25C64475C26 for ; Sat, 8 Feb 2003 11:32:21 -0500 (EST) Received: from brasileiro.net (slxwy.dorms.usu.edu [129.123.227.56]) by postgresql.org (Postfix) with ESMTP id 39449475C45 for ; Sat, 8 Feb 2003 11:31:53 -0500 (EST) Received: from roberto by brasileiro.net with local (Exim 3.35 #1 (Debian)) id 18hY0F-0006lu-00; Sat, 08 Feb 2003 09:38:55 -0700 Date: Sat, 8 Feb 2003 09:38:55 -0700 From: Roberto Mello To: John Cavacas Cc: pgsql-sql@postgresql.org Subject: Re: query help/sugestions Message-ID: <20030208163855.GD25254@cc.usu.edu> References: <001501c2cf35$f7584a10$6401a8c0@spook> <20030208063539.GA20208@cc.usu.edu> <002a01c2cf44$71948600$6401a8c0@spook> Mime-Version: 1.0 Content-Type: text/plain; charset=iso-8859-1 Content-Disposition: inline In-Reply-To: <002a01c2cf44$71948600$6401a8c0@spook> User-Agent: Mutt/1.4i X-message-flag: Please consider using an e-mail program other than Outlook. It's insecure. X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200302/171 X-Sequence-Number: 11569 On Sat, Feb 08, 2003 at 02:33:58AM -0500, John Cavacas wrote: > > CREATE VIEW viewcontent > (id,statusid,title,datecreated,lastmodified,userid,username,catid,catego > ry,intro,body,typeid,content,commentcount) as > > SELECT a.id as id, > a.status as statusid, > a.title as title, > a.datecreated as datecreated, ... Have you run the query above under EXPLAIN to see what kind of execution plan you're getting? > Is creating the view a good idea in terms of performance? This is a > general query, I would for example not include the main body of the text > needed for the front page listing. The performance will be as good as your underlying query is. The VIEW is just an "alias" if you will, and they are generally a good idea. -Roberto P.S.: Please trim the messages to which you respond. For this message you left dozens of lines of cruft hanging after your message (replying at the top is a bad idea too, because you lose context). Not only this is bad for the archives, but it wastes bandwidth and shows lack of respect for other people's inbox. -- +----| Roberto Mello - http://www.brasileiro.net/ |------+ + Computer Science Graduate Student, Utah State University + + USU Free Software & GNU/Linux Club - http://fslc.usu.edu/ + Boss spelled backwards is "double-SOB" From oogly@rogers.com Sat Feb 8 17:27:46 2003 X-Original-To: pgsql-sql@postgresql.org Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 3A6B8475425 for ; Sat, 8 Feb 2003 12:27:46 -0500 (EST) Received: from fep04-mail.bloor.is.net.cable.rogers.com (fep04-mail.bloor.is.net.cable.rogers.com [66.185.86.74]) by postgresql.org (Postfix) with ESMTP id 7BCCE4753A1 for ; Sat, 8 Feb 2003 12:27:45 -0500 (EST) Received: from spook ([65.48.10.226]) by fep04-mail.bloor.is.net.cable.rogers.com (InterMail vM.5.01.05.06 201-253-122-126-106-20020509) with ESMTP id <20030208172743.SQLV120822.fep04-mail.bloor.is.net.cable.rogers.com@spook> for ; Sat, 8 Feb 2003 12:27:43 -0500 Message-ID: <001f01c2cf97$5e774000$6401a8c0@spook> From: "John Cavacas" To: References: <001501c2cf35$f7584a10$6401a8c0@spook> <20030208063539.GA20208@cc.usu.edu> <002a01c2cf44$71948600$6401a8c0@spook> <20030208163855.GD25254@cc.usu.edu> Subject: Re: query help/sugestions Date: Sat, 8 Feb 2003 12:27:34 -0500 MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2720.3000 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000 X-Authentication-Info: Submitted using SMTP AUTH LOGIN at fep04-mail.bloor.is.net.cable.rogers.com from [65.48.10.226] using ID at Sat, 8 Feb 2003 12:27:43 -0500 X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200302/172 X-Sequence-Number: 11570 > Have you run the query above under EXPLAIN to see what kind of execution > plan you're getting? > > > Is creating the view a good idea in terms of performance? This is a > > general query, I would for example not include the main body of the text > > needed for the front page listing. > > The performance will be as good as your underlying query is. The VIEW is > just an "alias" if you will, and they are generally a good idea. I just started using PostgreSQL recently, and as such I am not sure how to properlly evaluate the results of EXPLAIN on that query. I started looking at the documentation last night regarding EXPLAIN but I haven't gotten my head around it. Any sugestions in that area? Thanks again, John From rmello@cc.usu.edu Sun Feb 9 02:08:00 2003 X-Original-To: pgsql-sql@postgresql.org Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 5045647580B for ; Sat, 8 Feb 2003 21:08:00 -0500 (EST) Received: from brasileiro.net (slxwy.dorms.usu.edu [129.123.227.56]) by postgresql.org (Postfix) with ESMTP id 0E28E474E5C for ; Sat, 8 Feb 2003 21:07:58 -0500 (EST) Received: from roberto by brasileiro.net with local (Exim 3.35 #1 (Debian)) id 18hgzu-0008FL-00; Sat, 08 Feb 2003 19:15:10 -0700 Date: Sat, 8 Feb 2003 19:15:10 -0700 From: Roberto Mello To: John Cavacas Cc: pgsql-sql@postgresql.org Subject: Re: query help/sugestions Message-ID: <20030209021510.GB31606@cc.usu.edu> References: <001501c2cf35$f7584a10$6401a8c0@spook> <20030208063539.GA20208@cc.usu.edu> <002a01c2cf44$71948600$6401a8c0@spook> <20030208163855.GD25254@cc.usu.edu> <001f01c2cf97$5e774000$6401a8c0@spook> Mime-Version: 1.0 Content-Type: text/plain; charset=iso-8859-1 Content-Disposition: inline In-Reply-To: <001f01c2cf97$5e774000$6401a8c0@spook> User-Agent: Mutt/1.4i X-message-flag: Please consider using an e-mail program other than Outlook. It's insecure. X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200302/174 X-Sequence-Number: 11572 On Sat, Feb 08, 2003 at 12:27:34PM -0500, John Cavacas wrote: > > I just started using PostgreSQL recently, and as such I am not sure how > to properlly evaluate the results of EXPLAIN on that query. I started > looking at the documentation last night regarding EXPLAIN but I haven't > gotten my head around it. Any sugestions in that area? Do you have any specific questions? In general you're trying to get rid of sequential scans (Seq Scan). -Roberto -- +----| Roberto Mello - http://www.brasileiro.net/ |------+ + Computer Science Graduate Student, Utah State University + + USU Free Software & GNU/Linux Club - http://fslc.usu.edu/ + File not found. Try again? Try again? Ah, come'on, pleeeeeease!