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 9B7AAD1B49E for ; Sat, 7 Feb 2004 16:11:11 +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 77207-10 for ; Sat, 7 Feb 2004 12:11:08 -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 A51EFD1B4DA for ; Sat, 7 Feb 2004 12:11:07 -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 i17GAr7b060400; Sat, 7 Feb 2004 10:10:59 -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 i17GArD6015068; Sat, 7 Feb 2004 10:10:53 -0600 (CST) (envelope-from karl@FS.Denninger.Net) Received: (from karl@localhost) by FS.Denninger.Net (8.12.10/8.12.8/Submit) id i17GAqxK015067; Sat, 7 Feb 2004 10:10:52 -0600 (CST) Message-ID: <20040207101052.A15045@Denninger.Net> Date: Sat, 7 Feb 2004 10:10:52 -0600 From: Karl Denninger To: Tom Lane Cc: 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> <17425.1076136714@sss.pgh.pa.us> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Mailer: Mutt 0.93.2i In-Reply-To: <17425.1076136714@sss.pgh.pa.us>; from Tom Lane on Sat, Feb 07, 2004 at 01:51:54AM -0500 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/111 X-Sequence-Number: 5635 On Sat, Feb 07, 2004 at 01:51:54AM -0500, Tom Lane wrote: > Karl Denninger writes: > > 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; > > QUERY PLAN > > ---------------------------------------------------------------------------------------------------------------------------- > > Sort (cost=28.41..28.42 rows=6 width=218) (actual time=0.677..0.698 rows=5 loops=1) > > Sort Key: pinned, replied > > -> Index Scan using post_toppost on post (cost=0.00..28.33 rows=6 width=218) (actual time=0.403..0.606 rows=5 loops=1) > > Index Cond: ((forum = 'General'::text) AND (toppost = 1)) > > SubPlan > > -> Seq Scan on forumlog (cost=0.00..1.18 rows=1 width=8) (actual time=0.015..0.027 rows=1 loops=5) > > Filter: ((login = 'genesis'::text) AND (forum = 'General'::text) AND (number = $0)) > > Total runtime: 0.915 ms > > (8 rows) > > As noted elsewhere, the inner subplan will not switch over to an > indexscan until you get some more data in that table. Note however that > the subplan is only accounting for about 0.13 msec (0.027*5) so it's not > the major cost here anyway. The slow part seems to be the indexed fetch > from "post", which is taking nearly 0.5 msec to fetch five rows. > > regards, tom lane Ok... BTW, the other posted "cleaner" model doesn't work for me. If there is NO row in the subtable that matches, the other version returns nothing (which makes sense since the initial select fails to match any rows as one of the things its trying to match is missing.) I do need a return even if the log row is missing (and it WILL be, for a first visit to that particular item in the table by a particular user) -- -- 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!