pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Karl Denninger <karl@denninger.net>
To: josh@agliodbs.com
To: pgsql-performance@postgresql.org
Subject: Re: Why is query selecting sequential?
Date: Fri, 6 Feb 2004 19:53:48 -0600
Message-ID: <20040206195348.B6406@Denninger.Net> (raw)
In-Reply-To: <200402061436.57903.josh@agliodbs.com>
References: <20040206153602.B4910@Denninger.Net>
	<200402061351.39084.josh@agliodbs.com>
	<20040206162232.D4910@Denninger.Net>
	<200402061436.57903.josh@agliodbs.com>

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



view thread (8+ messages)  latest in thread

Message-ID: <20040206195348.B6406@Denninger.Net>
Permalink:  ../20040206195348.B6406@Denninger.Net/
Also on:    postgresql.org/message-id/20040206195348.B6406@Denninger.Net

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: karl@denninger.net, josh@agliodbs.com
  Subject: Re: Why is query selecting sequential?
  In-Reply-To: <20040206195348.B6406@Denninger.Net>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox