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 4DB28D1B456 for ; Fri, 6 Feb 2004 22:22:36 +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 30481-04 for ; Fri, 6 Feb 2004 18:22:37 -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 17224D1DAA9 for ; Fri, 6 Feb 2004 18:22:34 -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 i16MMX7b055881; Fri, 6 Feb 2004 16:22:33 -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 i16MMWD6005288; Fri, 6 Feb 2004 16:22:32 -0600 (CST) (envelope-from karl@FS.Denninger.Net) Received: (from karl@localhost) by FS.Denninger.Net (8.12.10/8.12.8/Submit) id i16MMW0h005287; Fri, 6 Feb 2004 16:22:32 -0600 (CST) Message-ID: <20040206162232.D4910@Denninger.Net> Date: Fri, 6 Feb 2004 16:22:32 -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> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Mailer: Mutt 0.93.2i In-Reply-To: <200402061351.39084.josh@agliodbs.com>; from Josh Berkus on Fri, Feb 06, 2004 at 01:51:39PM -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/102 X-Sequence-Number: 5626 On Fri, Feb 06, 2004 at 01:51:39PM -0800, Josh Berkus wrote: > Karl, > > > SubPlan > > -> Seq Scan on forumlog (cost=0.00..1.18 rows=1 width=8) > > Filter: ((login = '%s'::text) AND (forum = '%s'::text) AND > (number = $0)) > > > Why is the subplan using a sequential scan? At minimum the index on the > > post number ("forumlog_number") should be used, no? What would be even > > better would be a set of indices that allow at least two (or even all three) > > of the keys in the inside SELECT to be used. > > It's using a seq scan because you have only 1 row in the table. Don't > bother testing performance before your database is populated. > > PostgreSQL doesn't just use an index because it's there; it uses and index > because it's faster than not using one. > > If there is more than one row in the table, then: > 1) run ANALYZE forumlog; > 2) Send us the EXPLAIN ANALYZE, not just the explain for the query. Hmmm... there is more than one row in the table. :-) There aren't a huge number, but there are a few. I know about the optimizer not using indices if there are no (or only one) row in the table - not making that mistake here. Ran analyze forumlog; Same results. Here's an explain analyze with actual values (that DO match real values in the table) filled in. 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) -- -- 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!