From: Karl Denninger <karl@Denninger.Net>
To: pgsql-performance@postgresql.org
Subject: Why is query selecting sequential?
Date: Fri, 6 Feb 2004 15:36:02 -0600
Message-ID: <20040206153602.B4910@Denninger.Net> (raw)
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!
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: karl@Denninger.Net
Subject: Re: Why is query selecting sequential?
In-Reply-To: <20040206153602.B4910@Denninger.Net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox