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 by pinned 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 ([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 5: Have you checked our extensive FAQ?

               http://www.postgresql.org/docs/faqs/FAQ.html

Reply via email to