On Fri, Feb 06, 2004 at 02:36:57PM -0800, Josh Berkus wrote:
> Karl,
> Well, still with only 5 rows in the forumlog table you're not going get 
> realistic results compared to a loaded database.  However, you are making 
> things difficult for the parser with awkward query syntax; what you currently 
> have encourages a sequential loop.
> If there are potentially several rows in forumlog for each row in post, then 
> your query won't work either.

It better not.  Indeed, I WANT it to blow up if there is, as that's a
serious error, and am counting on that to happen (and yes, I know it will -
and it should!)

> > 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;       
> Instead:
> if only one row in forumlog per row in post:
> SELECT (replied > lastview) AS newflag, post.* 
> FROM post, forumlog
> WHERE post.forum = 'General' and toppost = 1 and forumlog.login = 'genesis'
> and forumlog.forum='General' and forumlog.number=post.number;

It still thinks its going to sequentially scan it...

I'll see what happens when I get some more rows in the table and if it 
decides to start using the indices then....

akcs=> explain analyze select (replied > lastview) as newflag, post.* from post, 
forumlog where post.forum ='General' and toppost = 1 and forumlog.login='genesis' and 
forumlog.forum='General' order by post.pinned desc, post.replied desc;
                                                            QUERY PLAN                 
 Sort  (cost=23.83..23.90 rows=30 width=226) (actual time=1.228..1.331 rows=25 loops=1)
   Sort Key: post.pinned, post.replied
   ->  Nested Loop  (cost=1.15..23.09 rows=30 width=226) (actual time=0.157..0.797 
rows=25 loops=1)
         ->  Index Scan using post_toppost on post  (cost=0.00..21.27 rows=6 
width=218) (actual time=0.059..0.089 rows=5 loops=1)
               Index Cond: ((forum = 'General'::text) AND (toppost = 1))
         ->  Materialize  (cost=1.15..1.20 rows=5 width=8) (actual time=0.013..0.046 
rows=5 loops=5)
               ->  Seq Scan on forumlog  (cost=0.00..1.15 rows=5 width=8) (actual 
time=0.027..0.065 rows=5 loops=1)
                     Filter: ((login = 'genesis'::text) AND (forum = 'General'::text))
 Total runtime: 1.754 ms
(9 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 2: you can get off all lists at once with the unregister command
    (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Reply via email to