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 ([EMAIL PROTECTED]) 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!
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
