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 58E94D1DB20 for ; Fri, 6 Feb 2004 21:36:07 +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 14242-08 for ; Fri, 6 Feb 2004 17:36:04 -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 4D639D1DAD1 for ; Fri, 6 Feb 2004 17:36:01 -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 i16La27b055769 for ; Fri, 6 Feb 2004 15:36:03 -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 i16La2D6004966; Fri, 6 Feb 2004 15:36:02 -0600 (CST) (envelope-from karl@FS.Denninger.Net) Received: (from karl@localhost) by FS.Denninger.Net (8.12.10/8.12.8/Submit) id i16La2nB004965; Fri, 6 Feb 2004 15:36:02 -0600 (CST) Message-ID: <20040206153602.B4910@Denninger.Net> Date: Fri, 6 Feb 2004 15:36:02 -0600 From: Karl Denninger To: pgsql-performance@postgresql.org Subject: Why is query selecting sequential? Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Mailer: Mutt 0.93.2i 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/100 X-Sequence-Number: 5624 I have two tables which have related selection data; they get updated separately. One contains messages, the second an "index key" for each user's viewing history. When I attempt to use a select that merges the two to produce a "true or false" output in one of the reply rows, I get a sequential scan of the second table - which is NOT what I want! Here are the table definitions and query explain results... akcs=> \d post Table "public.post" Column | Type | Modifiers -----------+-----------------------------+----------------------------------------------------------- forum | text | number | integer | toppost | integer | views | integer | default 0 login | text | subject | text | message | text | inserted | timestamp without time zone | modified | timestamp without time zone | who | text | reason | text | ordinal | integer | not null default nextval('public.post_ordinal_seq'::text) replies | integer | default 0 ip | text | invisible | integer | sticky | integer | lock | integer | pinned | integer | default 0 replied | timestamp without time zone | Indexes: "post_forum" btree (forum) "post_lookup" btree (forum, number) "post_order" btree (number, inserted) "post_toppost" btree (forum, toppost, inserted) akcs=> \d forumlog; Table "public.forumlog" Column | Type | Modifiers ----------+-----------------------------+----------- login | text | forum | text | lastview | timestamp without time zone | number | integer | Indexes: "forumlog_composite" btree (login, forum, number) "forumlog_login" btree (login) "forumlog_number" btree (number) akcs=> explain select forum, (replied > (select lastview from forumlog where forumlog.login='%s' and forumlog.forum='%s' and number=post.number)) as newflag, * from post where forum = '%s' and toppost = 1 order by pinned desc, replied desc; QUERY PLAN ------------------------------------------------------------------------------------------- Sort (cost=3.20..3.21 rows=1 width=218) Sort Key: pinned, replied -> Index Scan using post_forum on post (cost=0.00..3.19 rows=1 width=218) Index Cond: (forum = '%s'::text) Filter: (toppost = 1) 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)) (8 rows) 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. What am I missing here? -- -- 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!