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 68C9BD1C98E for ; Sat, 7 Feb 2004 06:52:31 +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 64268-09 for ; Sat, 7 Feb 2004 02:52:30 -0400 (AST) Received: from sss.pgh.pa.us (unknown [192.204.191.242]) by svr1.postgresql.org (Postfix) with ESMTP id 3F37FD1B911 for ; Sat, 7 Feb 2004 02:52:29 -0400 (AST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.11/8.12.11) with ESMTP id i176psXI017426; Sat, 7 Feb 2004 01:51:55 -0500 (EST) To: Karl Denninger Cc: josh@agliodbs.com, pgsql-performance@postgresql.org Subject: Re: Why is query selecting sequential? In-reply-to: <20040206162232.D4910@Denninger.Net> References: <20040206153602.B4910@Denninger.Net> <200402061351.39084.josh@agliodbs.com> <20040206162232.D4910@Denninger.Net> Comments: In-reply-to Karl Denninger message dated "Fri, 06 Feb 2004 16:22:32 -0600" Date: Sat, 07 Feb 2004 01:51:54 -0500 Message-ID: <17425.1076136714@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200402/109 X-Sequence-Number: 5633 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