From: | Karl Denninger <karl(at)Denninger(dot)Net> |
---|---|
To: | pgsql-performance(at)postgresql(dot)org |
Subject: | Why is query selecting sequential? |
Date: | 2004-02-06 21:36:02 |
Message-ID: | 20040206153602.B4910@Denninger.Net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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(at)denninger(dot)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!
From | Date | Subject | |
---|---|---|---|
Next Message | Josh Berkus | 2004-02-06 21:51:39 | Re: Why is query selecting sequential? |
Previous Message | Octavio Alvarez | 2004-02-06 17:24:52 | Re: [PERFORM] Seq scan on zero-parameters function |