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