X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id EC0DED1D29D for ; Sat, 7 Feb 2004 01:53:53 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 84069-10 for ; Fri, 6 Feb 2004 21:53:53 -0400 (AST) Received: from iceberg.ics911.net (h-66-167-103-228.CHCGILGM.covad.net [66.167.103.228]) by svr1.postgresql.org (Postfix) with ESMTP id 9ED0ED1D287 for ; Fri, 6 Feb 2004 21:53:48 -0400 (AST) Received: from FS.Denninger.Net (ip68-1-64-244.pn.at.cox.net [68.1.64.244]) by iceberg.ics911.net (8.12.2/8.11.6) with ESMTP id i171rn7b056320; Fri, 6 Feb 2004 19:53:49 -0600 (CST) (envelope-from karl@FS.Denninger.Net) Received: from FS.Denninger.Net (localhost [127.0.0.1]) by FS.Denninger.Net (8.12.10/8.12.8) with ESMTP id i171rmD6006528; Fri, 6 Feb 2004 19:53:48 -0600 (CST) (envelope-from karl@FS.Denninger.Net) Received: (from karl@localhost) by FS.Denninger.Net (8.12.10/8.12.8/Submit) id i171rmKg006527; Fri, 6 Feb 2004 19:53:48 -0600 (CST) Message-ID: <20040206195348.B6406@Denninger.Net> Date: Fri, 6 Feb 2004 19:53:48 -0600 From: Karl Denninger To: josh@agliodbs.com, pgsql-performance@postgresql.org Subject: Re: Why is query selecting sequential? References: <20040206153602.B4910@Denninger.Net> <200402061351.39084.josh@agliodbs.com> <20040206162232.D4910@Denninger.Net> <200402061436.57903.josh@agliodbs.com> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Mailer: Mutt 0.93.2i In-Reply-To: <200402061436.57903.josh@agliodbs.com>; from Josh Berkus on Fri, Feb 06, 2004 at 02:36:57PM -0800 Organization: Karl's Sushi and Packet Smashers X-Die-Spammers: Spammers cheerfully broiled for supper and served with ketchup! X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200402/107 X-Sequence-Number: 5631 On Fri, Feb 06, 2004 at 02:36:57PM -0800, Josh Berkus wrote: > Karl, > > Well, still with only 5 rows in the forumlog table you're not going get > realistic results compared to a loaded database. However, you are making > things difficult for the parser with awkward query syntax; what you currently > have encourages a sequential loop. > > If there are potentially several rows in forumlog for each row in post, then > your query won't work either. It better not. Indeed, I WANT it to blow up if there is, as that's a serious error, and am counting on that to happen (and yes, I know it will - and it should!) > > akcs=> explain analyze select forum, (replied > (select lastview from > forumlog where forumlog.login='genesis' and forumlog.forum='General' and > number=post.number)) as newflag, * from post where forum = 'General' and > toppost = 1 order by pinned desc, replied desc; > > Instead: > > if only one row in forumlog per row in post: > > SELECT (replied > lastview) AS newflag, post.* > FROM post, forumlog > WHERE post.forum = 'General' and toppost = 1 and forumlog.login = 'genesis' > and forumlog.forum='General' and forumlog.number=post.number; It still thinks its going to sequentially scan it... I'll see what happens when I get some more rows in the table and if it decides to start using the indices then.... akcs=> explain analyze select (replied > lastview) as newflag, post.* from post, forumlog where post.forum ='General' and toppost = 1 and forumlog.login='genesis' and forumlog.forum='General' order by post.pinned desc, post.replied desc; QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------------- Sort (cost=23.83..23.90 rows=30 width=226) (actual time=1.228..1.331 rows=25 loops=1) Sort Key: post.pinned, post.replied -> Nested Loop (cost=1.15..23.09 rows=30 width=226) (actual time=0.157..0.797 rows=25 loops=1) -> Index Scan using post_toppost on post (cost=0.00..21.27 rows=6 width=218) (actual time=0.059..0.089 rows=5 loops=1) Index Cond: ((forum = 'General'::text) AND (toppost = 1)) -> Materialize (cost=1.15..1.20 rows=5 width=8) (actual time=0.013..0.046 rows=5 loops=5) -> Seq Scan on forumlog (cost=0.00..1.15 rows=5 width=8) (actual time=0.027..0.065 rows=5 loops=1) Filter: ((login = 'genesis'::text) AND (forum = 'General'::text)) Total runtime: 1.754 ms (9 rows) -- -- Karl Denninger (karl@denninger.net) Internet Consultant & Kids Rights Activist http://www.denninger.net Tired of spam at your company? LOOK HERE! http://childrens-justice.org Working for family and children's rights http://diversunion.org LOG IN AND GET YOUR TANK STICKERS TODAY!