Re: Why is query selecting sequential?

On Fri, Feb 06, 2004 at 01:51:39PM -0800, Josh Berkus wrote:
> Karl,
>
> >          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))
>
> > 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.
>
> It's using a seq scan because you have only 1 row in the table.     Don't
> bother testing performance before your database is populated.
>
> PostgreSQL doesn't just use an index because it's there; it uses and index
> because it's faster than not using one.
>
> If there is more than one row in the table, then:
> 1) run ANALYZE forumlog;
> 2) Send us the EXPLAIN ANALYZE, not just the explain for the query.

Hmmm... there is more than one row in the table. :-)  There aren't a huge
number, but there are a few.  I know about the optimizer not using indices
if there are no (or only one) row in the table - not making that
mistake here.

Ran analyze forumlog;

Same results.

Here's an explain analyze with actual values (that DO match real values in
the table) filled in.

akcs=> explain analyze select forum, (replied > (select lastview from forumlog where forumlog.login='genesis' and
forumlog.forum='General'and number=post.number)) as newflag, * from post where forum = 'General' and toppost = 1 order
bypinned desc, replied desc;                
                                                         QUERY PLAN
    

----------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=28.41..28.42 rows=6 width=218) (actual time=0.677..0.698 rows=5 loops=1)
   Sort Key: pinned, replied
   ->  Index Scan using post_toppost on post  (cost=0.00..28.33 rows=6 width=218) (actual time=0.403..0.606 rows=5
loops=1)
         Index Cond: ((forum = 'General'::text) AND (toppost = 1))
         SubPlan
           ->  Seq Scan on forumlog  (cost=0.00..1.18 rows=1 width=8) (actual time=0.015..0.027 rows=1 loops=5)
                 Filter: ((login = 'genesis'::text) AND (forum = 'General'::text) AND (number = $0))
 Total runtime: 0.915 ms
(8 rows)

--
--
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!