Skip site navigation (1) Skip section navigation (2)

Why is query selecting sequential?

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 (view raw or flat)
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!

Responses

pgsql-performance by date

Next:From: Josh BerkusDate: 2004-02-06 21:51:39
Subject: Re: Why is query selecting sequential?
Previous:From: Octavio AlvarezDate: 2004-02-06 17:24:52
Subject: Re: [PERFORM] Seq scan on zero-parameters function

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group