Why is query selecting sequential? - Mailing list pgsql-performance
From | Karl Denninger |
---|---|
Subject | Why is query selecting sequential? |
Date | |
Msg-id | 20040206153602.B4910@Denninger.Net Whole thread Raw |
Responses |
Re: Why is query selecting sequential?
|
List | pgsql-performance |
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!
pgsql-performance by date: