Re: Why is query selecting sequential? - Mailing list pgsql-performance

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'
andtoppost = 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=5loops=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 (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!